Hi,
I want to report a possible Hibernate bug which appears on queries like UPDATE AAA a SET ... WHERE a IN (SELECT b FROM AAA b JOIN b.xxx c WHERE ...). In this case SQL generated in subquery does not contain table qualifiers and database throws an SQL error: "Ambiguous column name "ID"; SQL statement:"
For example we have a Parent and a Child entities and 1-n relation between them implemented via intermediate table (meet-in-the-middle).
Code:
@Entity
public class Parent {
@Id public String id;
public String data;
@OneToMany
public List<Child> children;
}
@Entity
public class Child {
@Id public String id;
public String data;
}
And this update query throws the SQL exception:
Code:
em.createQuery( "UPDATE Parent u SET u.data='xxx' WHERE u IN( "
+ "SELECT p FROM Parent p JOIN p.children c WHERE "
+ "c.data='yyy' )" ).executeUpdate();
Ambiguous column name "ID"; SQL statement:
update parent set data='xxx'
where id in (select id from parent parent1_ inner join parent_children children2_ on parent1_.id=children2_.parent_id inner join child child3_ on children2_.children=child3_.id)
If we see the generated query there is no table qualifier in subquery:
"select id from ..." instead of "select parent1_.id from ..."
While both tables child and parent have the "id" field the database engine can not identify the entity it must return and throws error.
The same thing happens with DELETE statement. Curiously if we change the statement to SELECT the query works well and subquery generated with qualifiers.
Code:
em.createQuery( "SELECT u FROM Parent u WHERE u IN( "
+ "SELECT p FROM Parent p JOIN p.children c WHERE "
+ "c.data='yyy' )" ).getResultList();
select parent0_.id as id1149_, parent0_.data as data1149_ from parent parent0_ where parent0_.id in (select parent1_.id from parent parent1_ inner join parent_children children2_ on parent1_.id=children2_.parent inner join child child3_ on children2_.children=child3_.id where child3_.data='yyy')
Partial work-around: add PK column to subquery
Code:
em.createQuery( "UPDATE Parent u SET u.data='xxx' WHERE u IN( "
+ "SELECT p.id FROM Parent p, IN(p.children) c WHERE "
+ "c.data='yyy' )" ).executeUpdate();
But it fails on H2 database in case of compound primary key.
Tested with: Hibernate 4.2.2 over H2 1.3.161
Salu2,
Antón
Login
FAQ
Search