Error in included report query

[kevin]

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

[Christopher Lenz]

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?

[Jonas Borgström]

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.

[dluke@…]

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

[malign]

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

[alan]

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 :)

[vnaum@…]

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

[Christian Boos]

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.