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?
org.hibernate.SQLtoDEBUGandorg.hibernate.type.descriptor.sql.BasicBindertoTRACE. 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.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]