[Trac-tickets] [The Trac Project] #3038: Report "All Tickets By Milestone" does not work with PostgreSQL

[The Trac Project]

#3038: Report "All Tickets By Milestone" does not work with PostgreSQL
-----------------------+----------------------------------------------------
 Reporter:  anonymous  |       Owner:  jonas
     Type:  defect     |      Status:  new  
 Priority:  normal     |   Milestone:       
Component:  general    |     Version:  0.9.4
 Severity:  normal     |    Keywords:       
-----------------------+----------------------------------------------------
 When I want to view 6th report (All Tickets By Milestone) Trac reports
 error:
 {{{
 Report execution failed:
 ERROR: column "modified" does not exist
 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 'mn' 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 modified ELSE (-1)*p.value END)
 DESC
 }}}

 I have corrected it with change in database:
 {{{
 update report set sql='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
 ' where id=6;
 }}}

 I have changed:
  1. in ORDER BY I renamed {{{modified}}} to {{{changetime}}} (PostgreSQL
 doesn't use aliases in ORDER BY)
  1. cast p.value to int

-- 
Ticket URL: <http://projects.edgewall.com/trac/ticket/3038>
The Trac Project <http://trac.edgewall.com/>