My Tickets report fails
|Reported by:||Owned by:|
We are running Trac 0.13-dev, currently r11028 The database backend is postgres 9.1.1, we also see this on postgres 8.4.11 Our python postgres interface is psycopg2 2.0.13
When running the default 'My Tickets' report, we see a failure message. This has been the case for a while, we have worked around by pasting in an older 'My Tickets' report.
The error report we see is:
Report execution failed: Traceback (most recent call last): File "/usr/local/lib/python2.6/dist-packages/Trac-0.13dev_r11028-py2.6.egg/trac/ticket/report.py", line 380, in _render_view offset) File "/usr/local/lib/python2.6/dist-packages/Trac-0.13dev_r11028-py2.6.egg/trac/ticket/report.py", line 616, in execute_paginated_report cursor.execute("SELECT COUNT(*) FROM (%s) AS tab" % sql, args) File "/usr/local/lib/python2.6/dist-packages/Trac-0.13dev_r11028-py2.6.egg/trac/db/util.py", line 65, in execute return self.cursor.execute(sql_escape_percent(sql), args) ProgrammingError: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 18: ORDER BY (owner = 'tjb' AND status = 'accepted') DESC, ^ ProgrammingError: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 18: ORDER BY (owner = 'tjb' AND status = 'accepted') DESC,
The SQL text causing this is:
SELECT DISTINCT p.value AS __color__, (CASE WHEN owner = $USER AND status = 'accepted' THEN 'Accepted' WHEN owner = $USER THEN 'Owned' WHEN reporter = $USER THEN 'Reported' ELSE 'Commented' END) AS __group__, t.id AS ticket, summary, component, version, milestone, t.type AS type, priority, t.time AS created, t.changetime AS _changetime, description AS _description, reporter AS _reporter FROM ticket t LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority' LEFT JOIN ticket_change tc ON tc.ticket = t.id AND tc.author = $USER AND tc.field = 'comment' WHERE t.status <> 'closed' AND (owner = $USER OR reporter = $USER OR author = $USER) ORDER BY (owner = $USER AND status = 'accepted') DESC, owner = $USER DESC, reporter = $USER DESC, CAST(p.value AS int), milestone, t.type, t.time
Which so far as I can tell is the current report SQL.
We are able to work-around this by pasting in an older, working, "My Tickets" SQL, which does not use the DISTINCT keyword.
I did see references to this in at least 2 other tickets, but both were 'resolved'.