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

Question

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.

fredt · Accepted Answer · 2022-04-17 13:13:01Z

2

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.

answered Apr 17, 2022 at 13:13

fredt

24.5k3 gold badges43 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

davey Over a year ago

thanks for answer, for me this is a bug as version update makes existing code fail. hsqld should not fail on NULL, it should ignore the datatype. we don't want to change code, so we will stay on 2.5.1

Jens Schauder Over a year ago

do you happen to have a link to the issue tracker for the regression?

fredt Over a year ago

Issue tracker link: sourceforge.net/p/hsqldb/bugs/1641

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related