Edgewall Software
Modify

Opened 14 years ago

Closed 12 years ago

Last modified 5 years ago

#2123 closed defect (duplicate)

Error in included report query

Reported by: kevin Owned by: Jonas Borgström
Priority: normal Milestone:
Component: report system Version: 0.9b1
Severity: normal Keywords: postgresql
Cc: klm@… Branch:
Release Notes:
API Changes:

Description

The "All Tickets By Milestone (Including closed)" report generates:

"Report execution failed: ERROR: column "modified" does not exist"

and nothing else.

This is a postgresql installation with Trac 0.9b1

Attachments (0)

Change History (11)

comment:1 by anonymous, 14 years ago

Cc: klm@… added

comment:2 by anonymous, 14 years ago

Component: generalreport system
Owner: changed from Jonas Borgström to daniel

comment:3 by Christopher Lenz, 14 years ago

Keywords: postgres added
Owner: changed from daniel to Jonas Borgström

I don't think that report can be made compatible with Postgres without removing the ordering by modification time. You can replace "modified" in the SQL by "changetime", but then Postgres will complain about a type conflict.

Jonas, do you have an idea here?

comment:4 by Jonas Borgström, 14 years ago

Resolution: wontfix
Status: newclosed

Yeah, the whole (-1) * p.value thing will never work with PostgreSQL without using CAST(p.value AS INT). I think we have no choice but to accept that not every sql report will work on any sql server.

The report module should probably be more officially deprecated and users recommended to use the roadmap module and custom queries instead.

I'm resolving this one as "wontfix" for now. I guess we could remove this report from db_default.py, but since most users still use sqlite I don't think we'll have to do that before 0.9.

comment:5 by dluke@…, 14 years ago

For anyone like me who stumbles across this in the future, here's SQL syntax that will make the above report work:

SELECT p.value AS color,

t.milestone AS group, (CASE status

WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;' ELSE

(CASE owner WHEN '$USER' THEN 'font-weight: bold' END)

END) AS style,

id AS ticket, summary, component, status, resolution,version, t.type AS type, priority, owner, changetime AS modified, time AS _time,reporter AS _reporter

FROM ticket t,enum p WHERE p.name=t.priority AND p.type='priority' ORDER BY (milestone IS NULL), milestone DESC, (status = 'closed'),

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

comment:6 by malign, 14 years ago

Thanks for the fix, dluke. Worked like a charm. =)

comment:7 by alan, 13 years ago

Note that in the query above, due to WikiFormatting, the underlined words color, group, and style should actually read: __color__, __group__, and __style__.

And yes, this fix does work like a charm on postres :)

comment:8 by vnaum@…, 12 years ago

Resolution: wontfix
Status: closedreopened

This patch fixes priority sorting and also fixes this issue: ticket:4715:default_reports011.diff

Tested with Postgres 7.4 and SQLite 2.8.17.

It only fixes 'initenv' command, so if your environment is already initiated (and I presume it is) - use solution suggested by dluke: (full SQL query for PostgreSQL attached for you convenience):

SELECT p.value AS __color__,
   t.milestone AS __group__,
   (CASE status 
      WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;'
      ELSE 
        (CASE owner WHEN $USER THEN 'font-weight: bold' END)
    END) AS __style__,
   id AS ticket, summary, component, status, 
   resolution,version, t.type AS type, priority, owner,
   changetime AS modified,
   time AS _time,reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  ORDER BY (milestone IS NULL), milestone DESC, (status = 'closed'), 
        (CASE status WHEN 'closed' THEN changetime ELSE (-1)*CAST(p.value AS int) END) DESC

in reply to:  8 comment:9 by Christian Boos, 12 years ago

Resolution: duplicate
Status: reopenedclosed

Replying to vnaum@vnaum.com:

This patch fixes priority sorting and also fixes this issue: ticket:4715:default_reports011.diff

Yes, I've noticed that, as I tested the same fix provided by trac-ja on #5543, which I'm about to commit.

comment:10 by Christian Boos, 12 years ago

Milestone: 0.9

comment:11 by Ryan J Ollos, 5 years ago

Keywords: postgresql added; postgres removed

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.