Atomic INSERT/SELECT in HSQLDB

Question

Hello,

I have the following hsqldb table, in which I map UUIDs to auto incremented IDs:

SHORT_ID (BIG INT, PK, auto incremented) | UUID (VARCHAR, unique)

Create command:

CREATE TABLE mytable (SHORT_ID BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, UUID VARCHAR(36) UNIQUE)

In order to add new pairs concurrently, I want to use the atomic MERGE INTO statement. So my (prepared) statement looks like this:

MERGE INTO mytable USING (VALUES(CAST(? AS VARCHAR(36)))) AS v(x) ON mytable.UUID = v.x WHEN NOT MATCHED THEN INSERT VALUES v.x

When I execute the statement (setting the placeholder correctly), I always get a

Caused by: org.hsqldb.HsqlException: row column count mismatch

Could you please give me a hint, what is going wrong here?

Thanks in advance.

Answer 1

Epilogue

I reported this behavior as a bug, and it is today (2010-05-25) fixed in the hsqldb SVN repository, per hsqldb-Bugs-2989597. (Thanks, hsqldb!)

Updated Answer

Neat one! Here's what I got to work under HSQLDB 2.0.0rc9, which supports the syntax and the error message you posted:

MERGE INTO mytable
   USING (SELECT 'a uuid' FROM dual) AS v(x) -- my own "DUAL" table
   ON (mytable.UUID = v.x)
   WHEN NOT MATCHED THEN INSERT
     VALUES (NULL, x)                        -- explicit NULL for "SHORT_ID" :(

Note, I could not convince 2.0.0rc9 to accept ... THEN INSERT (UUID) VALUES (x), which is IIUC a perfectly acceptable and clearer specification than the above. (My SQL knowledge is hardly compendious, but this looks like a bug to me.)

Original Answer

You appear to be INSERTing a single value (a 1-tuple) into a table with more than one column. Perhaps you can modify the end of your statement to read:

... WHEN NOT MATCHED INSERT ("UUID") VALUES (v.x)