Hi,
I'm asking here before submit a bug to EclipseLink bugtracker.
I have a simple entity with a compound PK.
@Entity public class Entity1 {
@EmbeddedId public PK id;
public String data;
}
@Embeddable public class PK implements Serializable {
public String field1;
public String field2;
@Override public boolean equals(Object obj) {
if ( !(obj instanceof PK) ) return false;
PK o = (PK)obj;
return field1==null?o.field1==null:field1.equals(o.field1)
&&field2==null?o.field2==null:field2.equals(o.field2);
}
@Override public int hashCode() {
return (""+field1+field2).hashCode();
}
}
This query generates a wrong SQL (I know it can be optimized but the real case query is much more complex):
em.createQuery( "SELECT e1 FROM Entity1 e1 WHERE e1 NOT IN ("
+ "SELECT e1 FROM Entity1 e1 WHERE "
+ "e1.data='qqq')" ).getResultList();
SELECT t0.DATA, t0.FIELD2, t0.FIELD1 FROM ENTITY1 t0 WHERE EXISTS (
SELECT 1 FROM ENTITY1 t1 WHERE
(NOT (((t0.FIELD2 = t1.FIELD2) AND (t0.FIELD1 = t1.FIELD1))) AND (t1.DATA = qqq))
)
As we can see the NOT operator in generated subquery is applied to PK join clause but not to filter clause as it must be: NOT (t1.DATA = qqq). Thus the query returns wrong result.
Workaround. If we modify query like this it works well and returns right resultset:
em.createQuery( "SELECT e1 FROM Entity1 e1 WHERE NOT (e1 IN ("
+ "SELECT e1 FROM Entity1 e1 WHERE "
+ "e1.data='qqq'))" ).getResultList();
SELECT t0.DATA, t0.FIELD2, t0.FIELD1 FROM ENTITY1 t0 WHERE NOT (EXISTS (SELECT 1 FROM ENTITY1 t1 WHERE (((t0.FIELD2 = t1.FIELD2) AND (t0.FIELD1 = t1.FIELD1)) AND (t1.DATA = qqq))) )
EclipseLink: 2.5.1, H2 1.3.173, Oracle 12c.
P.S. When the PK is not compound (synthetic) a query works well with H2 but still has the same problem with Oracle.
Antón
] 
Search
Help
Register
Login
Home
