On Jan 16, 10:46 pm, Christian Boos <[email protected]> wrote: > On 1/16/2012 12:17 AM, osimons wrote: > > ... > > 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. > > Well, then it looks like the current way we do the pagination is > flawed. It's strange that we never noticed it so far. I wonder if > we couldn't adopt the approach taken in the custom queries and > simply append LIMIT ... OFFSET ... to the query? > > > > > 1) Should we just ignore it? As I jokingly said on IRC, just rename > > report {8} to "Active Tickets, Mine last"? :-) > > > > Would be nice if we could fix this for 0.12.3 as apparently > r10892 made the problem more or differently visible.
Agree. Or just revert it if we find no good fix now. > > 2) Should we try to parse the report SQL and somehow extract any > > underlying order specification? > > It seems it could be "enough" to check if the SQL query is ending > with an `ORDER BY`, and in that case simply insert our extra > order columns, if any. But as that clause may contain arbitrary > expressions, we should at least check for an even count of > parentheses between the ORDER BY and the end of the query. If we can safely extract and reuse the ORDER BY clause of the underlying query, we have no additional columns to insert - we would just reuse that at top level. > > 3) Rewrite report pagination to not use a subquery? > > Yep Doing it in Python would likely mean making full query each time, but just return the number of rows (limit + offset) requested by the user. On the other hand, if we can be sure that the underlying report have no LIMIT or OFFSET clauses we could in theory just append the clauses to the main query and use that directly. There is no way of knowing what SQL reports look like in the wild though. Doing it in SQL would certainly be preferable. > > 4) Any other good ideas? > > Let's see if we can implement the above... Yes, lets give it a shot. IRC? :::simon -- 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.
