Spring Data JDBC @Embedded entity with @MappedCollection

[hk-2keys]

When updating from 3.1.6 to 3.2.0 I've noticed a behaviour change in the following scenario:

@Table(name = "root", schema = "a_schema")
record RootEntity(
  @Id
  @Column("id")
  String idColumn,
  @Nullable
  @Embedded.Nullable(prefix = "embedded_")
  EmbeddedEntity embeddedEntity
) {}

record EmbeddedEntity(
  @MappedCollection(idColumn = "root_id", keyColumn = "mapped_index")
  List<MappedEntity> mappedEntities
) {}

@Table(name = "mapped", schema = "a_schema")
record MappedEntity(
  @Column("mapped_column")
  String mappedColumn
) {}

@Transactional(readOnly = true)
interface RootEntityRepository extends CrudRepository<RootEntity, String> {}

When using RootEntityRepository.save(RootEntty) with 3.1.6 and 3.2.0 it works as expected, and when using RootEntityRepository.findById(String) with 3.1.6 it works as expected. However, when using RootEntityRepository.findById(String) with 3.2.0 it encounters the following exception:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT "a_schema"."mapped"."mapped_column" AS "mapped_column", "a_schema"."mapped"."mapped_index" AS "mapped_index" FROM "a_schema"."mapped" WHERE "a_schema"."mapped"."id" = ? ORDER BY "mapped_index"]

	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:112)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:107)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:116)
	at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1548)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:677)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:723)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:748)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:804)
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:218)
	at org.springframework.data.jdbc.core.convert.DefaultDataAccessStrategy.findAllByPath(DefaultDataAccessStrategy.java:306)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:352)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:242)
	at jdk.proxy2/jdk.proxy2.$Proxy123.findAllByPath(Unknown Source)
	at org.springframework.data.jdbc.core.convert.MappingJdbcConverter$ResolvingRelationalPropertyValueProvider.getPropertyValue(MappingJdbcConverter.java:379)
	at org.springframework.data.jdbc.core.convert.MappingJdbcConverter$ResolvingRelationalPropertyValueProvider.getPropertyValue(MappingJdbcConverter.java:310)
	at org.springframework.data.relational.core.conversion.MappingRelationalConverter$2.getPropertyValue(MappingRelationalConverter.java:494)
	at org.springframework.data.relational.core.conversion.MappingRelationalConverter$2.getPropertyValue(MappingRelationalConverter.java:475)
	at org.springframework.data.mapping.model.PersistentEntityParameterValueProvider.getParameterValue(PersistentEntityParameterValueProvider.java:71)
	at org.springframework.data.relational.core.conversion.MappingRelationalConverter$ConvertingParameterValueProvider.getParameterValue(MappingRelationalConverter.java:1161)
	at org.springframework.data.mapping.model.SpELExpressionParameterValueProvider.getParameterValue(SpELExpressionParameterValueProvider.java:49)
	at org.springframework.data.mapping.model.ClassGeneratingEntityInstantiator.extractInvocationArguments(ClassGeneratingEntityInstantiator.java:301)
	at org.springframework.data.mapping.model.ClassGeneratingEntityInstantiator$EntityInstantiatorAdapter.createInstance(ClassGeneratingEntityInstantiator.java:273)
	at org.springframework.data.mapping.model.ClassGeneratingEntityInstantiator.createInstance(ClassGeneratingEntityInstantiator.java:98)
	at org.springframework.data.relational.core.conversion.MappingRelationalConverter.read(MappingRelationalConverter.java:454)
	at org.springframework.data.relational.core.conversion.MappingRelationalConverter.readEmbedded(MappingRelationalConverter.java:566)
	at org.springframework.data.relational.core.conversion.MappingRelationalConverter$2.getPropertyValue(MappingRelationalConverter.java:490)
	at org.springframework.data.relational.core.conversion.MappingRelationalConverter$2.getPropertyValue(MappingRelationalConverter.java:475)
	at org.springframework.data.mapping.model.PersistentEntityParameterValueProvider.getParameterValue(PersistentEntityParameterValueProvider.java:71)
	at org.springframework.data.relational.core.conversion.MappingRelationalConverter$ConvertingParameterValueProvider.getParameterValue(MappingRelationalConverter.java:1161)
	at org.springframework.data.mapping.model.SpELExpressionParameterValueProvider.getParameterValue(SpELExpressionParameterValueProvider.java:49)
	at org.springframework.data.mapping.model.ClassGeneratingEntityInstantiator.extractInvocationArguments(ClassGeneratingEntityInstantiator.java:301)
	at org.springframework.data.mapping.model.ClassGeneratingEntityInstantiator$EntityInstantiatorAdapter.createInstance(ClassGeneratingEntityInstantiator.java:273)
	at org.springframework.data.mapping.model.ClassGeneratingEntityInstantiator.createInstance(ClassGeneratingEntityInstantiator.java:98)
	at org.springframework.data.relational.core.conversion.MappingRelationalConverter.read(MappingRelationalConverter.java:454)
	at org.springframework.data.relational.core.conversion.MappingRelationalConverter.readAggregate(MappingRelationalConverter.java:348)
	at org.springframework.data.relational.core.conversion.MappingRelationalConverter.readAggregate(MappingRelationalConverter.java:311)
	at org.springframework.data.jdbc.core.convert.MappingJdbcConverter.readAndResolve(MappingJdbcConverter.java:287)
	at org.springframework.data.jdbc.core.convert.JdbcConverter.readAndResolve(JdbcConverter.java:106)
	at org.springframework.data.jdbc.core.convert.EntityRowMapper.mapRow(EntityRowMapper.java:82)
	at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:94)
	at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:61)
	at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:733)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:658)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:723)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:748)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:804)
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForObject(NamedParameterJdbcTemplate.java:252)
	at org.springframework.data.jdbc.core.convert.DefaultDataAccessStrategy.findById(DefaultDataAccessStrategy.java:268)
	at org.springframework.data.jdbc.core.JdbcAggregateTemplate.findById(JdbcAggregateTemplate.java:290)
	at org.springframework.data.jdbc.repository.support.SimpleJdbcRepository.findById(SimpleJdbcRepository.java:79)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:352)
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker$RepositoryFragmentMethodInvoker.lambda$new$0(RepositoryMethodInvoker.java:277)
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:170)
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:158)
	at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:516)
	at org.springframework.data.repository.core.support.RepositoryComposition.invoke(RepositoryComposition.java:285)
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:628)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:168)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:143)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:385)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:249)
	at jdk.proxy2/jdk.proxy2.$Proxy130.findById(Unknown Source)
	at SpringDataJdbcTest.test(SpringDataJdbcTest.java:70)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
