'Unknown data type' error performing date arithmetic with parameter

[LukeWoodward]

(From https://stackoverflow.com/questions/51892300)

The following class

import java.sql.*;

public class H2Test {
    public static void main(String[] args) throws Exception {
        Connection conn = DriverManager.getConnection("jdbc:h2:./test.db;MODE=ORACLE");
        PreparedStatement ps = conn.prepareStatement("SELECT SYSDATE - ?");
    }
}

throws the following exception when run:

Exception in thread "main" org.h2.jdbc.JdbcSQLException: Unknown data type: "?"; SQL statement:
SELECT SYSDATE - ? [50004-197]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)
        at org.h2.message.DbException.get(DbException.java:179)
        at org.h2.message.DbException.get(DbException.java:155)
        at org.h2.value.DataType.getDataType(DataType.java:827)
        at org.h2.expression.Operation.optimize(Operation.java:312)
        at org.h2.command.dml.Select.prepare(Select.java:858)
        at org.h2.command.Parser.prepareCommand(Parser.java:283)
        at org.h2.engine.Session.prepareLocal(Session.java:611)
        at org.h2.engine.Session.prepareCommand(Session.java:549)
        at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1247)
        at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:76)
        at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:304)
        at H2Test.main(H2Test.java:6)

There is no exception if you replace the parameter ? with a hard-coded integer.

Incidentally (and this may be a separate issue already reported elsewhere), changing the compatibility mode in the connection string to an unrecognised value (e.g. UNKNOWN) changes the exception to the following:

Exception in thread "main" org.h2.jdbc.JdbcSQLException: General error: "java.lang.NullPointerException" [50000-197]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)
        at org.h2.message.DbException.get(DbException.java:168)
        at org.h2.message.DbException.convert(DbException.java:307)
        at org.h2.engine.Database.openDatabase(Database.java:319)
        at org.h2.engine.Database.<init>(Database.java:280)
        at org.h2.engine.Engine.openSession(Engine.java:66)
        at org.h2.engine.Engine.openSession(Engine.java:179)
        at org.h2.engine.Engine.createSessionAndValidate(Engine.java:157)
        at org.h2.engine.Engine.createSession(Engine.java:140)
        at org.h2.engine.Engine.createSession(Engine.java:28)
        at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:351)
        at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:124)
        at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:103)
        at org.h2.Driver.connect(Driver.java:69)
        at java.sql/java.sql.DriverManager.getConnection(Unknown Source)
        at java.sql/java.sql.DriverManager.getConnection(Unknown Source)
        at H2Test.main(H2Test.java:5)
Caused by: java.lang.NullPointerException
        at org.h2.command.Parser.read(Parser.java:3731)
        at org.h2.command.Parser.readIf(Parser.java:3525)
        at org.h2.command.Parser.parseCreateUser(Parser.java:5077)
        at org.h2.command.Parser.parseCreate(Parser.java:4564)
        at org.h2.command.Parser.parsePrepared(Parser.java:380)
        at org.h2.command.Parser.parse(Parser.java:335)
        at org.h2.command.Parser.parse(Parser.java:307)
        at org.h2.command.Parser.prepare(Parser.java:262)
        at org.h2.engine.Session.prepare(Session.java:576)
        at org.h2.engine.Session.prepare(Session.java:560)
        at org.h2.engine.MetaRecord.execute(MetaRecord.java:56)
        at org.h2.engine.Database.open(Database.java:775)
        at org.h2.engine.Database.openDatabase(Database.java:286)
        ... 13 more

[katzyn]

1. H2 tries to compile prepared statement immediately. Subtraction operation between different data types works in very different ways and it needs to know data types of its arguments. You can add an explicit cast as workaround.

SELECT SYSDATE - CAST(? AS INTEGER)

(Actually Oracle does not support selects without FROM clause, so you should add FROM DUAL or something like it to test this sample with Oracle.)

This is a limitation of H2. It can be resolved somehow, however.

