incompatible data type in conversion in hsqldb when query IS NULL with java.sql.Timestamp with spring-data-jpa 2.6.2

user_1351687 0
Viewed 307

spring-boot-starter-data-jpa 2.6.4 has a dependency to spring-data-jpa 2.6.2 which has a dependency to hsqldb 2.5.2 and hibernate 5.6.7.Final

when we want to query mutliple values of type java.sql.Timestamp in a CrudRepository, i.e.

@Query("SELECT dm " +
        " FROM DataModel dm " +
        "WHERE (dm.ts1 IS NULL OR :ts1 IS NULL OR dm.ts1 < :ts1) " +
        "  AND (dm.ts2 IS NULL OR :ts2 IS NULL OR dm.ts2 < :ts2)")
List<DataModel> findByTimestamps(@Param("ts1") Timestamp ts1, @Param("ts2") Timestamp ts2);

it fails with org.hsqldb.HsqlException: incompatible data type in conversion

this also happens with spring-data-jpa 2.6.3

downgrade to hsqldb 2.5.1 works

Repo for reproduce: https://github.com/daveyx/spring-data-hsqldb-timestamp-issue

The problem occurs in org.hsqldb.jdbc.JDBCPreparedStatement#setTimestamp(int, java.sql.Timestamp, java.util.Calendar) because of a wrong parameterType.

It looks like the "IS NULL" part of the @Param annotated parameter leads to a "CharacterType" instead of a "DateTimeType" in JDBCPreparedStatement#setTimestamp.

spring-data-jpa hsqldb timestamp hibernate
1 Answers

As you mentioned, the problem is caused by the conditions OR :ts1 IS NULL and OR :ts2 IS NULL. Hibernate applies these to the SQL query in the form ? IS NULL and HSQLDB resolves the parameter as String. Hibernate then assumes the parameter type is resolved to java.sql.Timestamp and calls the setTimestamp(int, Timestamp) method, which causes the problem.

You can add CAST to determine the type of the parameter.

@Query("SELECT dm " +
    " FROM DataModel dm " +
    "WHERE (dm.ts1 IS NULL OR CAST(:ts1 AS TIMESTAMP) IS NULL OR dm.ts1 < :ts1) " +
    "  AND (dm.ts2 IS NULL OR CAST(:ts2 AS TIMESTAMP) IS NULL OR dm.ts2 < :ts2)")

HSQLDB version 2.5.1 was lenient with setTimestamp(int, Timestamp) and converted the timestamp to a String when the target was not a timestamp. A regression in version 2.5.2 and 2.6.0 made it strict. Future versions of HSQLDB will revert to the lenient approach.

user_4710700
Related