UnknownTableReferenceException in subquery selecting embeddable foreign key

Description

Hi,
I got the following exception when migrating from spring boot 3.0 to 3.1 with default JPA version, so from Hibernate 6.1 to 6.2:

org.springframework.orm.jpa.JpaSystemException: Unable to determine TableReference (`infra_operation`) for `io.paasas.server.environment.module.adapter.database.entity.InfraOperationEntity(existingInfraOperation).environment.{id}(10893200763000)` at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:320) ~[spring-orm-6.0.12.jar:6.0.12] at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:229) ~[spring-orm-6.0.12.jar:6.0.12] at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:550) ~[spring-orm-6.0.12.jar:6.0.12] at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61) ~[spring-tx-6.0.12.jar:6.0.12] at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:243) ~[spring-tx-6.0.12.jar:6.0.12] at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152) ~[spring-tx-6.0.12.jar:6.0.12] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.12.jar:6.0.12] at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:135) ~[spring-data-jpa-3.1.4.jar:3.1.4] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.12.jar:6.0.12] at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97) ~[spring-aop-6.0.12.jar:6.0.12] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.12.jar:6.0.12] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:244) ~[spring-aop-6.0.12.jar:6.0.12] at jdk.proxy2/jdk.proxy2.$Proxy202.findEnvironmentWithOperations(Unknown Source) ~[na:na] at io.paasas.server.environment.module.adapter.database.DatabaseInfraOperationRepository.findOperationsToProcess(DatabaseInfraOperationRepository.java:142) ~[classes/:na] at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na] at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na] at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na] at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na] at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343) ~[spring-aop-6.0.12.jar:6.0.12] at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196) ~[spring-aop-6.0.12.jar:6.0.12] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-6.0.12.jar:6.0.12] at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:751) ~[spring-aop-6.0.12.jar:6.0.12] at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) ~[spring-tx-6.0.12.jar:6.0.12] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.12.jar:6.0.12] at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:751) ~[spring-aop-6.0.12.jar:6.0.12] at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:703) ~[spring-aop-6.0.12.jar:6.0.12] at io.paasas.server.environment.module.adapter.database.DatabaseInfraOperationRepository$$SpringCGLIB$$0.findOperationsToProcess(<generated>) ~[classes/:na] at io.paasas.server.environment.module.InfraOperationProcessor.process(InfraOperationProcessor.java:41) ~[classes/:na] at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na] at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na] at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na] at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na] at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:84) ~[spring-context-6.0.12.jar:6.0.12] at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54) ~[spring-context-6.0.12.jar:6.0.12] at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539) ~[na:na] at java.base/java.util.concurrent.FutureTask.runAndReset(FutureTask.java:305) ~[na:na] at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305) ~[na:na] at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) ~[na:na] at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) ~[na:na] at java.base/java.lang.Thread.run(Thread.java:833) ~[na:na] Caused by: org.hibernate.sql.ast.tree.from.UnknownTableReferenceException: Unable to determine TableReference (`infra_operation`) for `io.paasas.server.environment.module.adapter.database.entity.InfraOperationEntity(existingInfraOperation).environment.{id}(10893200763000)` at org.hibernate.sql.ast.tree.from.ColumnReferenceQualifier.resolveTableReference(ColumnReferenceQualifier.java:74) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.metamodel.mapping.internal.BasicAttributeMapping.resolveSqlSelection(BasicAttributeMapping.java:324) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.metamodel.mapping.internal.BasicAttributeMapping.applySqlSelections(BasicAttributeMapping.java:346) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.metamodel.mapping.EmbeddableMappingType.lambda$applySqlSelections$3(EmbeddableMappingType.java:246) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.metamodel.mapping.internal.MutableAttributeMappingList.forEach(MutableAttributeMappingList.java:46) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.metamodel.mapping.internal.AbstractEmbeddableMapping.forEachAttributeMapping(AbstractEmbeddableMapping.java:557) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.metamodel.mapping.EmbeddableMappingType.applySqlSelections(EmbeddableMappingType.java:245) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.metamodel.mapping.internal.EmbeddedAttributeMapping.applySqlSelections(EmbeddedAttributeMapping.java:230) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.sqm.sql.internal.AbstractSqmPathInterpretation.applySqlSelections(AbstractSqmPathInterpretation.java:65) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.lambda$visitSelection$25(BaseSqmToSqlAstConverter.java:2316) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at java.base/java.util.Collections$SingletonList.forEach(Collections.java:4966) ~[na:na] at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitSelection(BaseSqmToSqlAstConverter.java:2311) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitSelectClause(BaseSqmToSqlAstConverter.java:2207) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQuerySpec(BaseSqmToSqlAstConverter.java:2051) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQuerySpec(BaseSqmToSqlAstConverter.java:435) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.sqm.tree.select.SqmQuerySpec.accept(SqmQuerySpec.java:125) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.sqm.spi.BaseSemanticQueryWalker.visitQueryPart(BaseSemanticQueryWalker.java:226) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQueryPart(BaseSqmToSqlAstConverter.java:1909) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitSubQueryExpression(BaseSqmToSqlAstConverter.java:6683) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitSubQueryExpression(BaseSqmToSqlAstConverter.java:435) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.sqm.tree.select.SqmSubQuery.accept(SqmSubQuery.java:663) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitWithInferredType(BaseSqmToSqlAstConverter.java:6801) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitInSubQueryPredicate(BaseSqmToSqlAstConverter.java:7732) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitInSubQueryPredicate(BaseSqmToSqlAstConverter.java:435) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.sqm.tree.predicate.SqmInSubQueryPredicate.accept(SqmInSubQueryPredicate.java:87) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitJunctionPredicate(BaseSqmToSqlAstConverter.java:6981) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitJunctionPredicate(BaseSqmToSqlAstConverter.java:435) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.sqm.tree.predicate.SqmJunctionPredicate.accept(SqmJunctionPredicate.java:74) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitWhereClause(BaseSqmToSqlAstConverter.java:2478) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQuerySpec(BaseSqmToSqlAstConverter.java:2055) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQuerySpec(BaseSqmToSqlAstConverter.java:435) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.sqm.tree.select.SqmQuerySpec.accept(SqmQuerySpec.java:125) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.sqm.spi.BaseSemanticQueryWalker.visitQueryPart(BaseSemanticQueryWalker.java:226) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQueryPart(BaseSqmToSqlAstConverter.java:1909) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitSelectStatement(BaseSqmToSqlAstConverter.java:1594) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitSelectStatement(BaseSqmToSqlAstConverter.java:435) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.sqm.tree.select.SqmSelectStatement.accept(SqmSelectStatement.java:222) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.translate(BaseSqmToSqlAstConverter.java:771) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.buildCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:345) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.withCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:268) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.performList(ConcreteSqmSelectQueryPlan.java:244) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.sqm.internal.QuerySqmImpl.doList(QuerySqmImpl.java:518) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:367) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at org.hibernate.query.Query.getResultList(Query.java:119) ~[hibernate-core-6.2.9.Final.jar:6.2.9.Final] at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na] at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na] at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na] at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na] at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:405) ~[spring-orm-6.0.12.jar:6.0.12] at jdk.proxy2/jdk.proxy2.$Proxy193.getResultList(Unknown Source) ~[na:na] at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:129) ~[spring-data-jpa-3.1.4.jar:3.1.4] at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:92) ~[spring-data-jpa-3.1.4.jar:3.1.4] at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:148) ~[spring-data-jpa-3.1.4.jar:3.1.4] at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:136) ~[spring-data-jpa-3.1.4.jar:3.1.4] at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:136) ~[spring-data-commons-3.1.4.jar:3.1.4] at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:120) ~[spring-data-commons-3.1.4.jar:3.1.4] at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:164) ~[spring-data-commons-3.1.4.jar:3.1.4] at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:143) ~[spring-data-commons-3.1.4.jar:3.1.4] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.12.jar:6.0.12] at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:72) ~[spring-data-commons-3.1.4.jar:3.1.4] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.12.jar:6.0.12] at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) ~[spring-tx-6.0.12.jar:6.0.12] at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:391) ~[spring-tx-6.0.12.jar:6.0.12] at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-6.0.12.jar:6.0.12] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.12.jar:6.0.12] at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) ~[spring-tx-6.0.12.jar:6.0.12] ... 34 common frames omitted

