Duplicate key column in selection

[mivanus]

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

Code that causes duplication:

spring-data-relational/spring-data-jdbc/src/main/java/org/springframework/data/jdbc/core/convert/SqlGenerator.java

Lines 537 to 539 in 21e8d9a

	for (SqlIdentifier keyColumn : keyColumns) {
	columnExpressions.add(table.column(keyColumn).as(keyColumn));
	}

[schauder]

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]

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

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

[schauder]

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]

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]

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]

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.