inconsistent sorting in Custom Query

[anonymous]

The attached screenshot shows a Custom Query sorted by component, but the values don't appear in sorted order.

[stephane]

… additionally in 0.10b1 the sorting in the reports doesnt work at all.

If you go to:

​http://trac.edgewall.org/report/19

and click on the table header(s) like priority no sort is done.

(maybe it never worked … I don't remember when I used this last time)

[Matthew Good]

Please use the summary and description to actually explain the problem in a way that a developer can actually understand and reproduce the problem. Without the screenshot I'd have no idea what this referred to. I'll fill them in, but in the future please be more descriptive.

Also, please don't put two unrelated issues in the same ticket. The problem with sorting in report {19} is already described in #1375, so you don't need to file a ticket about it.

[sid]

What is the custom query you are using? I just tried some different custom queries here on the Trac site (running 0.10) and the component sorting was correct.

[sid]

Closing due to lack of information. I'm also unable to reproduce the error on t.e.o.

[mmanulis@…]

I am reopening this ticket because there is still a problem with sorting using a Custom Query.

The Problem

Sorting using severity and priority does not sort priority correctly.

Example Query

The following query will select all tickets belonging to the current user and sort them by priority in ascending order and then by severity.

SELECT p.value AS __color__,
              t.id AS ticket,
              severity,
              priority,
              status,
              summary,
              t.milestone,
              component,
              reporter
FROM ticket t,enum p
WHERE owner='$USER' AND
             t.status IN ('new', 'assigned', 'reopened') AND
             p.name=t.priority AND
             p.type='priority'
ORDER BY t.priority ASC, t.severity

Setup Description

Priority

There are three (3) priority levels defined in the system:

Priority Name	Order
high	1
medium	2
low	3

Severity

There are six (6) severity levels defined, named from 1 to 6.

Expected Result

The tickets should be sorted first by priority in ascending order, such that given the above setup configuration, the tickets will appear as follows:

1. Low priority
2. Medium priority
3. High priority

Multiple tickets that have the same priority should be sorted further by severity, such that tickets with highest severity will be shown first, unless a sorting order has been specified.

Actual Result

See attached screenshot​ for an example of the result.

Sorted tickets are ordered first by priority in the following way:

1. High priority
2. Low priority
3. Medium priority

Sorting by severity is done correctly.

Notes

• The same behavior is seen when sorting by severity first and then by priority
• When sorting in DESC order or not specifying the order, the actual results are the reverse of above, such that tickets appear as follows:
  1. Medium priority
  2. Low priority
  3. High priority

[mmanulis@…]

Incorrect sorting behavior screenshot

[Christian Boos]

This is actually a duplicate of #4715 which has patches ready for review. You can greatly help the process of getting them applied if you spend some time testing those patches and confirm if they're working for you. Thanks!

[vnaum@…]

Replying to cboos:

This is actually a duplicate of #4715

No, it is not.

#4715 only deals with wrong sorting for priority values ≥ 10, and mmanulis has priority values 1, 2 and 3.

I checked SQL query from comment:7, and it looks like it's just a misunderstanding of Trac's DB structure.

ORDER BY t.priority is not a right order (since t.priority is a string value, and being sorted alphabetically). To get proper sorting you must sort like this: ORDER BY p.value. Sorting by severity will not work like you intended either: you have to join enum table once again, for 'severity' value.

This is probably what you need:

SELECT p.value AS __color__,
              t.id AS ticket,
              severity,
              priority,
              status,
              summary,
              t.milestone,
              component,
              reporter
FROM ticket t
LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
LEFT JOIN enum s ON s.name = t.severity AND s.type = 'severity'
WHERE owner='$USER' AND
             t.status IN ('new', 'assigned', 'reopened')
ORDER BY p.value, s.value;

TracTicketsCustomFields has some info in this, the only thing you have to change - you're joining enum table, not ticket_custom.

Also, some SQL book or SQL guru help might be handy.

PS: I beleive it's not a "Custom Query" (View Tickets → Custom Query) on the screenshot, but just a "report". Since we have SQL for it - I assume you're clicking "Edit Report" button, right?

[Christian Boos]

Ok, so:

• attachment:default_reports011.diff:ticket:4715​ would fix a similar issue than the one reported by mmanulis in comment:7 for the default SQL reports in new environments, and of course would not magically fix existing reports (probably derived from the original "broken" ones).
• attachment:query_sortorder011.diff:ticket:4715​ would fix the sorting order for enums for Custom Queries, but apparently this is not what mmanulis was using and the original report was not about sorting issues with an enum field, so it's not related either.

mmanulis, you have no other choice than to fix your SQL reports like vnaum explained [commnet:9 above].

[Christian Boos]

As for the original issue reported by sergiu, I think this simply worksforme now.

I still have no idea about what the original problem could have been (0.10 only?), but if this happens again or to someone else, then please describe the problem in such a way that someone could reproduce it starting from a newly created environment.

[anonymous]

Replying to vnaum@vnaum.com:

Thanks, I'll work on getting the SQL query right.

P.S. - Thanks for the SQL example above. It still produces the same result as original query. I'll figure out the exact query and post it here at a later date.

[Christian Boos]

Replying to cboos:

As for the original issue reported by sergiu, I think this simply worksforme now.

I still have no idea about what the original problem could have been

For the record, I think the original issue (attachment:track.png​) was related to the mixing of tickets from the current query and those saved in the session (code which was removed in r6792).