Reports seem to generate bad SQL with MySQL

[jesse.lovelace@…]

When I run reports with dynamic variables using Mysql I get this debug:

Trac[report] DEBUG: Executing report with SQL "SELECT p.value AS __color__,
   (CASE owner
     WHEN 'concat(', %s, ')' THEN 'My Tickets: '
     ELSE 'Active Tickets: '
    END)||t.milestone AS __group__,
   t.id AS ticket, summary, component, version AS _version, t.milestone as milestone, t.type AS type,
   (CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS owner,
   t.time AS created,
   changetime AS _changetime, t.description AS _description,
   reporter AS _reporter
  FROM ticket t, enum p, milestone m
  WHERE status IN ('new', 'assigned', 'reopened')
AND p.name = t.priority AND p.type = 'priority'
AND m.name = t.milestone
  ORDER BY (owner = 'concat(', %s, ')') DESC, m.due, p.value, severity, t.time" (['jesse', 'jesse'])

Example is "Active Tickets by Milestone, Mine first"

It seems that concat is generated wrong. Using MySQL 5.0.18 on FreeBSD 6.1. with py-MySQLdb-1.2.1.p2 (Python 2.4.2) and libmysqlclient 5.0.22.

No output is generated by the report.

[trac@…]

I have the same problem, using MySQL 5.0.21, Fedora Core 4, Python 2.4.3. I don't know what version of libmysqlclient or py-MySQLdb I am using, though (I'm not sure how to find out).

All queries with the $USER dynamic variable fail with the following error:

Report execution failed: (1064, "You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the right syntax
to use near ' 'anonymous', ')'\r\n ORDER BY (status = 'assigned') DESC,
p.value, milestone, t.' at line x")

I'm using Trac 10.3devel updated nightly from the 0.10-stable branch from SVN.

[jesse.lovelace@…]

A little more investigation shows that Trac in the source:/trunk/trac/ticket/report.py​ Trac expects the database concationation to use a binary operator in string literals—since MySQL uses a function, the function is being inserted into the string literal:

'$USER' becomes 'concat(', '%s', ')'

Which doesn't really make sense to the DB. I'm not the expert, but I think the sql_sub_vars (in source:/trunk/trac/ticket/report.py​ especially line 411) needs to be changed to support both functional concat and binary op.

[Matthew Good]

The single quotes around $USER aren't necessary and I've update TracReports and removed them. I think I should be able to support them in a way that's compatible with MySQL, but for now I'd recommend not putting single quotes around the parameters.

[Matthew Good]

Oh, I just noticed that built-in report 7 uses '$USER', so I'll also remove the single quotes from it there.

[Matthew Good]

Fixed in r4453 and r4454.