Order By In Update statement not supported by H2 Grammer

[GoogleCodeExporter]

Steps to 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"

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 "; SQL statement:
[info]           [42000-170]

Product version: h2-1.3.172.jar

Additional Information:
H2 UPDATE Grammer: http://www.h2database.com/html/grammar.html#update
MYSQL UPDATE Grammer: http://dev.mysql.com/doc/refman/5.0/en/update.html

Original issue reported on code.google.com by subhajit...@sidusa.com on 11 Jul 2014 at 8:38

[GoogleCodeExporter]

There is no plan to make H2 100% compatible with MySQL (and Oracle, and MS SQL 
Server, and PostgreSQL).

If you need this (fairly exotic, in my view) feature, you would need to have a 
really, really good explanation.

Original comment by thomas.t...@gmail.com on 11 Jul 2014 at 9:20

• Changed state: Invalid

[GoogleCodeExporter]

Consider the example:
"UPDATE table SET col1 = col1 + 1 WHERE col2 = 'some_value' ORDER BY col2 desc"
Now when there is a uniqueness constraint defined on (col1, col2) the above 
update will not work without the ORDER BY clause.
So how are these to be updated without the ordering.
If there are no plans for incorporating this functionality, then please provide 
a workaround query in H2 compatible with MYSQL for this purpose.

Original comment by subhajit...@sidusa.com on 11 Jul 2014 at 10:08

[GoogleCodeExporter]

OK I see. In H2, you simply don't need the "order by", because internally all 
rows are first removed and then the new rows are added. Test case:

drop table test;
create table test(col1 int primary key);
insert into test values(1);
insert into test values(2);
insert into test values(3);
update test set col1 = col1 + 1;
select * from test;

I assumed all other databases do the same. But I see now this doesn't work in 
other databases, including PostgreSQL (which is really a surprise to me), 
SQLite, MySQL. It does work in H2, HSQLDB, and Apache Derby.

Original comment by thomas.t...@gmail.com on 11 Jul 2014 at 11:37

[GoogleCodeExporter]

Yes correct it does not work in MYSQL.
So in this particular use case, I have data and table constraints created in 
MYSQL and directly migrated to H2. We use H2 for testing.
And I cannot just change queries in the source(connects to MYSQL) to be 
compatible with both MYSQL and H2(used for testing). 
So may you suggest/provide some work around if this feature is not be added 
into H2.
Ideally, I think it should be added into H2(will make it all the more useful).

Original comment by subhajit...@sidusa.com on 11 Jul 2014 at 12:17

[GoogleCodeExporter]

You could probably change the parser of H2 (the class "Parser", method 
"parseUpdate") to ignore "order by ...". A patch is welcome!

Original comment by thomas.t...@gmail.com on 11 Jul 2014 at 12:33

• Changed state: PatchesWelcome

[GoogleCodeExporter]

Like a DO NOTHING order by?

Original comment by subhajit...@sidusa.com on 11 Jul 2014 at 12:38

[GoogleCodeExporter]

Yes.

Original comment by thomas.t...@gmail.com on 11 Jul 2014 at 12:40

[GoogleCodeExporter]

BTW what is the process followed in H2 for code change pushes, release, etcc?

Original comment by subhajit...@sidusa.com on 11 Jul 2014 at 12:57

[GoogleCodeExporter]

See http://h2database.com/html/build.html 

Original comment by thomas.t...@gmail.com on 11 Jul 2014 at 3:27

[GoogleCodeExporter]

Hey I have made the changes and tested locally.
The SVN diff file is attached.
Please let me know if any thing else is needed.
Also let me know when this patch will be merged with the trunk.
And when I can change my project build settings to start using the 
h2-1.4.180.jar

Original comment by subhajit...@sidusa.com on 14 Jul 2014 at 10:13

Attachments:

• update_with_orderby_clause.diff

[GoogleCodeExporter]

Thanks a lot for the patch! It looks fine to me, I will try to merge it in the 
next days.
If everything works, it will be included in the next release (1.4.181), which 
will be in about 3 weeks.

Original comment by thomas.t...@gmail.com on 14 Jul 2014 at 10:34

• Changed state: Accepted

[GoogleCodeExporter]

Thanks for making H2 such easy to use/modify :). 
My pleasure to contribute to the code base.
Please let me know in this thread when the new release with the patch is 
available.
Thanks a lot.

Original comment by subhajit...@sidusa.com on 14 Jul 2014 at 10:42

[GoogleCodeExporter]

This should not be fixed in todays release (1.4.181). Thanks again for the 
patch!

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

• Changed state: Fixed

[GoogleCodeExporter]

Hey, So is this included in 1.4.181?

Original comment by subah...@gmail.com on 7 Aug 2014 at 5:56

[GoogleCodeExporter]

Yes. Sorry, a typo, I wanted to write:

This should *now* be fixed in todays release (1.4.181). Thanks again for the 
patch!

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

[GoogleCodeExporter]

Thanks. I will add in my project the new release version and check.

Original comment by subah...@gmail.com on 7 Aug 2014 at 6:21

[GoogleCodeExporter]

hey i tried the version h2-1.4.181.jar
seems the patch is not included.

java -jar h2-1.4.181.jar

then in the browser client http://127.0.1.1:8082/

i tried 

