Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » JPA: subquery SQL generation bug(Bug with queries like SELECT ... WHERE e NOT IN(subquery))
JPA: subquery SQL generation bug [message #1131452] Thu, 10 October 2013 14:39
Anton Kuranov is currently offline Anton KuranovFriend
Messages: 1
Registered: October 2013
Junior Member
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
Previous Topic:How to create POJOs with JAXB and JPA Annotations from XSD?
Next Topic:Hung threads
Goto Forum:
  


Current Time: Fri Nov 21 19:15:37 GMT 2014

Powered by FUDForum. Page generated in 0.03663 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software