2

we are facing one problem. In our code that runs for year at least, we use spring repositories with custom query methods, using @Query annotation. In these methods we tend to apply filters on queries, based on passed in @Nullable parameters. So far it was working nice, for queries like this:

@Query(value =
    "select p FROM PersonOfInterestEntity p "
        + "where (?1 is null or p.upperPoiIdentifier like ?1) "
        + "and p.status in ?2 "
        + "and (?3 is null or p.createdDtm >= ?3) "
        + "and (?4 is null or p.createdDtm <= ?4) "
        + "and p.ixTenant.id=?5 ")
Page<PersonOfInterestEntity> findAllByNamesAndDateRangeAndStatusesAndTenantId(
    @Nullable String identifier, List<StatusEnum> statuses, @Nullable Timestamp startDate, @Nullable Timestamp endDate, String tenantId, Pageable pageable);

As found on articles on net, that is handy way to have single dao method that can be used to query with different filters, and to skip conditions on certain columns in case that passed in values are null. But problem is that once we upgraded to hibernate 5.4.10.Final (we used hibernate 5.2.8.Final so far), we suddenly started getting error:

[ WARN] [  org.hibernate.engine.jdbc.spi.SqlExceptionHelper] - SQL Error: 932, SQLState: 42000
[ERROR] [  org.hibernate.engine.jdbc.spi.SqlExceptionHelper] - ORA-00932: inconsistent datatypes: expected TIMESTAMP got BINARY

Trouble is that it is not only hibernate what is upgraded, but also whole bunch of dependencies that we get from some third party team - so we can't really state that Hibernate is what makes the difference here. Also, we can't decrease Hibernate version back to 5.2.8 in upgraded code, neither we can increase Hibernate version in old code that works fine with these queries, since in both case we get some startup issues -> that makes hard to isolate what actually causes that change of behavior. Interesting is that same syntax still seem to work for parameters of type String (see ?1 directly after where keyword in query), issue seems related only to Timestamp.

My question is, should we look at change in hibernate, spring-data-jpa or Oracle driver? I couldn't find any documentation that would state such backward inconsistency. We would really like to skip need for workaround to split our methods to separate queries for each null/non null parameter, since that would really increase the mess in code since we have a lot of combinations of these optional parameters. And also would like to understand how it was working so far, and why Timestamp is that different than other types.

update: I just realized we have also some special class that affects how Timestamps in code are serialized into database. Due to planned port to MySql, there was some need to round times in db on 3/100 of ms, so we have UserType that should be responsible to do that. This might somehow impact that query params binding, not sure... From debugging the hibernate code (that is quite hard since it does not get on thread calls stack in IDE), I noticed that PreparedStatementParameter for part of query like this: p.createdDtm >= ?3, gets internal expectedType as CustomType, with name of that special class that was added. part of query for parameter with same index: ?3 is null, gets resolved without expected type, that later gets resolved in binding as (sqlTypeDescriptior=VarbinaryTypeDescriptor and javaTypeDescriptor=SerializableTypeDescriptor), and later results with error that I gave upper. But also to say, that code that adjusts Timestamp values already existed in previous version of code that was working ok on hibernate 5.2.8... Maybe that custom type requires some adjustments for newer versions of hibernate?

6
  • please post the source of PersonOfInterestEntity class Commented Jan 9, 2020 at 15:31
  • Ideally set log level of org.hibernate.SQL to DEBUG and org.hibernate.type.descriptor.sql.BasicBinder to TRACE. By the way we used such syntax as well, but it turned up to dramatically decrease the query performance sometimes, because the database had little chance to come up with a right plan. So we dropped such constructs. Commented Jan 9, 2020 at 15:39
  • hi, logs are already set: Commented Jan 10, 2020 at 11:25
  • logging.level.org.hibernate.type.descriptor.sql=TRACE, in hib 5.2.8.Final it produces this log: binding parameter [1] as [VARCHAR] - [null] binding parameter [2] as [VARCHAR] - [null] binding parameter [10] as [VARCHAR] - [4dqv9WCZenvofaDVIipeuA], while in hib 5.4.0.Final it gives this log: binding parameter [1] as [VARBINARY] - [null] binding parameter [2] as [VARBINARY] - [null] binding parameter [6] as [VARBINARY] - [null] (same for [7, 8, ,9]... comment is limited in stackoverflow) binding parameter [10] as [VARCHAR] - [mi6MBSusOXjc17EuLBg-_A] Commented Jan 10, 2020 at 11:33
  • PersonOfInterestEntity is quite big... It also inherits some base class that contains that column that has issue, and we do not have sources of it... Decompiled code shows this for that column ` @Column( table = "", name = "CREATED_DTM" ) @Type( type = "timestamp" ) private Timestamp createdDtm;` as I noted, was working just fine until now... regarding performance, we didn't notice problems, but queries are quite simple an on single join at most, so was no issue so far Commented Jan 10, 2020 at 11:41

1 Answer 1

1

Ok, this is fun... After quite some debug and trying to understand why it got broken, I realized that same query works if we use named parameters !?

@Query(value =
    "select p FROM PersonOfInterestEntity p "
        + "where (:id is null or p.upperPoiIdentifier like :id) "
        + "and p.status in :statuses "
        + "and (:startDtm is null or p.createdDtm >= :startDtm) "
        + "and (:endDtm  is null or p.createdDtm <= :endDtm ) "
        + "and p.ixTenant.id=:tenantId ")
Page<PersonOfInterestEntity> findAllByNamesAndDateRangeAndStatusesAndTenantId(
    @Param("id") @Nullable String identifier,
    @Param("statuses") List<StatusEnum> statuses,
    @Param("startDtm") @Nullable Timestamp startDate,
    @Param("endDtm") @Nullable Timestamp endDate,
    @Param("tenantId") String tenantId,
    Pageable pageable);

I think that it proves that it is some bug in hibernate, since it should work the same for both queries? At least, I'm happy enough with this, hope it would also save someone else time in future ;)

Cheers!

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.