William Shallum

H2 Date Arithmetic vs Prepare

Posted Jun 17 2023, 04:44 by William Shallum

I encountered this issue when preparing a statement for the H2 database (for use in test).

Repro below (on H2 version 2.1.214):

try (var conn = org.h2.Driver.load().connect("jdbc:h2:mem:default", new Properties())) {
  var stmt = conn.prepareStatement("SELECT CURRENT_DATE - ?1");
  stmt.setInt(1, 1);
  try (var rs = stmt.executeQuery()) {
    while (rs.next()) {
      System.out.println(rs.getObject(1));
    }
  }
}

Error:

org.h2.jdbc.JdbcSQLFeatureNotSupportedException: Feature not supported: "DATE - UNKNOWN"; SQL statement:
SELECT CURRENT_DATE - ?1 [50100-214]
	at app//org.h2.message.DbException.getJdbcSQLException(DbException.java:556)
	at app//org.h2.message.DbException.getJdbcSQLException(DbException.java:477)
	at app//org.h2.message.DbException.get(DbException.java:223)
	at app//org.h2.message.DbException.get(DbException.java:199)
	at app//org.h2.message.DbException.getUnsupportedException(DbException.java:287)
	at app//org.h2.expression.BinaryOperation.getUnsupported(BinaryOperation.java:421)
	at app//org.h2.expression.BinaryOperation.optimizeDateTime(BinaryOperation.java:417)
	at app//org.h2.expression.BinaryOperation.optimize(BinaryOperation.java:148)
	at app//org.h2.command.query.Select.prepareExpressions(Select.java:1170)
	at app//org.h2.command.query.Query.prepare(Query.java:218)

Which just seems weird - why would failing to optimize fail the prepare? Just return it as-is and evaluate when you know the value… As far as I know you can’t even specify a type as part of the parameter placeholder.

To “fix”:

SELECT CURRENT_DATE - CAST(?1 AS INTEGER)