[Patch] SQL query failures with MySQL

[scouret@…]

Hello,

I ran into SQL requests issues while running TracTicketStatsPlugin with a mysql backend :

1. (1054, "Unknown column 'created' in 'where clause'") 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 :

ticketstats.py 
@@ -88,13 +88,13 @@
-               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))
+               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))

2. The next issue is related with the use of 'enum p' . Here comes the proposed patch :

ticketstats.py 
@@ -61,7 +61,7 @@
-               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)))
+               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)))

Hope this helps. Thanks for all your good job with this plugin

[anonymous]

omg, thanks for the fix was driving me crazy... could it be that enum is some reserved word?

[anonymous]

Replying to anonymous:

omg, thanks for the fix was driving me crazy... could it be that enum is some reserved word?

No.

First issue is that the column aliases are not allowed in WHERE clause.
​http://dev.mysql.com/doc/refman/5.1/en/problems-with-alias.html

Second is that the JOIN has the higher precedence, so the table being joined must be next to JOIN clause (i.e. must go after tables not participating in JOIN).
​http://dev.mysql.com/doc/refman/5.1/en/join.html

BTW. I was about to submit same patches.

[Bart Ogryczak]

patch for the MySQL issues

[Ryan J Ollos]

Reassigning ticket to new maintainer.

[Stefan Rungardt]

After applying the patch it works fine as long as Milestone = 'All Milestones'. If I choose a specific milestone I get a similar error though:

2010-12-09 16:10:54,163 Trac[main] ERROR: column "<milestone_name>" does not exist

[Stefan Rungardt]

Please ignore the previous comment. I installed the patch incorrectly.

[Ryan J Ollos]

Thanks for letting me know that the patch works with MySQL. I'll test it with SQLite and apply if it doesn't break anything.

[Ryan J Ollos]

(In [9660]) Fixed SQL queries that were incompatible with MySQL. Fixes #5568.

[Ryan J Ollos]

(In [9661]) Merged [9660] from trunk to branches/0.11 and branches/0.12. Refs #5568.

[Ryan J Ollos]

(In [13104]) Refs #8600, #5568, #3594: Removed the 0.11 and 0.12 branches. The trunk will be kept compatible with 0.11 and higher for now.