Invalid SQL generated when non-root entity in aggregate has a collection

[jamoamo]

If I have a nested entity aggregate where somewhere in the nest (not the root) there is a one-to-one relationship with a nested one-to-many relationship, the query from the one-to-many relationship entity will reference the incorrect column name. That sounds vague, even to me, so let me illustrate.

If I have a database structure such as below (imagine foreign keys from c -> b and b -> c):

CREATE TABLE a (a_id INT)
CREATE TABLE b (b_id INT, a INT)
CREATE TABLE c (c_id INT, b INT)

and entities for each table as follows (extraneous code excluded) where A contains a one-to-one relationship with B and B contains a one-to-many relationship with C.

public class C {
   @Id
   private int cId;
}

public class B {
   @Id
   private int bId;
   private Set<C> cChild;
}
public class A {
   @Id
   private int aId;
   private B bChild;
}

The above will result in an exception when querying the root of the aggregate using a repository bean along the lines of

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT "C"."C_ID" AS "C_ID" FROM "C" WHERE "C"."A_ID" = ?]

The generated SQL makes use of a column which does not exist in the table 'c' but only exists in table 'a'.

A workaround is to make the relationship between A and B to be a one-to-many relationship.

There is a minimalist project that reproduces the bug at https://github.com/jamoamo/spring-data-bug-reproducer

[ljpeters]

This looks similar to my reported issue https://github.com/spring-projects/spring-data-relational/issues/1739

It took some time for me to get redirected to spring-data-relational, so I'm just looking at already reported issues now.

[schauder]

There is a snapshot available that fixes https://github.com/spring-projects/spring-data-relational/issues/1692 3.3.0-1692-collection-in-embedded-SNAPSHOT And a PR to go with it: https://github.com/spring-projects/spring-data-relational/pull/1773

I assume this issue will be fixed by it as well. If nobody objects I'll close this issue as a duplicate once the PR is merged.

[schauder]

Duplicate of #1692