Upgrade to 6.3 RC1 - Cannot compare left expression of type 'java.sql.Timestamp' with right expression of type 'com.model.entity.impl.User'

Hibernate ORM
1

Hi,

I recently tested upgrading to 6.3.0.CR1 from 6.1.7.Final to test and received the below exception:

Caused by: org.hibernate.query.SemanticException: Cannot compare left expression of type 'java.sql.Timestamp' with right expression of type 'com.model.entity.impl.User'
	at org.hibernate.query.sqm.internal.TypecheckUtil.assertComparable(TypecheckUtil.java:325) ~[hibernate-core-6.3.0.CR1.jar:6.3.0.CR1]
	at org.hibernate.query.sqm.internal.SqmCriteriaNodeBuilder.greaterThanOrEqualTo(SqmCriteriaNodeBuilder.java:2148) ~[hibernate-core-6.3.0.CR1.jar:6.3.0.CR1]
	at org.hibernate.query.sqm.internal.SqmCriteriaNodeBuilder.greaterThanOrEqualTo(SqmCriteriaNodeBuilder.java:185) ~[hibernate-core-6.3.0.CR1.jar:6.3.0.CR1]
	at com.service.impl.UserServiceImpl$1.toPredicate(UserServiceImpl.java:277) ~[app-services-1.1.0.jar:1.1.0]
	at com.repository.specifications.query.QueryBuilderSpecificationAnd.toPredicate(QueryBuilderSpecificationAnd.java:51) ~[repository-1.1.0.jar:1.1.0]
	at com.repository.specifications.VisibilityProfileSpecification.toPredicate(VisibilityProfileSpecification.java:143) ~[epository-1.1.0.jar:1.1.0]
	at org.springframework.data.jpa.repository.support.SimpleJpaRepository.applySpecificationToCriteria(SimpleJpaRepository.java:829) ~[spring-data-jpa-3.1.2.jar:3.1.2]
	at org.springframework.data.jpa.repository.support.SimpleJpaRepository.getQuery(SimpleJpaRepository.java:748) ~[spring-data-jpa-3.1.2.jar:3.1.2]
	at org.springframework.data.jpa.repository.support.SimpleJpaRepository.getQuery(SimpleJpaRepository.java:733) ~[spring-data-jpa-3.1.2.jar:3.1.2]
	at com.repository.stream.impl.StreamRepositoryImpl.scrollableResults(StreamRepositoryImpl.java:121) ~[repository-1.1.0.jar:1.1.0]
	at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
	at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[?:?]
	at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
	at java.lang.reflect.Method.invoke(Method.java:568) ~[?:?]
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker$RepositoryFragmentMethodInvoker.lambda$new$0(RepositoryMethodInvoker.java:288) ~[spring-data-commons-3.1.2.jar:3.1.2]
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:136) ~[spring-data-commons-3.1.2.jar:3.1.2]
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:120) ~[spring-data-commons-3.1.2.jar:3.1.2]
	at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:516) ~[spring-data-commons-3.1.2.jar:3.1.2]
	at org.springframework.data.repository.core.support.RepositoryComposition.invoke(RepositoryComposition.java:285) ~[spring-data-commons-3.1.2.jar:3.1.2]
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:628) ~[spring-data-commons-3.1.2.jar:3.1.2]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.11.jar:6.0.11]
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:168) ~[spring-data-commons-3.1.2.jar:3.1.2]
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:143) ~[spring-data-commons-3.1.2.jar:3.1.2]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.11.jar:6.0.11]
	at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:72) ~[spring-data-commons-3.1.2.jar:3.1.2]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.11.jar:6.0.11]
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) ~[spring-tx-6.0.11.jar:6.0.11]
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:391) ~[spring-tx-6.0.11.jar:6.0.11]
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-6.0.11.jar:6.0.11]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.11.jar:6.0.11]
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) ~[spring-tx-6.0.11.jar:6.0.11]
	... 21 more

It is caused by this which was working in 6.1.7:

Subquery subquery = query.subquery(User.class);
Root ua = subquery.from(User.class);
subquery.select(ua.get("userDts"));
subquery.where(cb.equal(ua.get("id"), callingUser.getId()));
	
String propertyName = "myDate";
Predicate searchCriteria = cb.greaterThanOrEqualTo(root.get(propertyName), subquery);

The root entity (which is a view) contains:

@Column(name = "MY_DATE")
@Temporal(TemporalType.TIMESTAMP)
private Date myDate;

Sub query entity:

@Column(name = "USER_DTS", length = 11)
private Date userDts;

Is this a bug or some new change to the api usage?

2

The bug is in your code

Subquery subquery = query.subquery(User.class);

should be

Subquery subquery = query.subquery(Date.class);
1 Like
3

Cheers for the reply that fixed it.

