INSERT .. ON DUPLICATE KEY UPDATE may not work depending on table indexes order and columns participating in statement

[Filipp Zhinkin]

Hello,

I've tried to use H2 1.4.192 for unit tests using MYSQL compatibility mode and found that in some cases INSERT ... ON DUPLICATE KEY UPDATE statement is not updating rows.

If there are primary key and unique constrains and both constrained columns are participating in insert statement then Insert::searchForUpdateIndex will return the first index that contains columns from statement, but that index may not be the index that caused constraint violation and as a result old row won't be updated.

Suppose we have following table:

CREATE TABLE test_table (

  id INT,

  dup INT,

  counter INT,

  UNIQUE(dup),

  PRIMARY KEY(id)

);

I'm expecting to observe counter == 2 after following statements being executed:

INSERT INTO test_table (id, dup, counter) VALUES (1, 1, 1);

INSERT INTO test_table (id, dup, counter) VALUES (2, 2, 2) ON DUPLICATE KEY UPDATE counter = counter + VALUES(counter);

But counter's value for row with id == 1 may still be 1.

I've added tests on that issue, but failed to provide any fix:

https://github.com/nerh/h2database/commit/91efa01d8f13c761322ce79d0ecb104122abe8e0

A naive approach to use an index from exception's getSource() won't work because duplicate could be detected at least in MVPrimaryIndex which will contain all columns. :(

Best regards,

Filipp Zhinkin.

[Noel Grandin]

On 2016/10/19 2:24 PM, Filipp Zhinkin wrote:
>
> Suppose we have following table:
>
> CREATE TABLE test_table (
> id INT,
> dup INT,
> counter INT,
> UNIQUE(dup),
> PRIMARY KEY(id)
> );
>
> I'm expecting to observe counter == 2 after following statements being executed:
>
> INSERT INTO test_table (id, dup, counter) VALUES (1, 1, 1);
> INSERT INTO test_table (id, dup, counter) VALUES (2, 2, 2) ON DUPLICATE KEY UPDATE counter = counter + VALUES(counter);
>
> But counter's value for row with id == 1 may still be 1.
>

Are you sure you meant to write the test like this?
Because you have ID as the primary key, which means after the two INSERTS, the table should look like:

select * from test_table;
ID DUP COUNTER
1 1 1
2 2 2

Because the second insert is using a different ID, which means that the "ON DUPLICATE KEY" logic does not trigger.

And H2 appears to correctly handle this.

[Filipp Zhinkin]

четверг, 20 октября 2016 г., 17:30:39 UTC+3 пользователь Noel Grandin написал:

Sorry, there is a typo in second insert. :(

Values should be equal to (2, 1, 1) in order to force unique constrain violation on DUP column.

[Noel Grandin]

If I execute your new test, my result is

select * from test_table;
ID DUP COUNTER

1 1 2


???

[Filipp Zhinkin]

Just reordered primary key and unique constraint declarations and it stopped working:

DROP TABLE test_table IF EXISTS;

CREATE TABLE test_table (id INT, dup INT, counter INT, PRIMARY KEY(id), UNIQUE(dup));

INSERT INTO test_table (id, dup, counter) VALUES (1, 1, 1);

INSERT INTO test_table (id, dup, counter) VALUES (2, 1, 1) ON DUPLICATE KEY UPDATE counter = counter + VALUES(counter);

SELECT * FROM test_table;

ID          DUP          COUNTER 
1        1        1 

вторник, 25 октября 2016 г., 10:04:09 UTC+3 пользователь Noel Grandin написал:

[Noel Grandin]

thanks for the good test cases, this has been fixed on master

[Filipp Zhinkin]

great, thank you!

среда, 26 октября 2016 г., 17:23:37 UTC+3 пользователь Noel Grandin написал: