Support ON DUPLICATE KEY UPDATE syntax in MySQL Compatibility mode

[GoogleCodeExporter]

Currently MySQL compatibility mode doesn't support the ON DUPLICATE KEY UPDATE 
clause. Supporting this would make H2 VERY usable as an in-memory substitute 
for MySQL in testing in cases where the code uses MySQL-specific syntax without 
a mediating ORM layer.

Product version is 1.3.162

There's no workaround I know of.

Thanks

Original issue reported on code.google.com by shai.yal...@gmail.com on 26 Dec 2011 at 5:33

[GoogleCodeExporter]

I agree with this. The current MERGE statement is less powerful than MySQL's ON 
DUPLICATE KEY UPDATE clause, and even less powerful than the SQL:2003 MERGE 
statement supported by DB2, Firebird (I think?), HSQLDB, Oracle, SQL Server, 
Sybase SQL Anywhere.

Nevertheless, these statements / clauses are a very nice means of merging more 
complex data...

Original comment by lukas.eder@gmail.com on 6 Jan 2012 at 11:44

[GoogleCodeExporter]

Is this fixed by now?

Original comment by jtgrabow...@gmail.com on 29 Jul 2013 at 8:37

[GoogleCodeExporter]

No, setting to "patches welcome"

Original comment by thomas.t...@gmail.com on 29 Jul 2013 at 9:07

• Changed state: PatchesWelcome

[GoogleCodeExporter]

We used h2 in tests and needed it to support this so here is my patch for it. 
Let me know if you end up using it.

I followed pretty much all the step from the build page, tell me how to change 
it, if I forgot something.

Finally I don't know what is the threshold for a significant patch so here is 
the disclaimer to include.

