Opened 10 years ago
Last modified 2 years ago
#11571 closed defect
Dynamic variable assignment in ticket reports fails in some cases — at Initial Version
Reported by: | Owned by: | ||
---|---|---|---|
Priority: | normal | Milestone: | 0.12.6 |
Component: | report system | Version: | 1.0.1 |
Severity: | normal | Keywords: | |
Cc: | Branch: | ||
Release Notes: | |||
API Changes: | |||
Internal Changes: |
Description
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:
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