Edgewall Software
Modify

Opened 12 years ago

Closed 12 years ago

Last modified 4 years ago

#6334 closed defect (duplicate)

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

Reported by: Goodbyte Owned by: Jonas Borgström
Priority: normal Milestone:
Component: general Version: 0.10.4
Severity: normal Keywords: postgresql, cast
Cc: Branch:
Release Notes:
API Changes:

Description

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.

Attachments (0)

Change History (8)

comment:1 by anonymous, 12 years ago

Version: 0.10.4

comment:2 by rfmorris, 12 years ago

I get this on 0.11dev-6047 too…

comment:3 by rfmorris, 12 years ago

Keywords: cast added

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

comment:4 by anonymous, 12 years ago

Cc: algroth@… added

comment:5 by anonymous, 12 years ago

Cc: algroth@… removed

comment:6 by anonymous, 12 years ago

I am also getting this, on 0.95

comment:7 by Christian Boos, 12 years ago

Resolution: duplicate
Status: newclosed

See #5543.

comment:8 by Ryan J Ollos, 4 years ago

Keywords: postgresql cast → postgresql, cast

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Jonas Borgström.
The resolution will be deleted. Next status will be 'reopened'.
to as closed The owner will be changed from Jonas Borgström 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.