Default "my tickets" report doesn't work for Postgres

[josh@…]

The default "my tickets" report has a query like this:

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

When running under postgres, the error "ORDER BY items must appear in the select list if SELECT DISTINCT is specified" is reported.

I fixed it by changing the query to this:

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,
       (owner = $USER AND status = 'accepted') as a,
       (owner = $USER) as b,
       (reporter = $USER) as c,
       CAST(p.value AS int) as d
  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

There might be a better way, but this seemed to work.

[josh@…]

Of course I'm only now seeing that those "a, b, c, d" columns now show up in the report, which of course you don't really want.

[Jun Omae]

Thanks for reporting. That is a duplicate of #10677.

[Jun Omae]

I've posted proposed fix at comment:32:ticket:9311.

[josh@…]

Thanks! That solution looks pretty good to me.