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

[Luis Fernando Planella Gonzalez]

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);

[Luis Fernando Planella Gonzalez (luisfpg)]

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?

[Timo Westkämper (timo-westkamper)]

Fix commited.

[Luis Fernando Planella Gonzalez (luisfpg)]

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.

[Timo Westkämper (timo-westkamper)]

Released in 2.2.3