Search code examples
javasqlfunctionhsqldb

HSQL Java Language Routines cause "General Error" (S1000) when called

I am trying to migrate an old application from HSQLDB 1.8.0.10 to the latest version 2.5.1.

Everything has been going fine, but the old application added two ALIASs for static Java methods:

statement.execute("CREATE ALIAS BigBitAnd FOR \"org.somedomain.ClassName.bigBitAnd\"");
statement.execute("CREATE ALIAS BigMod FOR \"org.somedomain.ClassName.bigMod\"");

with

public static long bigBitAnd(long a, long b) {
    return a & b;
}

public static long bigMod(long a, long b) {
    return a % b;
}

I have tried translating this to function definitions as follows:

statement.execute("CREATE FUNCTION BIGBITAND(a BIGINT, b BIGINT) RETURNS BIGINT LANGUAGE JAVA DETERMINISTIC NO SQL EXTERNAL NAME 'CLASSPATH:org.somedomain.ClassName.bigBitAnd'");
statement.execute("CREATE FUNCTION BIGBITAND(a BIGINT, b BIGINT) RETURNS BIGINT LANGUAGE JAVA DETERMINISTIC NO SQL EXTERNAL NAME 'CLASSPATH:org.somedomain.ClassName.bigMod'");

The functions are created successfully, but when they are applied in any statement via PreparedStatement.executeQuery(), the query fails with a "General Error" (S1000). A google search tells me this may be happening because of a NumberFormatException somewhere.

For testing purposes, I also tried hsqldb 2.3.6. It also failed with a "General Error" due to a NullPointerException.

Is there something wrong with my function declarations or are functions called differently than hsql 1.8 aliases?

Update: I tried replacing all occurrences of BIGBITAND with BIGAND calls, and the error no longer occurs. However, this leads to other issues because BIGAND does not apply to BIGINT, as far as I can tell. It appears that the BIGMOD custom function does not lead to a General Error.

Update 2: With HSQLDB version 2.4.0, I get a more detailed stack trace:

Caused by: java.lang.NullPointerException
at org.hsqldb.RoutineSchema.getSpecificRoutine(Unknown Source)
at org.hsqldb.FunctionSQLInvoked.resolveTypes(Unknown Source)
at org.hsqldb.ExpressionLogical.resolveTypes(Unknown Source)
at org.hsqldb.ExpressionLogical.resolveTypes(Unknown Source)
at org.hsqldb.ExpressionLogical.resolveTypes(Unknown Source)
at org.hsqldb.ExpressionLogical.resolveTypes(Unknown Source)
at org.hsqldb.QuerySpecification.resolveExpressionTypes(Unknown Source)
at org.hsqldb.QuerySpecification.resolveTypesPartOne(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)
Solution

  • Sometimes the type of CASEWHEN expressions is not determined correctly or the type is not exactly right. You can always cast the function arguments, including the variable placeholder ?, into the type you need. For example:

    BIGBITAND(CAST( CASEWHEN(BIGMOD(p.val,2)=0,p.val+1,p.val-1) AS BIGINT), CAST(? AS BIGINT) ) != 0

    P.S. The non-specific "General Error" is caused when the engine reports the actual error, which is this: "routine signature not found for: PUBLIC.BIGBITAND(DECIMAL,null) in statement ..". The data type of the expression p.val+1 is DECIMAL due to automatic type widening. This causes the engine to look for a routine signature that does not exist.