[JIRA] (HHH-16318) Wrong SQL query generated when type() tested inside selectCase() expression
by Archie Cobbs (JIRA)
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=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100217- sha1:fd7c927 )
3 years
[JIRA] (HHH-16318) Wrong SQL query generated when type() tested inside selectCase() expression
by Archie Cobbs (JIRA)
Archie Cobbs ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *updated* an issue
Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiMDc2MjQ0YTY0... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16318?atlOrigin=eyJpIjoiMDc2Mj... ) HHH-16318 ( https://hibernate.atlassian.net/browse/HHH-16318?atlOrigin=eyJpIjoiMDc2Mj... ) Wrong SQL query generated when type() tested inside selectCase() expression ( https://hibernate.atlassian.net/browse/HHH-16318?atlOrigin=eyJpIjoiMDc2Mj... )
Change By: Archie Cobbs ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... )
*I have a test case (will add shortly) here: * [https://github.com/archiecobbs/hibernate-test-case-templates/tree/HHH-163...]
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=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100217- sha1:fd7c927 )
3 years
[JIRA] (HHH-16318) Wrong SQL query generated when type() tested inside selectCase() expression
by Archie Cobbs (JIRA)
Archie Cobbs ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *created* an issue
Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiMmRiNDNhMDE4... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16318?atlOrigin=eyJpIjoiMmRiND... ) HHH-16318 ( https://hibernate.atlassian.net/browse/HHH-16318?atlOrigin=eyJpIjoiMmRiND... ) Wrong SQL query generated when type() tested inside selectCase() expression ( https://hibernate.atlassian.net/browse/HHH-16318?atlOrigin=eyJpIjoiMmRiND... )
Issue Type: Bug Affects Versions: 5.6.15, 6.1.7 Assignee: Unassigned Components: query-criteria Created: 15/Mar/2023 10:51 AM Priority: Major Reporter: Archie Cobbs ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... )
*I have a test case (will add shortly)*
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:
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);
Note that there is no WHERE clause in the query.
Here is the SQL that Hibernate generates:
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
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:
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)
( 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=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100217- sha1:fd7c927 )
3 years