Edgewall Software

Opened 18 years ago

Closed 18 years ago

#4387 closed defect (fixed)

Reports seem to generate bad SQL with MySQL

Reported by: jesse.lovelace@… Owned by: Matthew Good
Priority: normal Milestone: 0.10.4
Component: report system Version: 0.10.3
Severity: minor Keywords: report sql error
Cc: Branch:
Release Notes:
API Changes:
Internal Changes:


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.

Attachments (0)

Change History (5)

comment:1 by trac@…, 18 years ago

Component: generalreport system
Keywords: report sql error added
Owner: changed from Jonas Borgström to Matthew Good
Severity: normalmajor

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.

comment:2 by jesse.lovelace@…, 18 years ago

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.

comment:3 by Matthew Good, 18 years ago

Milestone: 0.10.4
Severity: majorminor
Status: newassigned
Version: devel0.10.3

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.

comment:4 by Matthew Good, 18 years ago

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

comment:5 by Matthew Good, 18 years ago

Resolution: fixed
Status: assignedclosed

Fixed in r4453 and r4454.

Modify Ticket

Change Properties
Set your email in Preferences
as closed The owner will remain Matthew Good.
The resolution will be deleted. Next status will be 'reopened'.
to The owner will be changed from Matthew Good to the specified user.

Add Comment

E-mail address and name can be saved in the Preferences .
Note: See TracTickets for help on using tickets.