Query "All Tickets By Milestone (Including closed)" doesn't work on PostgreSQL

[Goodbyte]

When I try to select the default-query "All Tickets By Milestone (Including closed)" I get the error "Report execution failed: column "modified" does not exist LINE 16: (CASE status WHEN 'closed' THEN modified ELSE (-1)*p… ^{".
According to the PostgreSQLs manual (​http://www.postgresql.org/docs/current/static/queries-order.html), this is because of using a column-alias ('modified') in the 'ORDER BY'-clause as part of a expression.}

Changing the last line of the query from

(CASE status WHEN 'closed' THEN modified ELSE (-1)*p.value END) DESC

to

(CASE status WHEN 'closed' THEN changetime ELSE (-1)*p.value END) DESC

solves that issue and should be compatible to MySQL and SQLite.

[rfmorris]

I get this on 0.11dev-6047 too…

[rfmorris]

Interestingly, applying the recommended change didn't fix this for me on 0.11dev — probably because it's not generating the same error :D That's what I get for reading in a hurry.

I'm using Trac 0.11dev-6047 on Postgres 8.2 — the problem I was (ultimately) having was the implicit cast in (-1)*p.value, which just so happens to be in the last line of this query and the same line referenced in the ticket description.

To fix it, the cast must be made explicit — so the line (after the modifications described in the description)…

(CASE status WHEN 'closed' THEN changetime ELSE (-1)*p.value END) DESC 

…gets changed to (reformatted for clarity):

(CASE status 
    WHEN 'closed' THEN 
        changetime 
    ELSE 
        (-1)*CAST(p.value AS integer) 
END) DESC

[anonymous]

I am also getting this, on 0.95

[cboos]

See #5543.