2. NPE with unknown database mode is a bug. I'll take a look on it.

[LukeWoodward]

Thanks for your prompt reply, @katzyn. I've updated my StackOverflow answer to mention the workaround you provided.

I agree that with Oracle you have to add FROM DUAL as you can't select from no tables. I omitted it from my code above as it wasn't necessary to reproduce the problem with H2.

[katzyn]

The biggest problem here is a metadata of result set. I tried to add a fallback code to arithmetic operations with ? arguments, it works perfectly, but metadata obviously cannot return a column type. It breaks ODBC layer completely, JDBC is fine but only if data types from metadata are not accessed.

Now I think that the only reliable solution is to detect unknown result types in queries. If they are detected, expressions should be re-optimized (with a known values of parameters) in a temporary copy and this copy should be used during current execution of this query. Next execution with different parameters should create own temporary copy (or may try to reuse existing if types of parameters are not changed).

[grandinj]

Now I think that the only reliable solution is to detect unknown result types in queries.

That might be trickier than it looks sometimes. Sometimes we can quite happily deduce the correct type for a param, but not always.

If they are detected, expressions should be re-optimized (with a known values of parameters) in a temporary copy and this copy should be used during current execution of this query. Next execution with different parameters should create own temporary copy (or may try to reuse existing if types of parameters are not changed).

Yup, this is pretty much the same conclusion I came to when I looked at this a while ago. It might mean extending the cache to also key on parameter types. But since parameter types are unlikely to change, probably best to just check if the statement in the cache has matching params, and evict it if it does not (so that we catch the very few parameter types that evolve over time)

[katzyn]

That might be trickier than it looks sometimes. Sometimes we can quite happily deduce the correct type for a param, but not always.

I mean detect untyped expressions in query. SELECT ? + 10 has unnamed untyped column, but SELECT CAST(? AS INT) + 10 has unnamed column with type INT, this situation is fine and does not require additional steps.

To implement it properly expressions that cannot detect its output type should not set it into NUMERIC, NULL (?), VARCHAR or something else, but return Value.UNKNOWN instead. Expressions that need to know exact type of values (like addition operation) should not throw exception during initial optimization phase, but return unknown type of result instead. It should not be very hard.

[grandinj]

Sounds like a reasonable plan

[bjoshua80]

I had similar issue, this is working for me: simple add 0.0

@Query("select p from Presentity p WHERE p.state='E' OR (p.state='S' AND p.expirationDate < (CURRENT_DATE - (:lpsDeletionGuardTimeInDay + 0.0)))") List<Presentity> listSubscriptionsExpired(Double lpsDeletionGuardTimeInDay);

[antoninoromeo]

It works great! many thks!

insert into VARIABLE_COMP (ID_VARIABLE, ID_COMPONENT_VARIABLE, WEIGHT) with names as (

	<foreach collection="list" item="param" separator=" union all  ">
	 select   CAST(#{param.idVariable} AS NUMBER), 
	 		 (select ov.id from OPTIM_VARIABLE ov join VARIABLE_TYPE vt on ov.id_variable_type=vt.id where ov.code=#{param.codeComponentVariable} 
	 		 	and vt.code=#{param.typeCodeComponentVariable}),
	 		   CAST(#{param.weight}  AS NUMBER) from dual  
	</foreach>
	
	)
	select * from names 
</insert>

1. H2 tries to compile prepared statement immediately. Subtraction operation between different data types works in very different ways and it needs to know data types of its arguments. You can add an explicit cast as workaround.

SELECT SYSDATE - CAST(? AS INTEGER)

(Actually Oracle does not support selects without FROM clause, so you should add FROM DUAL or something like it to test this sample with Oracle.)

This is a limitation of H2. It can be resolved somehow, however.

1. NPE with unknown database mode is a bug. I'll take a look on it.

[javaHelper]

Can anyone pls guide here - https://stackoverflow.com/questions/69163983/jpa-and-sysdate-issue-forcefully-accepting-double-data-types?