Error in Custom Report using subquery

[anonymous]

I created a custom report using the following SQLite query

SELECT p.value AS __color__,
  id AS ticket, summary, component, version, milestone, t.severity AS severity,
  (CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS owner,
  (CASE WHEN strftime('%%s','now') - changetime > 86400
        THEN round((strftime('%%s','now') - changetime)/86400,0) || ' days'
        WHEN strftime('%%s','now') - changetime > 3600
        THEN round((strftime('%%s','now') - changetime)/3600,0) || ' hours'
        ELSE (strftime('%%s','now') - changetime)/60 || ' mins'
  END) AS Modified,
  (SELECT author FROM ticket_change WHERE ticket = t.id AND field = 'comment' LIMIT 1) AS Modified_By,
  description AS _description,
  reporter AS _reporter
FROM ticket t, enum p
WHERE status IN ('new', 'assigned', 'reopened')
  AND p.name = t.priority AND p.type = 'priority'
ORDER BY changetime desc, p.value, milestone, t.severity, time 

The query executes correctly in SQLite, but trac complains about the subquery saying:
Report execution failed: no such column: t.id

[sid]

Yep, this did not work for me either. But by making the subquery a table instead (and changing %%s to %s), the query now works and displays active tickets by last modified date correctly:

SELECT p.value AS __color__,
  id AS ticket, summary, component, version, milestone, t.severity AS severity,
  (CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS owner,
  (CASE WHEN strftime('%s','now') - changetime > 86400
        THEN round((strftime('%s','now') - changetime)/86400,0) || ' days'
        WHEN strftime('%s','now') - changetime > 3600
        THEN round((strftime('%s','now') - changetime)/3600,0) || ' hours'
        ELSE (strftime('%s','now') - changetime)/60 || ' mins'
  END) AS Modified,
  author as Modified_By,
  description AS _description,
  reporter AS _reporter
FROM ticket t, enum p,
     (SELECT ticket, author, MAX(time) FROM ticket_change WHERE field = 'comment' GROUP BY ticket) tc
WHERE status IN ('new', 'assigned', 'reopened')
  AND p.name = t.priority AND p.type = 'priority'
  AND t.id = tc.ticket
ORDER BY changetime desc, p.value, milestone, t.severity, time

(tested and working on 0.10 release)

[sid]

I propose closing this as a worksforme.