Dynamic variable assignment in ticket reports fails in some cases

[Quince <eimacdude@…>]

The following ticket report query worked just fine with MySQL, but fails after migration to PostgreSQL:

SELECT p.value AS __color__, status AS __group__,
    id AS ticket, summary, t.type AS type, priority, milestone, component, version, s.value AS "Start", e.value AS "End", c.value AS "% Done", r.value as "Hours Left"
  FROM ticket t
  LEFT OUTER JOIN ticket_custom s ON (t.id = s.ticket AND s.name = 'due_assign')
  LEFT OUTER JOIN ticket_custom e ON (t.id = e.ticket AND e.name = 'due_close')
  LEFT OUTER JOIN ticket_custom c ON (t.id = c.ticket AND c.name = 'complete')
  LEFT OUTER JOIN ticket_custom r ON (t.id = r.ticket AND r.name = 'estimatedhours')
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  WHERE t.status <> 'closed' AND owner = $USER
  ORDER BY status = 'in_work' DESC, status = 'in_QA' DESC, status = 'assigned' DESC, status, CAST(p.value AS bigint)

Replacing $USER with the actual username works. However, this isn't the problem all by itself, because something like SELECT * FROM ticket as t WHERE owner = $USER does work fine. The error itself shows that in the above example, $USER is replaced with %s for whatever reason:

Report execution failed:

IndexError: list index out of range

SELECT COUNT(*) FROM (
SELECT p.value AS __color__, status AS __group__,
    id AS ticket, summary, t.type AS type, priority, milestone, component, version, s.value AS "Start", e.value AS "End", c.value AS "% Done", r.value as "Hours Left"
  FROM ticket t
  LEFT OUTER JOIN ticket_custom s ON (t.id = s.ticket AND s.name = 'due_assign')
  LEFT OUTER JOIN ticket_custom e ON (t.id = e.ticket AND e.name = 'due_close')
  LEFT OUTER JOIN ticket_custom c ON (t.id = c.ticket AND c.name = 'complete')
  LEFT OUTER JOIN ticket_custom r ON (t.id = r.ticket AND r.name = 'estimatedhours')
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  WHERE t.status <> 'closed' AND owner = %s
  ORDER BY status = 'in_work' DESC, status = 'in_QA' DESC, status = 'assigned' DESC, status, CAST(p.value AS bigint)
) AS tab

Why? How do I get around this error while waiting for a fix?

Here's another failure case where $USER gets replaced with %s instead of the username when running trac with PostgreSQL but wasn't an issue with MySQL:

SELECT p.value AS __color__,
    CONCAT('Milestone ', milestone, CASE WHEN m.due > 0 THEN CONCAT(' (due ', TO_TIMESTAMP(m.due/1000000)::TIMESTAMP, ')') ELSE '' END) 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, owner, t.type AS type, status, component, version, s.value AS "Start", e.value AS "End", c.value AS "% Done", r.value as "Hours Left"
  FROM ticket t
  LEFT JOIN milestone m ON m.name = t.milestone
  LEFT OUTER JOIN ticket_custom s ON (t.id = s.ticket AND s.name = 'due_assign')
  LEFT OUTER JOIN ticket_custom e ON (t.id = e.ticket AND e.name = 'due_close')
  LEFT OUTER JOIN ticket_custom c ON (t.id = c.ticket AND c.name = 'complete')
  LEFT OUTER JOIN ticket_custom r ON (t.id = r.ticket AND r.name = 'estimatedhours')
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  ORDER BY (m.due > 0) DESC, m.due, (milestone IS NULL), milestone, status = 'closed', (CASE status WHEN 'closed' THEN changetime ELSE (-1) * CAST(p.value AS bigint) END) DESC