Atlassian uses cookies to improve your browsing experience, perform analytics and research, and conduct advertising. Accept all cookies to indicate that you agree to our use of cookies on your device. Atlassian cookies and tracking notice, (opens new window)
Invalid SQL generated for mutation when using association with @NotFound
Description
Encountered an issue with many-to-one associations and @NotFound when migrating to Spring Boot 3. Incomplete SQL is generated for update and delete statements.
For example, having two entities, user:
@Entity
@Table(name = "users")
public class User {
@Id
@GeneratedValue
public Long id;
public String name;
public User(String name) {
this.name = name;
}
}
And comments:
@Entity
@Table(name = "comments")
public class Comment {
@Id
@GeneratedValue
public Long id;
public String text;
@ManyToOne
@JoinColumn(name = "user_id")
@NotFound(action = NotFoundAction.IGNORE) // <-- issue appears when @NotFound is added
public User user;
public Comment(String text, User user) {
this.text = text;
this.user = user;
}
}
And following HQL/JPQL statements:
SELECT c FROM Comment c WHERE c.user = :user
UPDATE Comment c SET c.text = :text WHERE c.user = :user
DELETE FROM Comment c WHERE c.user = :user
Selects are working and generate correct SQL - select ... from comments c1_0 left join users u1_0 on u1_0.id=c1_0.user_id where u1_0.id=?. However update and delete statements are incomplete (the join part is not included), resulting in invalid SQL - delete from comments where u1_0.id=?.
When @NotFound is removed from the association then all examples work, the SQL is generated same way as in Hibernate 5, without join - delete from comments where user_id=?.
Encountered an issue with many-to-one associations and
@NotFound
when migrating to Spring Boot 3. Incomplete SQL is generated for update and delete statements.For example, having two entities, user:
@Entity @Table(name = "users") public class User { @Id @GeneratedValue public Long id; public String name; public User(String name) { this.name = name; } }
And comments:
@Entity @Table(name = "comments") public class Comment { @Id @GeneratedValue public Long id; public String text; @ManyToOne @JoinColumn(name = "user_id") @NotFound(action = NotFoundAction.IGNORE) // <-- issue appears when @NotFound is added public User user; public Comment(String text, User user) { this.text = text; this.user = user; } }
And following HQL/JPQL statements:
SELECT c FROM Comment c WHERE c.user = :user
UPDATE Comment c SET c.text = :text WHERE c.user = :user
DELETE FROM Comment c WHERE c.user = :user
Selects are working and generate correct SQL -
select ... from comments c1_0 left join users u1_0 on u1_0.id=c1_0.user_id where u1_0.id=?
. However update and delete statements are incomplete (the join part is not included), resulting in invalid SQL -delete from comments where u1_0.id=?
.When
@NotFound
is removed from the association then all examples work, the SQL is generated same way as in Hibernate 5, without join -delete from comments where user_id=?
.Test case reproducing these examples - https://github.com/aivish/hibernate-test-case-association-comparisons/tree/main, it contains tests for Hibernate 5 and 6, the update/delete ones are failing in Hibernate 6, but working in 5.
I see that there are similar issues:
https://hibernate.atlassian.net/browse/HHH-16518
https://hibernate.atlassian.net/browse/HHH-16615
https://hibernate.atlassian.net/browse/HHH-15593
Seems like this provides a bit different use case and simpler test case how to reproduce the issue, so decided to create a separate issue.