RowDocumentResultSetExtractor : ResultSet contains column "id" multiple times

[fsudau]

Affects version 3.4.5

Hi,

I am experiencing this issue where the line RowDocumentResultSetExtractor : ResultSet contains column "id" multiple times. Later column index is 2 is logged on WARN every time I execute a (generated) statement. This could either be a bug or entirely my fault. In any case, I investigated this quiet a bit and could not fix it, so here we go:

I am using Postgres and created these 2 tables (forming a simple aggregate) using quotes and in all-lowercase. Thus, the identifiers are indeed stored as lower case in the db schema:

CREATE TABLE "users"
(
    "id"   UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    "name" TEXT NOT NULL
);

CREATE TABLE "user_items"
(
    "id"       UUID PRIMARY KEY,
    "code"     TEXT NOT NULL,
    "users_fk" UUID REFERENCES "users"
);

My "entities" are defined as follows:

@Table("user_items")
public class UserItem {

    @Id
    private UUID id;
    private String code;
}

@Table("users")
public class User {

    @Id
    private UUID id;
    private String name;
    @MappedCollection(idColumn = "users_fk", keyColumn = "id")
    private List<UserItem> items;

}

Queries on the User aggregate without any custom SQL, for example userRepository.findAll(), will then produce the warning mentioned above. Example project here.

Some Background

• The Postgres dialect is detected correctly.
• Fiddling around with NamingStrategy does not impact this; the NamingStrategy is applied later in the process.
• Capitalizing keyColumn will break the application entirely.

It seems to me that the fix from #1073 does not work here due to the odd setup in terms of casing. Although everything I wrote is in lower case, there is still some mixup happening. The problem is already apparent in SqlGenerator#selectBuilder(Collection<SqlIdentifier> keyColumns).
The key column name from my annotation is passed as a DefaultSqlIdentifier and the columns inferred from the field names as DerivedSqlIdentifier. DerivedSqlIdentifier then capitalizes the column name in toSql(IdentifierProcessing processing) although I am running a Postgres and DefaultSqlIdentifier does not capitalize.
The set logic in SqlGenerator will then not de-dupe these columns with different casing and in the end a query of the form SELECT "user_items"."id" AS "id", "user_items"."code" AS "code", "user_items"."id" AS "id" FROM "user_items" WHERE "user_items"."users_fk" = ('df07723f-6e8b-425c-a888-90c13c399799'::uuid) ORDER BY "id" is generated.
The sql "works" fine of course, making this a minor issue.

[schauder]

This is a mapping mistake. You specify @MappedCollection(..., keyColumn = "id").
This means, the index of the List is supposed to be stored in the column id of table user_items.
But that column is already mapped to UserItem.id.

To fix this, add another column to user_items, e.g. item_index and specify that as @MappedCollection.keyColumn.
The new column plus users_fk will be used as primary key for user_items.

Note: There is no need for an @Id annotated column in UserItem at least from Spring Data JDBC perspective, so you may drop that column, if it hasn't a business purpose.

Any problem with capitalization seems unrelated to this. If you still think there is an issue, please create a new issue and provide a reproducer.

[fsudau]

I misunderstood the feature completely. Works like a charm now, thanks a lot for clarifying!