[SQL] MySQLQuery generates invalid SQL when mixing limit and for update

Bug #857336 reported by Luis Fernando Planella Gonzalez on 2011-09-23
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Querydsl
Fix Released
Undecided
Unassigned

Bug Description

When using a MySQLQuery with forUpdate() and limit(), Querydsl is generating the SQL statement in the wrong order:
SELECT ... FROM ... WHERE ... FOR UPDATE LIMIT ?.
This is invalid. The expected order is:
SELECT ... FROM ... WHERE ... LIMIT ? FOR UPDATE.
For example, the following code triggers the bug:
MySQLQuery query = new MySQLQuery(...);
query.from(a).forUpdate().where(a.id.eq(id)).uniqueResult(a);

After examining the sources, I saw that SQLTemplates.serializeModifiers is invoked after invoking context.serialize(), which adds the for update clause on the Position.END.
The problem is that the modifiers (limit, in this case) are added after that.
Maybe there should be another Position.AFTER_MODIFIERS or something like that?

Fix commited.

Changed in querydsl:
status: New → Fix Committed

According to the MySQL query reference http://dev.mysql.com/doc/refman/5.1/en/select.html there are other commands which should be placed after the limit as well:
* PROCEDURE (not currently supported by MySQL query)
* [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
* [FOR UPDATE | LOCK IN SHARE MODE]

So, please, also make sure that the supported options (except PROCEDURE) are also serialized after the query modifiers.

Released in 2.2.3

Changed in querydsl:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers