UnknownTableReferenceException in subquery selecting embeddable foreign key
Description
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 PMEdited
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.
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 omittedMy 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)