Edgewall Software

Opened 9 years ago

Closed 9 years ago

#10677 closed defect (duplicate)

My Tickets report fails

Reported by: tony.butt@… Owned by:
Priority: normal Milestone:
Component: report system Version:
Severity: normal Keywords:
Cc: Branch:
Release Notes:
API Changes:
Internal Changes:


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):
line 380, in _render_view
line 616, in execute_paginated_report
    cursor.execute("SELECT COUNT(*) FROM (%s) AS tab" % sql, args)
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:

       p.value AS __color__,
         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'.

Attachments (0)

Change History (1)

comment:1 by Christian Boos, 9 years ago

Resolution: duplicate
Status: newclosed

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.

Modify Ticket

Change Properties
Set your email in Preferences
as closed The ticket will remain with no owner.
The resolution will be deleted. Next status will be 'reopened'.
to The owner will be changed from (none) to the specified user.

Add Comment

E-mail address and name can be saved in the Preferences .
Note: See TracTickets for help on using tickets.