id,summary,reporter,owner,description,type,status,priority,component,severity,resolution,keywords,cc,release
5568,[Patch] SQL query failures with MySQL,scouret@alten.fr,rjollos,Hello_\r\n\r\nI ran into SQL requests issues while running TracTicketStatsPlugin with a mysql backend :\r\n\r\n 1. `(1054_ "Unknown column 'created' in 'where clause'")`\r\n This is due to Mysql poor Alias handling_ i propose to use the column name (t.time) instead of alias name(created) as shown in following patch :\r\n\r\n{{{\r\nticketstats.py \r\n@@ -88_13 +88_13 @@\r\n-               cursor.execute("SELECT t.type AS type_ owner_ status_ time AS created FROM ticket t_ enum p WHERE p.name = t.priority AND p.type = 'priority' AND created <= %s" % to_timestamp(at_date))\r\n+               cursor.execute("SELECT t.type AS type_ owner_ status_ time AS created FROM ticket t_ enum p WHERE p.name = t.priority AND p.type = 'priority' AND t.time <= %s" % to_timestamp(at_date))\r\n}}}\r\n\r\n 2. The next issue is related with the use of 'enum p' . Here comes the proposed patch :\r\n\r\n{{{\r\nticketstats.py \r\n@@ -61_7 +61_7 @@\r\n-               cursor.execute("SELECT t.id_ tc.field_ tc.time_ tc.oldvalue_ tc.newvalue_ t.priority FROM ticket_change tc_ enum p INNER JOIN ticket t ON t.id = tc.ticket AND tc.time > %s AND tc.time <= %s WHERE p.name = t.priority AND p.type = 'priority' ORDER BY tc.time" % (to_timestamp(from_date)_ to_timestamp(at_date)))\r\n+               cursor.execute("SELECT t.id_ tc.field_ tc.time_ tc.oldvalue_ tc.newvalue_ t.priority FROM enum p_ ticket_change tc INNER JOIN ticket t ON t.id = tc.ticket AND tc.time > %s AND tc.time <= %s WHERE p.name = t.priority AND p.type = 'priority' ORDER BY tc.time" % (to_timestamp(from_date)_ to_timestamp(at_date)))\r\n}}}\r\n\r\nHope this helps. Thanks for all your good job with this plugin\r\n,defect,closed,normal,TracTicketStatsPlugin,blocker,fixed,mysql,,0.11