[hibernate-issues] [JIRA] (HHH-16979) SQLGrammarException for JOINED entity with additional join in query

Timon Zilles ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *created* an issue Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiMDdlZDJkY2Rl... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16979?atlOrigin=eyJpIjoiMDdlZD... ) HHH-16979 ( https://hibernate.atlassian.net/browse/HHH-16979?atlOrigin=eyJpIjoiMDdlZD... ) SQLGrammarException for JOINED entity with additional join in query ( https://hibernate.atlassian.net/browse/HHH-16979?atlOrigin=eyJpIjoiMDdlZD... ) Issue Type: Bug Affects Versions: 6.2.6 Assignee: Unassigned Components: query-criteria Created: 24/Jul/2023 02:44 AM Priority: Major Reporter: Timon Zilles ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) Constellation / Problem ----------------------- I have the following entities/objects * MasterEntity * ChildEntity * RelatedEntity * ChildPojo (used for the query) See https://postimg.cc/JH8h5t4h (unfortunately I cannot upload any pictures to this Jira board) The MasterEntity has the interhitance strategy JOINED. @Entity @Inheritance(strategy = InheritanceType.JOINED) public class MasterEntity { // ... } The ChildEntity extends the MasterEntity and has a (bi-directional) one-to-many relatio to RelatedEntity. @Entity public class ChildEntity extends MasterEntity { // ... @OneToMany(mappedBy = "childEntity" ) public Set<RelatedEntity> relatedEntitySet; } Additionally there is a ChildPojo , which is used in a criteria query. When executing now a query with a join to RelatedEntity , then I get an exception, as it looses the join to the MasterEntity. Criteria Query @Transactional List<ChildPojo> getByQueryWithAdditionalJoin() { CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<ChildPojo> query = cb.createQuery(ChildPojo.class); Root<ChildEntity> root = query.from(ChildEntity.class); Join<ChildEntity, RelatedEntity> relatedEntityJoin = root.join( "relatedEntitySet" ); query.multiselect( root.get( "masterField" ), root.get( "childField" ), relatedEntityJoin.get( "relatedField" ) ); TypedQuery<ChildPojo> result = em.createQuery(query); List<ChildPojo> resultList = result.getResultList(); return resultList; } Generated Query Hibernate: select c1_1.masterField, c1_0.childField, r1_0.relatedField from ChildEntity c1_0 join RelatedEntity r1_0 on c1_0.id=r1_0.childEntity_id Here the join to the MasterEntity is missing. Exception (tries to get a field for a not known alias) org.hibernate.exception.SQLGrammarException: could not prepare statement [Column "C1_1.MASTERFIELD" not found; SQL statement: select c1_1.masterField,c1_0.childField,r1_0.relatedField from ChildEntity c1_0 join RelatedEntity r1_0 on c1_0.id=r1_0.childEntity_id [42122-214]] [select c1_1.masterField,c1_0.childField,r1_0.relatedField from ChildEntity c1_0 join RelatedEntity r1_0 on c1_0.id=r1_0.childEntity_id] at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:64) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:56) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:108) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:187) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareStatement(StatementPreparerImpl.java:76) at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.lambda$list$0(JdbcSelectExecutorStandardImpl.java:102) at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:226) at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.getResultSet(DeferredResultSetAccess.java:163) at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.advanceNext(JdbcValuesResultSetImpl.java:254) at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.processNext(JdbcValuesResultSetImpl.java:134) at org.hibernate.sql.results.jdbc.internal.AbstractJdbcValues.next(AbstractJdbcValues.java:19) at org.hibernate.sql.results.internal.RowProcessingStateStandardImpl.next(RowProcessingStateStandardImpl.java:66) at org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:198) at org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:33) at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.doExecuteQuery(JdbcSelectExecutorStandardImpl.java:361) at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.executeQuery(JdbcSelectExecutorStandardImpl.java:168) at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.list(JdbcSelectExecutorStandardImpl.java:93) at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:31) at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.lambda$new$0(ConcreteSqmSelectQueryPlan.java:110) at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.withCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:303) at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.performList(ConcreteSqmSelectQueryPlan.java:244) at org.hibernate.query.sqm.internal.QuerySqmImpl.doList(QuerySqmImpl.java:518) at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:367) at org.hibernate.query.Query.getResultList(Query.java:119) at org.acme.QueryTestBean.getByQueryWithAdditionalJoin(QueryTestBean.java:84) // ... Without the (manual) join, the query looks like expected (but of course without the needed join to the RelatedEntity ): Hibernate: select c1_1.masterField, c1_0.childField from ChildEntity c1_0 join MasterEntity c1_1 on c1_0.id=c1_1.id In *Hibernate 5.6.15.Final* the same constellation (with the additional join) works fine: Hibernate: select childentit0_1_.masterField as col_0_0_, childentit0_.childField as col_1_0_, relatedent1_.relatedField as col_2_0_ from ChildEntity childentit0_ inner join MasterEntity childentit0_1_ on childentit0_.id=childentit0_1_.id inner join RelatedEntity relatedent1_ on childentit0_.id=relatedent1_.childEntity_id I created two reproducers for this - one with Hibernate 5 and one with Hibernate 6. Both uses Quarkus, but the error can also be reproduced with “plain” Hibernate. Simply execute a mvn clean install and you will get the error for the Hibernate 6 project. The Hibernate 5 project works as expected. * Reproducer Hibernate 6: https://github.com/timonzi/hibernate-6-query-with-join * Reproducer Hibernate 5: https://github.com/timonzi/hibernate-5-query-with-join Hint: Possibly this is related to https://hibernate.atlassian.net/browse/HHH-16967 ( https://hibernate.atlassian.net/browse/HHH-16979#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16979#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#100232- sha1:a133497 )