Edgewall Software
Modify

Opened 10 years ago

Closed 10 years ago

Last modified 10 years ago

#11480 closed defect (duplicate)

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

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

Description

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.

Attachments (0)

Change History (4)

comment:1 by josh@…, 10 years ago

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.

comment:2 by Jun Omae, 10 years ago

Resolution: duplicate
Status: newclosed

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

comment:3 by Jun Omae, 10 years ago

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

comment:4 by josh@…, 10 years ago

Thanks! That solution looks pretty good to me.

Modify Ticket

Change Properties
Set your email in Preferences
Action
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.