My Tickets report fails

[tony.butt@…]

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'.

[Christian Boos]

I found two related tickets, #8559 and #9311, neither of which is resolved…

That error is a problem introduced by [10385] which implemented the feature requested in #9311.

The fix proposed in ticket:8559#comment:18 seems to be similar to the first I proposed in ticket:9311#comment:13, but this apparently had some issues with grouping. The last variant of the fix still needed to be tested with MySQL before being committed.

So let's finish #9311.