micronaut-projects / micronaut-projects/micronaut-data

R2DBC with @EmbeddedId and @MapsId causes "column specified more than once" error in PostgreSQL

Open
#3,602 3 comments 0 reactions 0 assignees View on GitHub
type: bug
Dominant language
Java
Stars
482
Forks
229
Avg merge
1d 7h
Merged PRs (30d)
32

Description

### Problem Description

When using @EmbeddedId with @MapsId in R2DBC repositories with PostgreSQL, Micronaut Data generates SQL queries with duplicate column names, causing PostgresqlBadGrammarException: column "book_id" specified more than once.

### Expected Behavior

Micronaut Data should generate correct SQL without duplicate columns:
`INSERT INTO person (person_id, book_id, name) VALUES (?, ?, ?)`

### Actual Behaviour

Micronaut Data generates SQL with duplicate book_id column:

`INSERT INTO person (person_id, book_id, book_id, name) VALUES (?, ?, ?, ?)`

Resulting error:
io.r2dbc.postgresql.ExceptionFactory$PostgresqlBadGrammarException:
[42701] column "book_id" specified more than once

### Steps To Reproduce

Define entities with composite keys using @EmbeddedId():

```
// Composite key class
@Embeddable
@Introspected
public class PersonId implements Serializable {
@MappedProperty("person_id")
private Long personId;

@MappedProperty("book_id")
private Long bookId;
// constructors, getters, equals, hashCode
}

// Entity with @EmbeddedId and @MapsId
@MappedEntity("person")
public class Person {
@EmbeddedId
private PersonId id;

@Column("name")
private String name;

@Relation(value = Relation.Kind.MANY_TO_ONE)
@MapsId("bookId")
@JoinColumn(name = "book_id", referencedColumnName = "id")
private Book book;

// constructors, getters, setters
}

// Related entity
@MappedEntity("book")
public class Book {
@Id
@GeneratedValue
private Long bookId;

private String title;
// other fields
}
```

Run the test "IntegrationServiceTest" in project link below

### Environment Information

Micronaut Version: 4.9.3

R2DBC Driver: r2dbc-postgresql

Database: PostgreSQL

JDK Version: 21

### Example Application

https://github.com/alexmaximovna/books-authors/blob/main/src/test/java/com/books/authors/IntegrationServiceTest.java

### Version

4.9.3

Contributor guide

Open the contributing guide

Research direction

Start by running IntegrationServiceTest in the linked example application with PostgreSQL and inspect the generated INSERT for the person entity. Trace the handling of @EmbeddedId and @MapsId, then verify that the completed insert contains book_id only once and succeeds without the duplicate-column error.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, postgresql, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
42/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.