[hibernate-issues] [JIRA] (HHH-16318) Wrong SQL query generated when type() tested inside selectCase() expression

Archie Cobbs ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *updated* an issue Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiM2MwNGYxMjhh... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16318?atlOrigin=eyJpIjoiM2MwNG... ) HHH-16318 ( https://hibernate.atlassian.net/browse/HHH-16318?atlOrigin=eyJpIjoiM2MwNG... ) Wrong SQL query generated when type() tested inside selectCase() expression ( https://hibernate.atlassian.net/browse/HHH-16318?atlOrigin=eyJpIjoiM2MwNG... ) Change By: Archie Cobbs ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *I have a test case here:* [https://github.com/archiecobbs/hibernate-test-case-templates/tree/HHH-163...] _Note: in the_ {{hibernate-orm-6}} _test case, the Maven test will succeed; however, you can see in the log output that invalid SQL is being generated._ If you test for entity type via {{type()}} inside a {{selectCase()}} expression, Hibernate generates incorrect SQL for the query. My test case looks like this: {code:java}final CriteriaBuilder cb = entityManager.getCriteriaBuilder(); final CriteriaQuery<Integer> query = cb.createQuery(Integer.class); final Root<Animal> animal = query.from(Animal.class); final Expression<Boolean> isDog = JPAUtil.instanceOf(entityManager, animal, Dog.class); final Expression<Integer> numSpots = cb.<Integer>selectCase() .when(isDog, cb.treat(animal, Dog.class).get(Dog_.numSpots)) .otherwise(cb.literal(0)); query.select(numSpots);{code} Note that there is no {{WHERE}} clause in the query. Here is the SQL that Hibernate generates: {code:sql}select case when a1_0.type in(?) then a1_0.numSpots else 0 end from (select * from Animal t where t.type='D') a1_0{code} Note that the SQL query contains {{where t.type='D'}} - this clause should not be there. We want to query over all animals, not just dogs. The above behavior is from Hibernate 6.1.7. In 5.6.15, you get this exception instead: {noformat}java.lang.ClassCastException: class org.hibernate.hql.internal.ast.tree.NullNode cannot be cast to class org.hibernate.hql.internal.ast.tree.FromReferenceNode (org.hibernate.hql.internal.ast.tree.NullNode and org.hibernate.hql.internal.ast.tree.FromReferenceNode are in unnamed module of loader 'app') at org.hibernate.hql.internal.ast.tree.MethodNode.typeDiscriminator(MethodNode.java:105) at org.hibernate.hql.internal.ast.tree.MethodNode.resolve(MethodNode.java:79) at org.hibernate.hql.internal.ast.HqlSqlWalker.processFunction(HqlSqlWalker.java:1253) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.functionCall(HqlSqlBaseWalker.java:2844) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.expr(HqlSqlBaseWalker.java:1395) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.exprOrSubquery(HqlSqlBaseWalker.java:4797) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.comparisonExpr(HqlSqlBaseWalker.java:4575) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:2180) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.searchedCaseWhenClause(HqlSqlBaseWalker.java:5580) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.searchedCaseExpression(HqlSqlBaseWalker.java:5361) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.caseExpr(HqlSqlBaseWalker.java:5237) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.arithmeticExpr(HqlSqlBaseWalker.java:3442) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectExpr(HqlSqlBaseWalker.java:2468) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectExprList(HqlSqlBaseWalker.java:2275) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectClause(HqlSqlBaseWalker.java:1534) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:611) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:339) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:287) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:276) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:192) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144) at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:112) at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:73) at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:162) at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:636) at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:748) at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:811) at org.hibernate.query.criteria.internal.CriteriaQueryImpl$1.buildCompiledQuery(CriteriaQueryImpl.java:314) at org.hibernate.query.criteria.internal.compile.CriteriaCompiler.compile(CriteriaCompiler.java:170) at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:774) at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:23) at org.hibernate.bugs.JPAUnitTestCase.hhh123Test(JPAUnitTestCase.java:56){noformat} ( https://hibernate.atlassian.net/browse/HHH-16318#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16318#add-comment?atlOrigin=ey... ) Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=Em... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100217- sha1:fd7c927 )