Report execution failed: near ";": syntax error

[Karmadude]

After upgrading to 0.11, the My Tickets report keeps throwing this error

Report execution failed: near ";": syntax error

Here is the query:

SELECT p.value AS __color__,
   (CASE status WHEN 'assigned' THEN 'Assigned' ELSE 'Owned' END) AS __group__,
   id AS ticket, component, summary, version, milestone,
   t.type AS type, priority, t.time AS created,
   changetime AS _changetime, description AS _description,
   reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  WHERE t.status <> 'closed' AND owner = '$USER'
  ORDER BY (status = 'assigned')  DESC, p.value, milestone, t.type, t.time;

[Emmanuel Blot]

Not a blocker issue, Trac keeps working but with this specific report.

[Karmadude]

Replying to eblot:

Not a blocker issue, Trac keeps working but with this specific report.

Sorry, are you saying this is a known issue? Is there a work around? We use that report a lot.

[Emmanuel Blot]

Replying to Karmadude:

Sorry, are you saying this is a known issue? Is there a work around? We use that report a lot.

Not that I know of ("My Ticket" report works fine on my setup, so maybe something got wrong when updating).

blocker status is for situations where Trac cannot be started or simply crashes for instance. One report in trouble does not block the other Trac features.

Can you try the following report:

SELECT p.value AS __color__,
   (CASE status WHEN 'accepted' THEN 'Accepted' ELSE 'Owned' END) AS __group__,
   id AS ticket, summary, component, version, milestone,
   t.type AS type, priority, time AS created,
   changetime AS _changetime, description AS _description,
   reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  WHERE t.status <> 'closed' AND owner = $USER
  ORDER BY (status = 'accepted') DESC, p.value, milestone, t.type, time

[Karmadude]

That fixed it, thanks.

[Emmanuel Blot]

I've copied and pasted the report from a pristine Trac 0.11 environment, I'm closing this bug as worksforme, assuming there was a glitch on your environment.

[Karmadude]

Actually just removing the semicolon fixed the issue. Weird!

[dag.viggo@…]

I can verify that in my environment every report in which the query ends with a semicolon the reports throw a stacktrace. Output from trac.log:

2008-07-16 18:09:49,413 Trac[__init__] DEBUG: Dispatching <Request "GET u'/report/151'">
2008-07-16 18:09:49,421 Trac[__init__] DEBUG: Executing report with SQL "-- List tickets assigned, group by ticket owner
-- This report demonstrates the use of full-row display.

SELECT p.value AS __color__,
       owner,
       id AS ticket, summary, component, milestone, t.type AS type, severity, time AS created,
       --description AS _description_,
       changetime AS _changetime, reporter AS _reporter
  FROM ticket t, enum p
  WHERE (status = 'assigned' OR status = 'new')
    AND p.name = t.priority AND p.type = 'priority' and owner ILIKE 'DVL'
  ORDER BY owner, p.value, severity, time;
"
2008-07-16 18:09:49,421 Trac[__init__] DEBUG: Request args: {'id': u'151'}
2008-07-16 18:09:49,422 Trac[__init__] DEBUG: Prepare chrome data for request
2008-07-16 18:09:49,433 Trac[__init__] DEBUG: Retrieving session for ID u'dvl'
2008-07-16 18:09:49,435 Trac[__init__] DEBUG: Retrieving session for ID u'dvl'
2008-07-16 18:09:49,435 Trac[__init__] ERROR: current transaction is aborted, commands ignored until end of transaction block
Traceback (most recent call last):
  File "/usr/lib/python2.4/site-packages/Trac-0.11-py2.4.egg/trac/web/main.py", line 423, in _dispatch_request
    dispatcher.dispatch(req)
  File "/usr/lib/python2.4/site-packages/Trac-0.11-py2.4.egg/trac/web/main.py", line 219, in dispatch
    data, content_type)
  File "/usr/lib/python2.4/site-packages/Trac-0.11-py2.4.egg/trac/web/chrome.py", line 708, in render_template
    if not req.session or not int(req.session.get('accesskeys', 0)):
  File "/usr/lib/python2.4/site-packages/Trac-0.11-py2.4.egg/trac/web/api.py", line 168, in __getattr__
    value = self.callbacks[name](self)
  File "/usr/lib/python2.4/site-packages/Trac-0.11-py2.4.egg/trac/web/main.py", line 257, in _get_session
    return Session(self.env, req)
  File "/usr/lib/python2.4/site-packages/Trac-0.11-py2.4.egg/trac/web/session.py", line 142, in __init__
    self.get_session(req.authname, authenticated=True)
  File "/usr/lib/python2.4/site-packages/Trac-0.11-py2.4.egg/trac/web/session.py", line 156, in get_session
    super(Session, self).get_session(sid, authenticated)
  File "/usr/lib/python2.4/site-packages/Trac-0.11-py2.4.egg/trac/web/session.py", line 56, in get_session
    (sid, int(authenticated)))
  File "/usr/lib/python2.4/site-packages/Trac-0.11-py2.4.egg/trac/db/util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "/usr/lib/python2.4/site-packages/Trac-0.11-py2.4.egg/trac/db/util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
ProgrammingError: current transaction is aborted, commands ignored until end of transaction block

The postgresql log says:

ERROR:  syntax error at or near ";" at character 869

If I remove the semicolon, it works as it should.

This is with python 2.4.4c1, trac 0.11 working against a postgres 8.1 database.