Do you know why that worked in previous versions?

4

Because previous versions didn’t care to validate comparability of expressions and so ended up producing invalid SQL instead. Newer Hibernate versions do validate. Now the problem in your case is two fold actually and IMO you should have seen an error already in the line subquery.select( ua.get("userDts") ) because selecting something of type Date is not assignable to the result type User which you specified with query.subquery(User.class)

5

Could you please help on this too. I have recently upgraded springboot to 3 and I am getting this error on this query :

@Query(value = "SELECT NEW com.demo.domain.AImpl(a.startIp, a.endIp, a.cidr, "
+ "a.classcId, a.parametersId, a.location, a.entryDate) "
+ "FROM com.demo.domain.A a, Employee e, Algorithm al " + "WHERE a.parametersId = e.employeeId "
+ "and e.employeeName not like 'bulk%' " + "and a.algorithmId = al.algorithmId "
+ "and al.algorithmtypeId = 2 " + "and a.entryDate >= (sysdate - :exe_duration) ")
List<AImpl> findSomething(@Param("exe_duration") Integer exeDuration);

Caused by: org.hibernate.query.sqm.InterpretationException: Error interpreting query [SELECT NEW com.nsr.ipi.domain.IntAssignmentProjectionImpl(a.startIp, a.endIp, a.cidr, a.classcId, a.parametersId, a.location, a.entryDate) FROM com.nsr.ipi.domain.IntAssignment a, Employee e, Algorithm al WHERE a.parametersId = e.employeeId and e.employeeName not like 'bulk%' and a.algorithmId  = al.algorithmId and al.algorithmtypeId = 2 and a.entryDate >= (sysdate - :exe_duration)]; this may indicate a semantic (user query) problem or a bug in the parser [SELECT NEW com.nsr.ipi.domain.IntAssignmentProjectionImpl(a.startIp, a.endIp, a.cidr, a.classcId, a.parametersId, a.location, a.entryDate) FROM com.nsr.ipi.domain.IntAssignment a, Employee e, Algorithm al WHERE a.parametersId = e.employeeId and e.employeeName not like 'bulk%' and a.algorithmId  = al.algorithmId and al.algorithmtypeId = 2 and a.entryDate >= (sysdate - :exe_duration)]
	at org.hibernate.query.hql.internal.StandardHqlTranslator.translate(StandardHqlTranslator.java:97) ~[hibernate-core-6.2.6.Final.jar:6.2.6.Final]
	at org.hibernate.internal.AbstractSharedSessionContract.lambda$interpretHql$2(AbstractSharedSessionContract.java:744) ~[hibernate-core-6.2.6.Final.jar:6.2.6.Final]
	at org.hibernate.query.internal.QueryInterpretationCacheStandardImpl.createHqlInterpretation(QueryInterpretationCacheStandardImpl.java:141) ~[hibernate-core-6.2.6.Final.jar:6.2.6.Final]
	at org.hibernate.query.internal.QueryInterpretationCacheStandardImpl.resolveHqlInterpretation(QueryInterpretationCacheStandardImpl.java:128) ~[hibernate-core-6.2.6.Final.jar:6.2.6.Final]
	at org.hibernate.internal.AbstractSharedSessionContract.interpretHql(AbstractSharedSessionContract.java:741) ~[hibernate-core-6.2.6.Final.jar:6.2.6.Final]
	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:786) ~[hibernate-core-6.2.6.Final.jar:6.2.6.Final]
	... 98 common frames omitted