Caused by: org.postgresql.util.PSQLException: ERROR: column "a_schema.mapped.id" does not exist
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
	at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:134)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
	at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:732)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:658)
	... 78 more

I would expect the query to be

SELECT "a_schema"."mapped"."mapped_column" AS "mapped_column", 
    "a_schema"."mapped"."mapped_index" AS "mapped_index" 
FROM "a_schema"."mapped" 
WHERE "a_schema"."mapped"."root_id" = ? ORDER BY "mapped_index"

since idColumn = "root_id" is used in the @MappedCollection.

I believe I've narrowed down the reason for this to this condition in MappingJdbcConverter since the property owner appears to be the EmbeddedEntity which doesn't have its own ID.

Note: This occurred with the org.postgresql:postgresql:42.6.0 driver and CockroachDB v23.1.2

[ldap4life]

Hi I think I have a similar use case and since upgrading it seems im encountering the same thing.

[asmyers]

I'm also encountering this when trying to update, using the H2 driver in test.

[bluejays1]

Hi, I'm also running into the same issue when trying to upgrade.

[WeisSeb]

I'm also encountering this issue in 3.2.0 and 3.2.1, using H2 and Postgres

[charliemidtlyng]

Also having the same issue. Any updates here?

[pganster]

We are having the same issue with the following relationships (with a 1:1 relationship from A to B):

// Entities
AEntity(id: number, bEntity: BEntity, ...) 
BEntity(cSet: Set<CEntity>, dSet: Set<DEntity>, ...)
CEntity(...)
DEntity(...)

// Tables
a(id, ...)
b(a_id, ...)
c(id, a_id, ...)
d(id, a_id, ...)

When I'm now fetching a list of AEntity, the cSet and dSet of the bEntity are empty, although the database is populated. After debugging the MappingJdbcConverter, I think the line @hk-2keys linked is the same culprit for our problem, as the property owner of CEntity is BEntity, which doesn't have an ID.

This problem also started when upgrading to 3.2.x from 3.1.x.

[charliemidtlyng]

This is a real issue for us - any ETA here?

[juliojgd]

Hi @schauder any estimation about the resolution of this?

[mvpcortes]

Hi,

I found this error in 3.2.3 version.

[charliemidtlyng]

There are multiple issues with the same root cause here - will this ever been looked into?
Right now Spring Data JDBC only support collections on ONE level object A->B[], and not nested with two or more levelsA->B->C[]

It is pretty common to have multiple levels of objects with subsequent collections. I would call this a major issue for the framework and a "must have" feature to use in production.

Is anyone working on it, or should this be listed as a bug that won't be fixed?

#1748
#1734
#1739
#1748
#1692

[schauder]

Yes, this will be looked into.
Thanks for collecting all the related issues.

[charliemidtlyng]

Similar issue: #1771