My entity definition is:

@Data @EqualsAndHashCode @NoArgsConstructor @AllArgsConstructor @Builder(toBuilder = true) @Entity(name = "InfraOperation") @FieldDefaults(level = AccessLevel.PRIVATE) public class InfraOperationEntity { @Id String id; LocalDateTime createdTimestamp; @ManyToOne EnvironmentEntity environment; @Column(length = 16_777_216) @Lob String errorMessage; @Column(length = 16_777_216) @Lob String stackstrace; @Enumerated(EnumType.STRING) InfraOperationStatus status; @Enumerated(EnumType.STRING) InfraOperationType type; @Enumerated(EnumType.STRING) InfraVersion infraVersion; LocalDateTime updatedTimestamp; }

And the problematic query is:

@Query(""" SELECT DISTINCT infraOperation.environment FROM InfraOperation infraOperation WHERE infraOperation.status IN ('PENDING') AND infraOperation.environment.key NOT IN ( SELECT existingInfraOperation.environment.key FROM InfraOperation existingInfraOperation WHERE existingInfraOperation.status IN ('IN_PROGRESS', 'ERROR') ) """) List<EnvironmentEntity> findEnvironmentWithOperations(Pageable pageable);

Everything works fine for standard JPA requests, this happens only with native queries (other tables have the same issues)

Attachments

3
  • 18 Oct 2023, 03:00 PM
  • 18 Oct 2023, 02:55 PM
  • 29 Sep 2023, 06:31 PM

Activity

Show:

Marco Belladelli November 28, 2023 at 8:11 AM

We won’t be back-porting this fix to previous versions, you will have to update to version 6.4.1.

Arnould Gateaux November 27, 2023 at 6:00 PM

Will this fix be ported into 6.2 or 6.3? I see you put 6.4.1.

Arnould Gateaux November 27, 2023 at 5:56 PM

Big thanks for resolving this

Arnould Gateaux November 17, 2023 at 3:26 PM

Hi,
Any idea for a date of delivery?

Thanks.

Arnould Gateaux October 18, 2023 at 2:57 PM
Edited

The issue seems related to this @EmbeddedID. With an @Id on a simple String, it works. With a key in embeddedId, this happens.

Just in case, I’ve updated the framework test case with this change.

Fixed

Details

Assignee

Reporter

Worked in

Sprint

Fix versions

Affects versions

Priority

Created September 29, 2023 at 6:29 PM
Updated December 15, 2023 at 6:39 PM
Resolved November 27, 2023 at 8:05 AM