Caused by: java.lang.IllegalArgumentException: Can't compare test expression of type [BasicSqmPathSource(entryDate : Date)] with element of type [basicType@17(java.time.Duration,2)]
	at org.hibernate.query.sqm.internal.SqmCriteriaNodeBuilder.assertComparable(SqmCriteriaNodeBuilder.java:2098) ~[hibernate-core-6.2.6.Final.jar:6.2.6.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitComparisonPredicate(SemanticQueryBuilder.java:2493) ~[hibernate-core-6.2.6.Final.jar:6.2.6.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitComparisonPredicate(SemanticQueryBuilder.java:253) ~[hibernate-core-6.2.6.Final.jar:6.2.6.Final]
	at org.hibernate.grammars.hql.HqlParser$ComparisonPredicateContext.accept(HqlParser.java:6111) ~[hibernate-core-6.2.6.Final.jar:6.2.6.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitAndPredicate(SemanticQueryBuilder.java:2314) ~[hibernate-core-6.2.6.Final.jar:6.2.6.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitAndPredicate(SemanticQueryBuilder.java:253) ~[hibernate-core-6.2.6.Final.jar:6.2.6.Final]
	at org.hibernate.grammars.hql.HqlParser$AndPredicateContext.accept(HqlParser.java:6013) ~[hibernate-core-6.2.6.Final.jar:6.2.6.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitWhereClause(SemanticQueryBuilder.java:2297) ~[hibernate-core-6.2.6.Final.jar:6.2.6.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitWhereClause(SemanticQueryBuilder.java:253) ~[hibernate-core-6.2.6.Final.jar:6.2.6.Final]
	at org.hibernate.grammars.hql.HqlParser$WhereClauseContext.accept(HqlParser.java:5910) ~[hibernate-core-6.2.6.Final.jar:6.2.6.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitQuery(SemanticQueryBuilder.java:1161) ~[hibernate-core-6.2.6.Final.jar:6.2.6.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitQuerySpecExpression(SemanticQueryBuilder.java:937) ~[hibernate-core-6.2.6.Final.jar:6.2.6.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitQuerySpecExpression(SemanticQueryBuilder.java:253) ~[hibernate-core-6.2.6.Final.jar:6.2.6.Final]
	at org.hibernate.grammars.hql.HqlParser$QuerySpecExpressionContext.accept(HqlParser.java:1818) ~[hibernate-core-6.2.6.Final.jar:6.2.6.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSimpleQueryGroup(SemanticQueryBuilder.java:931) ~[hibernate-core-6.2.6.Final.jar:6.2.6.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSimpleQueryGroup(SemanticQueryBuilder.java:253) ~[hibernate-core-6.2.6.Final.jar:6.2.6.Final]
	at org.hibernate.grammars.hql.HqlParser$SimpleQueryGroupContext.accept(HqlParser.java:1711) ~[hibernate-core-6.2.6.Final.jar:6.2.6.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSelectStatement(SemanticQueryBuilder.java:418) ~[hibernate-core-6.2.6.Final.jar:6.2.6.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitStatement(SemanticQueryBuilder.java:377) ~[hibernate-core-6.2.6.Final.jar:6.2.6.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.buildSemanticModel(SemanticQueryBuilder.java:295) ~[hibernate-core-6.2.6.Final.jar:6.2.6.Final]
	at org.hibernate.query.hql.internal.StandardHqlTranslator.translate(StandardHqlTranslator.java:81) ~[hibernate-core-6.2.6.Final.jar:6.2.6.Final]
	... 103 common frames omitted
6

It’s simple, you can compare apples with oranges. Apples being Date and oranges being Duration i.e. a.entryDate >= (sysdate - :exe_duration). This simply makes no sense.
Note that Hibernate ORM has to assume a type for the parameter and it will just assume Date. Since Date - Date produces a Duration, you get this error.
You should be able to use the duration syntax to fix this: a.entryDate >= (sysdate - (:exe_duration days))

7

Excuse me for jumping in on this thread, but I have a similar issue, where I believe we are in fact comparing oranges to oranges, but something weird is going on with Hibernate.

We have the following types

public class Auditing<T extends AbstractEntity & Identifiable, E extends Enum<?>>
...
public class User extends AbstractEntity implements Identifiable<Long>
...
public class AuditEventUser extends Auditing<User, UserEventType>
...

where Identifiable<T> is an interface with a single T getId() method.

But then, doing this query that was working before Hibernate 6.3:

@Query("""
    SELECT MAX(h.eventInstant)
      FROM User u
      JOIN AuditEventUser a ON a.entity = u
      JOIN a.eventHistory h
     WHERE u.username = :username
       AND a.event = :event
    """)
  Instant getUserEventInstant(@Param("username") String username, @Param("event") UserEventType event);

It fails with the error mentioned in this thread, more specifically, saying Cannot compare left expression of type 'com.acme.domain.AbstractEntity' with right expression of type 'com.acme.domain.User', ignoring that Identifiable is the contract that it should be considering to see if types are comparable.

8

This could be a real bug. The ON a.entity = u predicate compares a path expression that is of the “static type” AbstractEntity against something of type User. Please try to create a reproducer with our test case template (hibernate-test-case-templates/orm/hibernate-orm-6/src/test/java/org/hibernate/bugs/JPAUnitTestCase.java at main · hibernate/hibernate-test-case-templates · GitHub) and if you are able to reproduce the issue, create a bug ticket in our issue tracker(https://hibernate.atlassian.net) and attach that reproducer.

9

In case this helps someone, for that specific situation, we were able to work around the issue by refactoring the queries a bit and using TREAT, like:

@Query("""
SELECT MAX(h.eventInstant)
      FROM AuditEventUser a
      JOIN TREAT(a.entity as User) u
      JOIN a.eventHistory h
     WHERE u.username = :username
       AND a.event = :event
""")

Unfortunately, the same change applied to dynamic queries using CriteriaBuilder#treat often leads to the exception org.hibernate.query.sqm.InterpretationException: Error interpreting query [SqmTreatedPath not yet resolved to TableGroup] which is proving to be very difficult to work around without major refactor.