Opened 17 years ago
Last modified 16 years ago
#4715 closed defect
SQLite and > 9 priorities values does not sort correctly — at Version 11
Reported by: | StefanC | Owned by: | Christian Boos |
---|---|---|---|
Priority: | normal | Milestone: | 0.11 |
Component: | report system | Version: | |
Severity: | normal | Keywords: | priority enum sort |
Cc: | vnaum@…, mmanulis@… | Branch: | |
Release Notes: | |||
API Changes: | |||
Internal Changes: |
Description (last modified by )
When using assigning priorities like this
Text : Value ---------- Prio100 : 6 Prio90 : 7 Prio80 : 8 Prio70: 9 Prio60: 10 Prio50: 11
And you try to sort on the priority, it won't work as expected (they should fall from 100 down to 50). It is my suspicion that the problem lies in sqlite's "feature" to be un-typed. I have had the same problem before with sqlite and integer ordereing. It will think that 11 is less than 6, as it does character comparision or something.
Change History (16)
follow-up: 2 comment:1 by , 17 years ago
comment:2 by , 17 years ago
Keywords: | needinfo added |
---|
Replying to StefanC:
Additional information: This behavior is only found when clicking on active ticket for a report (query), not when browsing a report.
Which is it, a report or query? I also don't understand what you mean by "clicking on active ticket" as opposed to "browsing a report". Can you be more explicit and provide the version of Trac and exactly where you're seeing this problem? If necessary make a list of steps to reproduce the problem e.g. "Click on View Tickets, Click on …" to make sure it's clear.
comment:3 by , 17 years ago
Component: | general → report system |
---|---|
Owner: | changed from | to
comment:4 by , 17 years ago
Sorry, I must have been sleeping when I wrote that.
The behavior is found when clicking on "active tickets" for a roadmap, closed tickets is also erroneous. So, what I meant before was that the page "http://ramses/cgi-bin/trac.cgi/query?" (query) does not order according to priority correctly (for me), whereas ticket reports (view tickets, click on a report) which leads "to http://ramses/cgi-bin/trac.cgi/report/16" (report), does not exhibit the erroneous behavior.
I hope this is a little bit more clear :-)
follow-up: 6 comment:5 by , 17 years ago
Keywords: | priority enum sort added; needinfo removed |
---|---|
Milestone: | → 0.12 |
He's right for the custom query, the numerical values are sorted as text ones.
For the reports, it's however quite likely that the order is correct "by chance", as the ordering is done on the field values (e.g. "high" before "highest" before "low" before "lowest", etc.)
Associating this with 0.12 for now, but could probably be backported to 0.11-stable once fixed.
comment:6 by , 17 years ago
Replying to cboos:
He's right for the custom query, the numerical values are sorted as text ones.
For the reports, it's however quite likely that the order is correct "by chance", as the ordering is done on the field values (e.g. "high" before "highest" before "low" before "lowest", etc.)
Associating this with 0.12 for now, but could probably be backported to 0.11-stable once fixed.
I don't think it has anything to do with the text the user sees, but the rank order that the admin assigns. I changed our priorities from the default values to P1, P2, P3, P4, P5, Unknown, and Retest. The order value that I assign was 6 through 12 for these priorities. When I sort them they show up as P5, Unknown, Retest, P1, P2, P3, and P4. A textual sort is being done on the numerical order values. The 6 through 12 values were used because that is all the system would allow me. The original default strings used up 1 through 5, and even though I deleted the orginal values the only choices left to me were 6 through 12.
The order is correct when the individual ticket is viewed. This problem only shows up in the query.
by , 16 years ago
Attachment: | sort_reports_on_enums.diff added |
---|
fixes enum sorting for "click on table header" sorts
comment:7 by , 16 years ago
Cc: | added |
---|---|
Owner: | changed from | to
Status: | new → assigned |
I confirm this bug. PostgreSQL also affected. These fields also affected: 'status', 'resolution', 'priority', 'severity'.
query_sortorder.diff fixes "custom query" sort issue. Tested on Trac 0.10.3 and Trac 0.10.4 with PostgreSQL databases.
Don't forget to remove query.pyc
if you have one and restart Apache if
you're using mod_python
.
BTW, this problem also appears when one is sorting report results by clicking table header "priority" (or any other enum field). sort_reports_on_enums.diff must solve this (again, tested on Tested on Trac 0.10.3 and Trac 0.10.4 with PostgreSQL database).
Patches do not apply to SVN trunk code, but still will be useful for users of 0.10.x versions. I'll try to make patches for trunk / 0.11b1 in a couple of days, too.
comment:8 by , 16 years ago
Owner: | changed from | to
---|---|
Status: | assigned → new |
Patches are looking good, so please have a try for the 0.11 version, as we probably won't apply them to 0.10.5dev (only "critical bug" fixing for 0.10.x, at this point).
For 0.12 though we should seriously consider having an change in the data model so that those casts won't be necessary in the first place (e.g. by using an enum_int
table).
by , 16 years ago
Attachment: | query_sortorder011.diff added |
---|
fixes enum sorting for "custom query", patch against trac-0.11b1 (SVN revision 6673)
by , 16 years ago
Attachment: | sort_reports_on_enums011.diff added |
---|
fixes enum sorting for "click on table header" sorts, patch against trac-0.11b1 (SVN revision 6673)
by , 16 years ago
Attachment: | default_reports011.diff added |
---|
patch for default reports to use CAST p.value AS INT for sorting
comment:10 by , 16 years ago
comment:11 by , 16 years ago
Description: | modified (diff) |
---|---|
Milestone: | 0.12 → 0.11 |
Status: | new → assigned |
- query_sortorder011.diff looks good.
- sort_reports_on_enums011.diff has a few minor nits concerning our coding style, and I have to have a look if there's no simpler way to do it
I'll try to get them in for 0.11.
Additional information: This behavior is only found when clicking on active ticket for a report (query), not when browsing a report.