On Jan 15, 9:35 pm, Steffen Hoffmann <[email protected]> wrote:
> Hello,
>
> it has been brought to my attention by a request in IRC #trac today,
> that a user is experiencing following issue with report 6 in default
> configuration on latest stable 0.12:
>
> 'My Tickets' is shown _after_ 'Active Tickets'
>
> Before the changeset it was ok. Work-around is to rename groups or use
>
> ORDER BY (COALESCE(owner, '') = '$USER') DESC, ...
>
> instead of
>
> ORDER BY (owner = '$USER') DESC, ...
>
> Hint: The underlying query seem to not get the DESC rule, when the
> column is added the new way, as SQL logging reveals ([2] last line).
> Well, looks like there is simply no way to attach a 'DESC' to the
> __group__ directive, right?
>
> Since I'm not a SQL guru, I start with discussion here rather than
> filing a ticket right-away.
>
> Sincerely,
>
> Steffen Hoffmann
>
> [1]http://trac.edgewall.org/changeset/10892
> [2]http://pastebin.com/G3criDRQ
Thanks Steffen,
It looks more like the change revealed another limitation of reports,
but in a sense it is also a regression. I'll try to summarize the
issue as I'm currently not quite sure how to solve it:
The issue adressed by r10892 is a problem introduced by report
pagination. Pagination essentially uses the "SELECT ..." report as a
subquery, and then adds a new level on top that does "SELECT * FROM
(<report sql>) LIMIT 100 OFFSET 0" (or whatever the current page and
size are). The problem is that even though the subquery is ordered
correctly, there is no guarantee that the order will be preserved when
the new SELECT grabs rows from it - if the toplevel SELECT wants order
it must explicitly state its own order.
Very often the order is as expected simply due to the efficient
caching nature of databases as we perform several quite similar
queries before the main report query - for instance a query to detect
the column names in use. But, the order is not always correct. For a
regular ordered query it often makes little difference if some rows
may have switched around a little - it can be quite hard to detect for
a normal human. However, with grouping (__group__) this becomes more
obvious as we switch group header each time a new value appears. So
what used to happen was that the same group could appear twice or more
in the report output. Very confusing.
So, the essence of the current situation is this: In
trac.ticket.report.ReportModule.execute_paginated_report() we detect
columns in use, and we detect if one of the columns is named
'__group__'. If so, we make sure it appears first in sort order. What
we cannot currently detect is if __group__ is sorted ASC or DESC by
the report SQL. Default report {8} "Active Tickets, Mine first"
includes "...ORDER BY (COALESCE(owner, '') = %s) DESC..". The SQL
could use and write anything for how it sorts, and all we know from
reading cursor description is that there is a '__group__'. But, at
cursor level we do not know that the grouping in {8} is based on
'owner'. The report could have said "...ORDER BY __group__ DESC..."
and it would be the same. Or it could have dropped ordering by
'__group__' as that would be inserted by the report pagination code
anyway following r10892 - but then without explicit order no DESC flag
could be added. In any report we have no control over how the ordering
is written or what fields it relates to, grouped or not.
No method that I'm aware of can make me detect the ASC/DESC order
specified for grouping - in a way that allows us to easily carry it
onto the toplevel select to maintain the expected order.
So:
1) Should we just ignore it? As I jokingly said on IRC, just rename
report {8} to "Active Tickets, Mine last"? :-)
2) Should we try to parse the report SQL and somehow extract any
underlying order specification?
3) Rewrite report pagination to not use a subquery?
4) Any other good ideas?
:::simon
https://www.coderesort.com
http://trac-hacks.org/wiki/osimons
--
You received this message because you are subscribed to the Google Groups "Trac
Development" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/trac-dev?hl=en.
