hsqldb任何数组函数都不工作

我有一张这样的桌子

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

我正在执行以下查询

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

使用此代码

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();
  }
}

我希望返回ID为3和5的行。相反,我得到了以下异常:

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

我正在使用hsqldb 2.4.1。同样的代码也适用于postgres和h2。

编辑1

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

不起作用,因为只有ID为3的行(数组中的第一个元素)匹配,ID为5的行(数组中的第二个元素)不匹配。

编辑2

更改列类型 NUMERIC(5) 到 int 使 UNNEST 即使数组元素类型是 smallint .