Edgewall Software
Modify

Opened 12 years ago

Closed 11 years ago

Last modified 11 years ago

#4715 closed defect (fixed)

SQLite and > 9 priorities values does not sort correctly

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:

Description (last modified by Christian Boos)

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.

Attachments (5)

query_sortorder.diff (790 bytes ) - added by vnaum@… 11 years ago.
fixes enum sorting for "custom query"
sort_reports_on_enums.diff (1.5 KB ) - added by vnaum@… 11 years ago.
fixes enum sorting for "click on table header" sorts
query_sortorder011.diff (1.4 KB ) - added by vnaum@… 11 years ago.
fixes enum sorting for "custom query", patch against trac-0.11b1 (SVN revision 6673)
sort_reports_on_enums011.diff (1.4 KB ) - added by vnaum@… 11 years ago.
fixes enum sorting for "click on table header" sorts, patch against trac-0.11b1 (SVN revision 6673)
default_reports011.diff (3.3 KB ) - added by vnaum@… 11 years ago.
patch for default reports to use CAST p.value AS INT for sorting

Download all attachments as: .zip

Change History (27)

comment:1 by StefanC, 12 years ago

Additional information: This behavior is only found when clicking on active ticket for a report (query), not when browsing a report.

in reply to:  1 comment:2 by Matthew Good, 12 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 Matthew Good, 12 years ago

Component: generalreport system
Owner: changed from Jonas Borgström to Matthew Good

comment:4 by StefanC, 12 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 :-)

comment:5 by Christian Boos, 12 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.

in reply to:  5 comment:6 by jim@…, 12 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 vnaum@…, 11 years ago

Attachment: query_sortorder.diff added

fixes enum sorting for "custom query"

by vnaum@…, 11 years ago

Attachment: sort_reports_on_enums.diff added

fixes enum sorting for "click on table header" sorts

comment:7 by vnaum@…, 11 years ago

Cc: vnaum@… added
Owner: changed from Matthew Good to anonymous
Status: newassigned

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 Christian Boos, 11 years ago

Owner: changed from anonymous to Christian Boos
Status: assignednew

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 vnaum@…, 11 years ago

Attachment: query_sortorder011.diff added

fixes enum sorting for "custom query", patch against trac-0.11b1 (SVN revision 6673)

by vnaum@…, 11 years ago

fixes enum sorting for "click on table header" sorts, patch against trac-0.11b1 (SVN revision 6673)

comment:9 by anonymous, 11 years ago

Patches I promised for trac-0.11b1 (SVN revision 6673):

by vnaum@…, 11 years ago

Attachment: default_reports011.diff added

patch for default reports to use CAST p.value AS INT for sorting

comment:10 by vnaum@…, 11 years ago

Default reports were also affected by this bug (weird sorting for >9 priorities)

Patch for default reports to use CAST p.value AS INT for sorting.

Also fixes #2123 (aka #3038).

Tested with Postgres 7.4 and SQLite 2.8.17.

comment:11 by Christian Boos, 11 years ago

Description: modified (diff)
Milestone: 0.120.11
Status: newassigned
  • 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.

in reply to:  11 comment:12 by anonymous, 11 years ago

Replying to cboos:

  • 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'm sorry for my coding style - I'm just a Python newbie trying to fix most annoying bugs…

Still, I have doubts about "simpler way" - after all, sorting is done after fetching strings from the database, and we have only strings to compare (that's why one more DB query is necessary).

The only other way I could think of is amending first query to fetch priority.value from DB, then save it into some kind of hidden column, and then sort using that values. That doesn't look like "simpler way" to me :-)

I'll try to get them in for 0.11.

Thank you!

What about default_reports011.diff? The fix from #5543 fixes just report:6 (which was broken badly), while this one fixes sorting for all the reports that sort on priority (these just had weird sorting).

comment:13 by Christian Boos, 11 years ago

#3671 was closed as duplicate.

comment:14 by anonymous, 11 years ago

Tried the query_sortorder.diff and sort_reports_on_enums.diff on an instance of 0.10.4 and the priorities are still not sorted correctly.

I'm using priorities as follows:

  • 1 = high
  • 2 = medium
  • 3 = low

When sorting in ascending order, the tickets show up in the following order:

  1. high
  2. low
  3. medium

The descending order is reversed, with medium being first and high being last.

comment:15 by mmanulis@…, 11 years ago

Cc: mmanulis@… added

comment:16 by vnaum@…, 11 years ago

Ultra strange.

First of all, you shouldn't bee seeing this bug at all - your priority values are all < 10, so sorting them as strings should work without any patching.

Did you remove precompiled *.pyc for files affected with patches (query.pyc and report.pyc in your case)? Are you using mod_python/FastCGI/tracd? Did you restart it after patching and removing *.pyc?

What database backend you're using - SQLite, PostgreSQL, Mysql? Could you provide this query results:

select * from enum where type='priority';

If you don't know how to do this and it is acceptable for you to disclose your environment contents (i.e. there's no secret information in it) you can just share full environment backup (see TracBackup).

With which report you got improper sorting? query_sortorder.diff only affects "custom query", and sort_reports_on_enums.diff only works when you click table header for sorting - while you didn't, it shows up the result you got from stored SQL report (this should be fixed with default_reports011.diff, but it only affects creating environment).

comment:17 by anonymous, 11 years ago

I patched the installation files that were downloaded from trac.edgewall.org and then I ran the setup.py install script which copied over the old files, then I restarted Apache.

I'm running Trac using mod_python in Apache with SQLite as the DB.

The output of the query is:

TypeNameValue
priorityhigh1
prioritylow3
prioritymedium2

The improper sorting happens on Custom Queries that I create. For more information on the problem I'm experiencing, you can see the duplicate ticket #3671

in reply to:  17 comment:18 by vnaum@…, 11 years ago

Replying to anonymous:

Your issue is not related to this bug.

Your priority.values are all below 10 (this means sorting using string comparison will work for it), and this bug (and patches) only affects users who have more then 10 priorities (and, therefore, need integer sorting).

I recommend you re-opening #3671 (if my answer there does not solve your issue). If it does - don't forget to thank me there :-)

comment:19 by Christian Boos, 11 years ago

Resolution: fixed
Status: assignedclosed

Patches committed in [6840:6842]. Thanks again for contributing them!

comment:20 by vnaum@…, 11 years ago

Thanks for applying :-) BTW, it was applied to trunk, not to tags/trac-0.11b1 - will these changes get to 0.11?

An one more thing… What about attachment:default_reports011.diff?

Default reports still have wrong sorting, and this bug will pop up as soon as one will have > 9 priorities…

The fix from #5543 fixes just report:6 (which was broken badly), while this one fixes sorting for all the reports that sort on priority (these just had weird sorting).

Should I create new bug?

Or, maybe, re-open #5543?

in reply to:  20 comment:21 by Christian Boos, 11 years ago

Replying to vnaum@vnaum.com:

Thanks for applying :-) BTW, it was applied to trunk, not to tags/trac-0.11b1 - will these changes get to 0.11?

trunk is still 0.11dev and 0.11rc1, 0.11 itself and then 0.11-stable will be branched from trunk.

An one more thing… What about attachment:default_reports011.diff?

That was r6840, slightly reformatted.

comment:22 by anonymous, 11 years ago

Sweet!

Thanks again, now my soul can rest peacefully :-)

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Christian Boos.
The resolution will be deleted.
to The owner will be changed from Christian Boos to the specified user.

Add Comment


E-mail address and name can be saved in the Preferences .
 
Note: See TracTickets for help on using tickets.