HSQLDB ANY array function not working

Question

I have a table like this

CREATE TABLE inlist_test_table (
    id  NUMERIC(5) NOT NULL PRIMARY KEY,
    val VARCHAR(50) NOT NULL
);

I'm executing the following query

SELECT val
FROM inlist_test_table
WHERE id = ANY(?)
ORDER BY id

Using this code

try (var connection = this.dataSource.getConnection();
     var preparedStatement = connection.prepareStatement(this.getQuery())) {
  var array = connection.createArrayOf("smallint", new Object[] {3, 5});
  try {
    preparedStatement.setArray(1, array);

    List<String> values = new ArrayList<>(2);
    try (var resultSet = preparedStatement.executeQuery()) {
      while (resultSet.next()) {
        values.add(resultSet.getString(1));
      }
    }
    assertEquals(Arrays.asList("Value_00003", "Value_00005"), values);
  } finally {
    array.free();
  }
}

I expect rows with id 3 and 5 to be returned. Instead I'm getting the following exception:

java.sql.SQLSyntaxErrorException: invalid ORDER BY expression in statement [SELECT val FROM inlist_test_table WHERE id = ANY(?) ORDER BY id]
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
    at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
    at com.github.marschall.jdbcinlists.AbstractInListTest.plainJdbc(AbstractInListTest.java:43)
    ...
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:206)
Caused by: org.hsqldb.HsqlException: invalid ORDER BY expression
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.QuerySpecification.resolveColumnReferencesInOrderBy(Unknown Source)
    at org.hsqldb.QuerySpecification.resolveColumnReferences(Unknown Source)
    at org.hsqldb.QuerySpecification.resolveReferences(Unknown Source)
    at org.hsqldb.QueryExpression.resolve(Unknown Source)
    at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)
    at org.hsqldb.ParserCommand.compilePart(Unknown Source)
    at org.hsqldb.ParserCommand.compileStatement(Unknown Source)
    at org.hsqldb.Session.compileStatement(Unknown Source)
    at org.hsqldb.StatementManager.compile(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)
... 59 more

I am using HSQLDB 2.4.1. The same code is working for Postgres and H2.

Edit 1

SELECT val
FROM inlist_test_table
WHERE id IN ( UNNEST(?) )
ORDER BY id

Does not work as only the row with id 3 (the first element in the array) is matched and row with id 5 (the second element in the array) is not matched.

Edit 2

Changing the column type from NUMERIC(5) to int makes the UNNEST code work even if the array element type is smallint.

Answer 1

With HSQLDB you need to use

... WHERE id IN ( UNNEST(?) ) ...

As of version 2.4.1 there appears to be an issue when the column is NUMERIC(5) and the array is smallint. That will likely be fixed in a future release of HSQLDB.