search

spring-projects / spring-data-relational

Spring Data Relational. Home of Spring Data JDBC and Spring Data R2DBC.
https://spring.io/projects/spring-data-jdbc
Apache License 2.0
737 stars 339 forks source link

Duplicate key column in selection #1779

Closed mivanus closed 1 month ago

mivanus commented 2 months ago

In some cases select statement contains duplicate key columns which causes BadSqlGrammarException when "order by" gets included in sql.

Code that causes duplication: https://github.com/spring-projects/spring-data-relational/blob/21e8d9a4fac7203f0f4d9a331b43e758ac47052c/spring-data-jdbc/src/main/java/org/springframework/data/jdbc/core/convert/SqlGenerator.java#L537-L539

schauder commented 2 months ago

Please provide a Minimimal Reproducable Example, preferable as a Github repository. Make sure to include the database, either as an in memory database or if that is not possible using Testcontainers.

mivanus commented 2 months ago

I couldn't reproduce issue with H2 so i had to use Testcontainers+db2.

https://github.com/mivanus/spring-data-retional-issue-1779

schauder commented 2 months ago

This might be related to #1680

I was able to reproduce this with DB2. Other databases don't throw an exceptions but still produce a select statement containing the same column twice in the select list.

For example: 

SELECT 
  "BBB"."ID" AS "ID", 
  "BBB"."NAME" AS "NAME", 
  "BBB"."AAA_ID" AS "AAA_ID", 
  "ccc"."ID" AS "ccc_ID", 
  "ccc"."NAME" AS "ccc_NAME", 
  "ccc"."BBB_ID" AS "ccc_BBB_ID", 
  "BBB"."ID" AS "ID" 
FROM "BBB" LEFT OUTER JOIN "CCC" "ccc" ON "ccc"."BBB_ID" = "BBB"."ID" WHERE "BBB"."AAA_ID" = ? ORDER BY "ID"
schauder commented 2 months ago

After some more investigation, there are a couple of things wrong with the mapping.

The relation between Aaa and Bbb is one internal to the aggregate of Aaa, but you use also AggregateTemplate.insert(bbb) which only works for aggregate roots. This does not work. See https://spring.io/blog/2018/09/24/spring-data-jdbc-references-and-aggregates

Also if Bbb is part of the Aaa aggregate, it needs a proper key-column holding the index of the Aaa.bbbs list. This is currently set to the "ID" which is the primary key of Bbb. Again, this does not work.

If you have further questions about fixing the mapping I recommend asking on SO. With the spring-data-jdbc tag, I will see and probably answer it.

If you think there is still an error with the SQL generation, please provide an updated reproducer. Otherwise this issue will be closed soon.

spring-projects-issues commented 1 month ago

If you would like us to look at this issue, please provide the requested information. If the information is not provided within the next 7 days this issue will be closed.

spring-projects-issues commented 1 month ago

Closing due to lack of requested feedback. If you would like us to look at this issue, please provide the requested information and we will re-open the issue.