[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-7072) ElementCollection not updated correctly if the Embeddable component has a nullable property

[Martin Berglund (JIRA)]

    [ https://hibernate.onjira.com/browse/HHH-7072?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=46155#comment-46155 ] 

Martin Berglund commented on HHH-7072:
--------------------------------------

I'm having the exact same problem. The combination here seems to be @ElementCollection + @Embeddable + java.util.Set + null + MySQL. If the collection type is changed to List, it works fine.

I created a very simple test-case for this, creating a Parent @Entity and a Child @Embeddable:
{code:java}
@Embeddable
public class Child {
    private String value1;
    private String value2;

    public Child() {}
    public String getValue1() { return value1; }
    public void setValue1(String value1) { this.value1 = value1; }
    public String getValue2() { return value2; }
    public void setValue2(String value2) { this.value2 = value2; }
}
{code}

{code:java}
@Table(name="Parent")
@Entity
public class Parent {
    private int id;
    private Set<Child> children;

    public Parent() {}

    @Id
    public int getId() { return id; }
    public void setId(int id) { this.id = id; }
   
    @ElementCollection
    @CollectionTable(name="Children", joinColumns=@JoinColumn(name="parentId", referencedColumnName="id"))
    public Set<Child> getChildren() { return children; }
    public void setChildren(Set<Child> children) { this.children = children; }
}
{code}

If I create one Parent object, attach one Child object and call merge (I'm using the JPA interface), I get this SQL generated by Hibernate:
{noformat}
Hibernate: select parent0_.id as id0_0_ from Parent parent0_ where parent0_.id=?
Hibernate: insert into Parent (id) values (?)
Hibernate: insert into Children (parentId, value1, value2) values (?, ?, ?)
Hibernate: delete from Children where parentId=? and value1=? and value2=?
Hibernate: insert into Children (parentId, value1, value2) values (?, ?, ?)
{noformat}
Not sure why it's inserting the same row twice, but anyhow, as the reporter is writing, MySQL doesn't support "WHERE column = null". The correct syntax would be "WHERE column IS NULL" or limit the delete query to only the foreign key ({{parentId}} above) (or "WHERE column <=> null"). 

Indeed, if I change the {{Set}} to {{List}}, I get this instead:
{noformat}
Hibernate: select parent0_.id as id0_0_ from Parent parent0_ where parent0_.id=?
Hibernate: insert into Parent (id) values (?)
Hibernate: insert into Children (parentId, value1, value2) values (?, ?, ?)
{noformat}
Then, if I modify the child object in Java and merge it again:
{noformat}
Hibernate: select parent0_.id as id0_0_ from Parent parent0_ where parent0_.id=?
Hibernate: select children0_.parentId as parentId0_0_, children0_.value1 as value2_0_, children0_.value2 as value3_0_ from Children children0_ where children0_.parentId=?
Hibernate: delete from Children where parentId=?
Hibernate: insert into Children (parentId, value1, value2) values (?, ?, ?)
{noformat}
Trying to modify and merge with the {{Set}} will cause the rows to multiply, since it's trying to delete each row one by one, and each delete fails, then insert all the rows again.

> ElementCollection not updated correctly if the Embeddable component has a nullable property
> -------------------------------------------------------------------------------------------
>
>                 Key: HHH-7072
>                 URL: https://hibernate.onjira.com/browse/HHH-7072
>             Project: Hibernate ORM
>          Issue Type: Bug
>          Components: annotations
>    Affects Versions: 3.5.2
>            Reporter: Aaron Trewern
>
> I have an @ElementCollection that is a collection of @Embeddable components. When saving an updated entity I can see that Hibernate issues an SQL DELETE for each of the rows in the collection followed by an INSERT, as is expected behaviour for an @ElementCollection.
> The rows are deleted using a where clause that includes all the properties of the @Embeddabkle component. With MySQL the DELETE fails to delete any rows where the property is a nullable property and the previous value was null.
> This is because Hibernate issues a prepared SQL ststement like "DELETE FROM tableName t where t.a = ? and t.b = ?" now if b is a nullable property and the component being saved has a null value for b then the delete will fail.
> To correctly delete the row with MySQL the statement should be "DELETE FROM tableName t where t.a = ? and t.b is null"

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira