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. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
