Why is my merge (upsert) failing with type mismatches in a HSQLDB database when using VARBINARY fields?

Database engine: HSQLDB (because, well, it allows an embedded application DB in Java)

Language: Java 8 (actually, Groovy 3.0.2, but it's basically JRE)

Schema as follows:

  CREATE TABLE A (
    START VARBINARY(16) NOT NULL,
    END VARBINARY(16) NOT NULL,
    ID BIGINT NOT NULL,
    COUNTRY VARCHAR(8),
    DESCRIPTION VARCHAR(256),
    PRIMARY KEY (START, END)
  );

START and END are IPv4 or IPv6 addresses encoded as 4 or 16 element byte arrays.

I get an error "org.hsqldb.HsqlException: incompatible data type in conversion" in this code (simplified):

stmt.addBatch(a, b, c, d, e)

When using this "upsert" to insert data which doesn't already exist in the table:

  MERGE INTO A
  USING (VALUES ?,?,?,?,?) I (START, END, ID, COUNTRY, DESCRIPTION)
  ON (A.START = I.START AND A.END = I.END)
  WHEN MATCHED THEN UPDATE
    SET A.ID = I.ID, A.COUNTRY = I.COUNTRY, A.DESCRIPTION = I.DESCRIPTION 
  WHEN NOT MATCHED THEN INSERT (START, END, ID, COUNTRY, DESCRIPTION)
    VALUES (I.START, I.END, I.ID, I.COUNTRY, I.DESCRIPTION)

Here a and b are byte[], c is a long and d and e are Strings and stmt is a BatchingPreparedStatementWrapper.

Debugging implies that the stmt variable thinks that parameters 1 and 2 of the statement (a and b) are of type VARCHAR... they should be VARBINARY(16).

In fact it seems to think all the parameters are VARCHARs. This seems odd.

I don't think there's anything obviously wrong with my SQL, as it used to work when I had the START and END fields defined as VARCHARs. It just doesn't now they're VARBINARYs.

(The reason I want VARBINARIES is to allow numeric style comparisons between VARBINARY values.)

My Googling finds nothing relevant, but perhaps someone here can offer a clue?

Solution

Does it work it you explicitly cast() the input parameter in the query?

MERGE INTO A
USING 
    (VALUES CAST(? AS VARBINARY(16)), CAST(? AS VARBINARY(16)), ?, ?, ?) 
    I (START, END, ID, COUNTRY, DESCRIPTION)
ON (A.START = I.START AND A.END = I.END)
WHEN MATCHED 
    THEN UPDATE
    SET A.ID = I.ID, A.COUNTRY = I.COUNTRY, A.DESCRIPTION = I.DESCRIPTION 
WHEN NOT MATCHED 
    THEN INSERT (START, END, ID, COUNTRY, DESCRIPTION)
    VALUES (I.START, I.END, I.ID, I.COUNTRY, I.DESCRIPTION)