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

**URL:** <https://discourse.hibernate.org/t/upgrade-to-6-3-rc1-cannot-compare-left-expression-of-type-java-sql-timestamp-with-right-expression-of-type-com-model-entity-impl-user/8034>\
**Category:** Hibernate ORM\
**Created:** [July 26, 2023, 1:31pm UTC](https://discourse.hibernate.org/t/upgrade-to-6-3-rc1-cannot-compare-left-expression-of-type-java-sql-timestamp-with-right-expression-of-type-com-model-entity-impl-user/8034 "2023-07-26T13:31:06Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![techdiver](https://avatars.discourse-cdn.com/v4/letter/t/e5b9ba/32.png) [@techdiver](https://discourse.hibernate.org/u/techdiver)\
**Post date:** [July 26, 2023, 1:31pm UTC](https://discourse.hibernate.org/t/upgrade-to-6-3-rc1-cannot-compare-left-expression-of-type-java-sql-timestamp-with-right-expression-of-type-com-model-entity-impl-user/8034/1 "2023-07-26T13:31:06Z")

</div>

Hi,

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

```auto
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:

```auto
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:

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

```

Sub query entity:

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

```

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

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [July 26, 2023, 2:51pm UTC](https://discourse.hibernate.org/t/upgrade-to-6-3-rc1-cannot-compare-left-expression-of-type-java-sql-timestamp-with-right-expression-of-type-com-model-entity-impl-user/8034/2 "2023-07-26T14:51:16Z")

</div>

The bug is in your code

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

```

should be

```java
Subquery subquery = query.subquery(Date.class);

```

---

<div class="post-metadata">

**Author:** ![techdiver](https://avatars.discourse-cdn.com/v4/letter/t/e5b9ba/32.png) [@techdiver](https://discourse.hibernate.org/u/techdiver)\
**Post date:** [July 26, 2023, 3:51pm UTC](https://discourse.hibernate.org/t/upgrade-to-6-3-rc1-cannot-compare-left-expression-of-type-java-sql-timestamp-with-right-expression-of-type-com-model-entity-impl-user/8034/3 "2023-07-26T15:51:08Z")

</div>

Cheers for the reply that fixed it.

Do you know why that worked in previous versions?

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [July 26, 2023, 4:58pm UTC](https://discourse.hibernate.org/t/upgrade-to-6-3-rc1-cannot-compare-left-expression-of-type-java-sql-timestamp-with-right-expression-of-type-com-model-entity-impl-user/8034/4 "2023-07-26T16:58:53Z")

</div>

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)`

---

<div class="post-metadata">

**Author:** ![SBar](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/sbar/32/2623_2.png) [@SBar](https://discourse.hibernate.org/u/SBar)\
**Post date:** [November 20, 2023, 7:39am UTC](https://discourse.hibernate.org/t/upgrade-to-6-3-rc1-cannot-compare-left-expression-of-type-java-sql-timestamp-with-right-expression-of-type-com-model-entity-impl-user/8034/5 "2023-11-20T07:39:38Z")

</div>

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

```auto
@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);

```

```auto
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

```

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [November 20, 2023, 5:15pm UTC](https://discourse.hibernate.org/t/upgrade-to-6-3-rc1-cannot-compare-left-expression-of-type-java-sql-timestamp-with-right-expression-of-type-com-model-entity-impl-user/8034/6 "2023-11-20T17:15:42Z")

</div>

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))`

---

<div class="post-metadata">

**Author:** ![samba](https://avatars.discourse-cdn.com/v4/letter/s/df788c/32.png) [@samba](https://discourse.hibernate.org/u/samba)\
**Post date:** [December 13, 2023, 7:47am UTC](https://discourse.hibernate.org/t/upgrade-to-6-3-rc1-cannot-compare-left-expression-of-type-java-sql-timestamp-with-right-expression-of-type-com-model-entity-impl-user/8034/7 "2023-12-13T07:47:27Z")

</div>

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

```auto
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:

```auto
@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.

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [December 13, 2023, 8:23am UTC](https://discourse.hibernate.org/t/upgrade-to-6-3-rc1-cannot-compare-left-expression-of-type-java-sql-timestamp-with-right-expression-of-type-com-model-entity-impl-user/8034/8 "2023-12-13T08:23:07Z")

</div>

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](https://github.com/hibernate/hibernate-test-case-templates/blob/master/orm/hibernate-orm-6/src/test/java/org/hibernate/bugs/JPAUnitTestCase.java)) and if you are able to reproduce the issue, create a bug ticket in our issue tracker([https://hibernate.atlassian.net](https://hibernate.atlassian.net)) and attach that reproducer.

---

<div class="post-metadata">

**Author:** ![samba](https://avatars.discourse-cdn.com/v4/letter/s/df788c/32.png) [@samba](https://discourse.hibernate.org/u/samba)\
**Post date:** [December 14, 2023, 8:32am UTC](https://discourse.hibernate.org/t/upgrade-to-6-3-rc1-cannot-compare-left-expression-of-type-java-sql-timestamp-with-right-expression-of-type-com-model-entity-impl-user/8034/9 "2023-12-14T08:32:30Z")

</div>

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:

```auto
@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.