drop table if exists test;
create table if not exists test(col1 int primary key);
insert into test values(1);
insert into test values(2);
insert into test values(3);
select count(*) from test;

select max(col1) from test;

update test set col1 = col1 + 1
order by col1 asc;

select count(*) from test;

select max(col1) from test;

drop table if exists test;

I get error on the order by

update test set col1 = col1 + 1 
order by col1 asc;
Syntax error in SQL statement "UPDATE TEST SET COL1 = COL1 + 1 
ORDER[*] BY COL1 ASC "; SQL statement:
update test set col1 = col1 + 1 
order by col1 asc [42000-181] 42000/42000 (Help)

Original comment by subah...@gmail.com on 7 Aug 2014 at 7:49

[GoogleCodeExporter]

PFA my jar file generated.
For this the above queries work successfully.

Original comment by subah...@gmail.com on 7 Aug 2014 at 8:13

Attachments:

• h2-1.4.180.jar

[GoogleCodeExporter]

I'm really sorry about that! I thought I applied your patch, but in fact I 
didn't so far. I did that just now, in 
https://code.google.com/p/h2database/source/detail?r=5841 - it will be included 
in the next release.

Thanks a lot again!

Original comment by thomas.t...@gmail.com on 8 Aug 2014 at 9:12

• Changed state: Accepted

[GoogleCodeExporter]

May I know when is the next release scheduled?

Original comment by subah...@gmail.com on 8 Aug 2014 at 9:19

[GoogleCodeExporter]

Hi, 

You could try with the nightly build: 
http://www.h2database.com/automated/h2-latest.jar

See also http://h2database.com/html/build.html#automated

The next release is in about 3 or 4 weeks.

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

[GoogleCodeExporter]

Hi All,

I was just checking the changes here. First of all thanks for the fix. This was 
something i was also looking for for my application.
The fix is working fine with ORDER BY supporting UPDATE.
However it breaks some cases in my code where i have
ORDER BY ... ASC LIMIT 1;
Somehow now it does not recognize LIMIT 1.

I used the JAR provided here.
Can someone confirm and help me fix this. I'm relatively new here.

Thanks

Original comment by girish.g...@gmail.com on 11 Aug 2014 at 4:11

[GoogleCodeExporter]

While investigating and looking at the code i got the issue
I think the place where order by is added should be above LIMIT parsing :

CURRENT CODE:
if (readIf("LIMIT")) {
            Expression limit = readTerm().optimize(session);
            command.setLimit(limit);
        }
        if (readIf("ORDER")) {
            // for MySQL compatibility
            // (this syntax is supported, but ignored)
            read("BY");
            parseSimpleOrderList();
        }

MODIFIED CODE SHOULD BE:

if (readIf("ORDER")) {
            // for MySQL compatibility
            // (this syntax is supported, but ignored)
            read("BY");
            parseSimpleOrderList();
        }
        if (readIf("LIMIT")) {
            Expression limit = readTerm().optimize(session);
            command.setLimit(limit);
        }

Original comment by girish.g...@gmail.com on 11 Aug 2014 at 4:29

[GoogleCodeExporter]

I tested the changes locally and the attached JAR is working.
Hopefully someone or myself would make this fix for the next relese

Original comment by girish.g...@gmail.com on 11 Aug 2014 at 4:59

Attachments:

• h2-1.4.181.jar

[GoogleCodeExporter]

hey Thomas,
Will h2 support the grammar:
UPDATE ....
ORDER BY ... ASC LIMIT 1;
?
OR just plain
UPDATE ....
ORDER BY ...(ASC/DESC);
?

Original comment by subah...@gmail.com on 11 Aug 2014 at 5:19

[GoogleCodeExporter]

Hi,
(I know its not for me. But the recent patch has some bugs)

Like i mentioned the above attached jar would run for both.
Currently there seems to be a bug to run the order by followed by LIMIT.
But if the code snippet attached is changed as per my suggestions then it 
would work.

Thanks,
Girish.

Original comment by girish.g...@gmail.com on 11 Aug 2014 at 8:53

[GoogleCodeExporter]

Hi All,

First time releasing a patch using SVN (used to CVS).
Let me know if the attached file is the right patch.
It is an enhancement over the previous patch ( to take into account LIMIT 
related changes)

Regards,
Girish.

Original comment by girish.g...@gmail.com on 11 Aug 2014 at 10:15

Attachments:

• update_with_orderby_clause.diff

[GoogleCodeExporter]

Issue 576 has been merged into this issue.

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

[GoogleCodeExporter]

Issue 576 has been merged into this issue.

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

[GoogleCodeExporter]

Hey Thomas,
Just wanted to confirm if this patch is the latest H2 release.
Also please let me know the release version.

Thanks and Regards,
Subhajit Datta

Original comment by subah...@gmail.com on 25 Sep 2014 at 7:49

[GoogleCodeExporter]

Hi Thomas,

Apologies if i'm bugging you.
Can you please confirm that the patch provided was accepted and merged ?

Kind Regards,
Girish

Original comment by girish.g...@gmail.com on 29 Sep 2014 at 8:55

[GoogleCodeExporter]

It should now be fixed with version 1.4.182

Original comment by thomas.t...@gmail.com on 17 Oct 2014 at 12:26

• Changed state: Fixed