After latest changes H2 not supporting UPDATE ... ORDER BY ... LIMIT

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Use H2 in MYSQL compatibility Mode "MODE=MYSQL;MVCC=TRUE" 
2. Use a update query eg: "UPDATE table SET col1 = col1 + 1 WHERE col2 = 
'some_value' ORDER BY col2 desc LIMIT 1"

Expected output: Query execution and updation
Actual output: 

[info]           org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement 
"UPDATE table SET col1 = col1 + 1
[info]           WHERE  col2 = 'some_value'
[info]           ORDER BY col2 desc LIMIT[*] 1"; SQL statement:
[info]           [42000-170]

Product version: h2-1.3.181.jar

Original issue reported on code.google.com by girish.g...@gmail.com on 12 Aug 2014 at 9:46

• Merged into: #572

[GoogleCodeExporter]

Hi ,

New to this forum. This is in continuation with ISSUE 572.
https://code.google.com/p/h2database/issues/detail?id=572

The solution to that had created a problem which is resolved here.

Support of
UPDATE ... ORDER BY ... LIMIT  would be enabled just by making some changes
to the existing file.

Original comment by girish.g...@gmail.com on 12 Aug 2014 at 9:51

Attachments:

• update_with_orderby_limit_clause.diff

[GoogleCodeExporter]

Hi Thomas,

Sorry for a typo this bug can be reproduced using h2-1.3.180.jar.
Also have attached the patch file in my reply.
Kindly let me know if the patch can be accepted or you need anything else.

Original comment by girish.g...@gmail.com on 13 Aug 2014 at 6:53

[GoogleCodeExporter]

There is already an issue for this problem: Issue 572.

It should be fixed, you can try with the latest nightly build: 
http://www.h2database.com/automated/h2-latest.jar - see also 
http://h2database.com/html/build.html#automated

If you get the error code 42000-170, then that means you are actually using 
version 1.3.170, not 1.4.181.

Original comment by thomas.t...@gmail.com on 13 Aug 2014 at 7:00

[GoogleCodeExporter]

Original comment by thomas.t...@gmail.com on 13 Aug 2014 at 7:01

• Changed state: Duplicate

[GoogleCodeExporter]

Hi Thomas,

Thanks for your reply.
I just wanted to make a point that fix for ISSUE 572
introduces another problem which i have mentioned
It fixes UPDATE ... ORDER BY.
but fails for UPDATE ... ORDER BY ... LIMIT.

The patch provided passes for both.

About  [42000-170] i had copy pasted it as i have merged my changes and did not 
want to revert back. but the problem of UPDATE ... ORDER BY ... LIMIT does not 
work for 
1.4.180

I have attached same patch to ISSUE 572. Kindly accept if you feel appropriate.
I thought there is a need of different patch. So i created one more issue.

Thanks,
Girish.

Original comment by girish.g...@gmail.com on 13 Aug 2014 at 7:08

[GoogleCodeExporter]

As I wrote:

It should be fixed, you can try with the latest nightly build: 
http://www.h2database.com/automated/h2-latest.jar - see also 
http://h2database.com/html/build.html#automated

Original comment by thomas.t...@gmail.com on 13 Aug 2014 at 7:13