I wrote the code, it's mine, and I'm contributing it to H2 for distribution 
multiple-licensed under the H2 License, version 1.0, and under the Eclipse 
Public License, version 1.0 (http://h2database.com/html/license.html).

Jean-François Noël

Original comment by jfn...@gmail.com on 15 Oct 2013 at 4:03

[GoogleCodeExporter]

I tested your patch but it does not support the form:

    INSERT INTO table (column) VALUES (?) ON DUPLICATE KEY UPDATE column = VALUES(column)

It fails with:

    Ambiguous column name "COLUMN"

Could you add support for this?

Original comment by g...@maginatics.com on 15 Oct 2013 at 5:03

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

I'll check, in the meantime does it fail also with `column`. Because MySQL 
fails with the query as you entered it. In fact MySQL doesn't allow a column 
named column without the `. Anyway I'll check what I can do.

Original comment by jfn...@gmail.com on 15 Oct 2013 at 5:51

[GoogleCodeExporter]

Sorry, I simplified the actual error message which included multiple column 
names.

Original comment by g...@maginatics.com on 15 Oct 2013 at 4:51

[GoogleCodeExporter]

Sorry, I tried to reproduce your ambiguous column problem, but still no luck. 
Can you provide me more detail on the error, so I can correct the patch. The 
create table and 1-2 inserts should do perfectly, thanks.

Original comment by jfn...@gmail.com on 18 Oct 2013 at 1:57

[GoogleCodeExporter]

Jean-Francois, I reduced the test case as follows:

    @Test
    public void testOnDuplicateKeyInsert() throws Exception {
        try (Connection conn = dataSource.getConnection()) {
            try (PreparedStatement stmt = conn.prepareStatement(
                    "CREATE TABLE table (" +
                    " x BIGINT NOT NULL" +
                    ", y BIGINT NOT NULL" +
                    ", z BIGINT NOT NULL" +
                    ", PRIMARY KEY (x))")) {
                stmt.executeUpdate();
            }
            for (int i = 0; i <= 2; ++i) {
                try (PreparedStatement stmt = conn.prepareStatement(
                        "INSERT INTO table" +
                        " (x, y, z) VALUES (?, ?, ?)" +
                        " ON DUPLICATE KEY UPDATE" +
                        " y = VALUES(y), z = VALUES(z)")) {
                    stmt.setLong(1, 0);
                    stmt.setLong(2, 0);
                    stmt.setLong(3, i);
                    logger.trace("{}", stmt);
                    stmt.executeUpdate();
                }
            }
            conn.commit();
        }
    }

I see an error on the third iteration, not the second as expected:

T 10-18 16:54:40.407 pool-1-thread-1 c.m.r.m.dbfs.DatabaseTest:640 ::] prep17: 
INSERT INTO table (x, y, z) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE y = 
VALUES(y), z = VALUES(z) {1: 0, 2: 0, 3: 0}
T 10-18 16:54:40.408 pool-1-thread-1 c.m.r.m.dbfs.DatabaseTest:640 ::] prep18: 
INSERT INTO table (x, y, z) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE y = 
VALUES(y), z = VALUES(z) {1: 0, 2: 0, 3: 1}
T 10-18 16:54:40.412 pool-1-thread-1 c.m.r.m.dbfs.DatabaseTest:640 ::] prep19: 
INSERT INTO table (x, y, z) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE y = 
VALUES(y), z = VALUES(z) {1: 0, 2: 0, 3: 2}

testOnDuplicateKeyInsert(com.maginatics.raptor.metadata.dbfs.DatabaseTest)  
Time elapsed: 0.689 sec  <<< ERROR!
org.h2.jdbc.JdbcSQLException: Ambiguous column name "Z"; SQL statement:
INSERT INTO table (x, y, z) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE y = 
VALUES(y), z = VALUES(z) [90059-173]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:332)
        at org.h2.message.DbException.get(DbException.java:172)
        at org.h2.message.DbException.get(DbException.java:149)
        at org.h2.expression.ExpressionColumn.mapColumn(ExpressionColumn.java:121)
        at org.h2.expression.ExpressionColumn.mapColumns(ExpressionColumn.java:91)
        at org.h2.expression.Function.mapColumns(Function.java:1711)
        at org.h2.command.dml.Update.prepare(Update.java:186)
        at org.h2.command.dml.Insert.handleOnDuplicate(Insert.java:369)
        at org.h2.command.dml.Insert.insertRows(Insert.java:151)
        at org.h2.command.dml.Insert.update(Insert.java:107)
        at org.h2.command.CommandContainer.update(CommandContainer.java:79)
        at org.h2.command.Command.executeUpdate(Command.java:253)
        at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:154)
        at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:140)
        at com.maginatics.raptor.metadata.dbfs.DatabaseTest.testOnDuplicateKeyInsert(DatabaseTest.java:641)

Original comment by g...@maginatics.com on 18 Oct 2013 at 11:56

[GoogleCodeExporter]

To clarify, I expect to see no exceptions, but if I did see one, I would expect 
to see it on the second insert and not the third.

Original comment by g...@maginatics.com on 19 Oct 2013 at 12:04

[GoogleCodeExporter]

Hi can you try this patch instead, I updated the tests to reflect your case and 
fixed the problem. The Insert statement can be cached and the column resolver 
was set incorrectly for the VALUES function parameter. Anyway now I refresh the 
function args even if the statement is cached. It should work now. I also did 
some refactoring, can't help it...

Original comment by jfn...@gmail.com on 21 Oct 2013 at 12:40

[GoogleCodeExporter]

Sorry, this still does not work for me:

    @Test
    public void testOnDuplicateKeyInsert2() throws Exception {
        try (Connection conn = dataSource.getConnection()) {
            try (PreparedStatement stmt = conn.prepareStatement(
                    "CREATE TABLE table (" +
                    " x BIGINT NOT NULL" +
                    ", y BIGINT NOT NULL" +
                    ", PRIMARY KEY (x))")) {
                stmt.executeUpdate();
            }
            for (int i = 0; i < 2; ++i) {
                try (PreparedStatement stmt = conn.prepareStatement(
                        "INSERT INTO table" +
                        " (x, y) VALUES (?, ?)" +
                        " ON DUPLICATE KEY UPDATE" +
                        " y = y + VALUES(y)")) {
                    stmt.setLong(1, 0);
                    stmt.setLong(2, 1);
                    logger.trace("{}", stmt);
                    stmt.executeUpdate();
                }
            }
            conn.commit();
        }
    }

testOnDuplicateKeyInsert2(com.maginatics.raptor.metadata.dbfs.DatabaseTest)  
Time elapsed: 0.753 sec  <<< ERROR!
org.h2.jdbc.JdbcSQLException: NULL not allowed for column "Y"; SQL statement:
INSERT INTO table (x, y) VALUES (?, ?) ON DUPLICATE KEY UPDATE y = y + 
VALUES(y) [23502-174]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:332)
        at org.h2.message.DbException.get(DbException.java:172)
        at org.h2.message.DbException.get(DbException.java:149)
        at org.h2.table.Column.validateConvertUpdateSequence(Column.java:295)
        at org.h2.table.Table.validateConvertUpdateSequence(Table.java:711)
        at org.h2.command.dml.Update.update(Update.java:124)
        at org.h2.command.dml.Insert.handleOnDuplicate(Insert.java:329)
        at org.h2.command.dml.Insert.insertRows(Insert.java:154)
        at org.h2.command.dml.Insert.update(Insert.java:110)
        at org.h2.command.CommandContainer.update(CommandContainer.java:79)
        at org.h2.command.Command.executeUpdate(Command.java:253)
        at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:154)
        at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:140)
        at com.maginatics.raptor.metadata.dbfs.DatabaseTest.testOnDuplicateKeyInsert2(DatabaseTest.java:666)

Original comment by g...@maginatics.com on 21 Oct 2013 at 6:11

[GoogleCodeExporter]

Thanks so much for the tests, here is a v3 of the patch. I also updated my test 
class to test more complex expression.

Original comment by jfn...@gmail.com on 25 Oct 2013 at 1:29

Attachments:

• onDuplicateKeyUpdate3.patch

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

I don't really see any line that were unnecessary changes. Is there any one in 
the patch file that you think I should remove? Like the comment you mention, 
I'm guessing it is just an example of what you mean, because it is not in the 
patch file.

Original comment by jfn...@gmail.com on 25 Oct 2013 at 3:05

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

Thanks a lot for the patch!

We need to review the patch now, and then integrate it.

Original comment by thomas.t...@gmail.com on 9 Nov 2013 at 7:28

• Changed state: Started

[GoogleCodeExporter]

Patch committed in revision 5300.

Original comment by noelgrandin on 18 Nov 2013 at 1:35

• Changed state: Fixed

[GoogleCodeExporter]

Great :) 

Original comment by cemalettin.koc@gmail.com on 18 Nov 2013 at 1:35

[GoogleCodeExporter]

It is one more problem related to support of ON DUPLICATE query - batch update. 
Exception is the same:

Caused by: org.h2.jdbc.JdbcBatchUpdateException: Ambiguous column name "Z"; SQL 
statement:
INSERT INTO table (x, y, z) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE y = 
VALUES(y), z = VALUES(z)
[90059-174]
    at org.h2.jdbc.JdbcPreparedStatement.executeBatch(JdbcPreparedStatement.java:1167)

I will investigate it a bit more detail if I found some spare time for that. 
For now I switched to for loop and execute update in conventional way.

Original comment by marek.sm...@gmail.com on 3 Dec 2013 at 10:48

[GoogleCodeExporter]

Re-opening

Original comment by thomas.t...@gmail.com on 12 Dec 2013 at 4:26

• Changed state: Started

[GoogleCodeExporter]

At my work we found the same issue, but as part of a multi-value insert.

In ExpressionColumn, the the Column Resolver (a TableFilter instance) is used 
to determine the value for the column. The value of the resolver isn't rebound 
if it has been set already.

In the case of a multi-valued INSERT with ON DUPLICATE KEY UPDATE, this leaves 
a resolver with an inaccurate view of the table. This is because the bound 
resolver has a copy of the row before the last UPDATE was executed.

I've attached a patch (including tests) that fixes this problem, by always 
using the new resolver for the ExpressionColumn if it is a TableFilter. I'm not 
sure of the implications of this change beyond this type of call though.

Original comment by angus.d....@gmail.com on 4 Feb 2014 at 7:25

Attachments:

• on-duplicate-key-update-multi-value.patch

[GoogleCodeExporter]

Hm, the patch breaks some test cases, for example the statement:

  select * from test, test where id=id;

is supposed to fail because "id" is ambiguous. With the patch, the statement 
doesn't fail but picks the second table.

Original comment by thomas.t...@gmail.com on 22 Feb 2014 at 2:02

[GoogleCodeExporter]

Should now be fixed in the trunk (I found a different solution, similar to how 
the MERGE statement is processed).

Original comment by thomas.t...@gmail.com on 22 Feb 2014 at 7:17

• Changed state: Fixed

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

 You need to append ";mode=mysql" to the database URL.

Original comment by thomas.t...@gmail.com on 10 Apr 2015 at 5:47