Search code examples
javamysqlhsqldb

HSQLDBs 'ON DUPLICATE KEY UPDATE'-feature does not behave as in MYSQL?

I am using MYSQLs ON DUPLICATE KEY UPDATE-feature, and it works as I expect when running on a MYSQL database. But when I try to write tests running against a in-memory HSQLDB, I experience different behaviour.

Given the following table:

CREATE TABLE foo (id INT PRIMARY KEY NOT NULL, counter INT);

With the following insert statement:

INSERT INTO foo(id, counter) VALUES (1, 1) ON DUPLICATE KEY UPDATE counter=counter+1
  • After the first run, counter is 1.
  • After the second run, counter is 2.
  • After the third run, counter is still 2. Here I expected the counter to be 3. (It is 3 if I run the same queries against MYSQL).

Is this a bug, or have I misunderstood how ON DUPLICATE KEY UPDATE should work?

For a running example, see the following github-repository: https://github.com/mortenberg80/hsqldbtest

Solution

  • The initial HSQLDB implementation of ON DUPLICATE UPDATE used the VALUES list as the source of the update in all cases where col_name was encounterd. It has been enhanced for version 2.5.1 to use the existing table row values as the source for col_name, and use the VALUES list as the source for VALUES(col_name). This corresponds with MySQL usage.