Eclipse Community Forums: EclipseLink » JPQL queries with IN operator and sub queries may be converted to incorrect SQL?

Home » Eclipse Projects » EclipseLink » JPQL queries with IN operator and sub queries may be converted to incorrect SQL?

Show: Today's Messages :: Show Polls :: Message Navigator

JPQL queries with IN operator and sub queries may be converted to incorrect SQL? [message #492708]

Wed, 21 October 2009 09:28

Torben Putkonen
Messages: 34
Registered: July 2009

Member

We have experienced strange behavior when using IN operator and sub queries in JPQL.

For example, JPQL query:

DELETE FROM Table1 o WHERE o.id.field2 IN (SELECT i.field2 FROM Table2 i WHERE i.period = :PERIOD)

Is converted to the following SQL:

DELETE FROM TABLE1 WHERE EXISTS(SELECT t0.FIELD1 FROM TABLE1 t0 WHERE t0.FIELD2 IN (SELECT t1.FIELD2 FROM TABLE2 t2, TABLE2 t1 WHERE (t2.PERIOD = ?)) AND t0.FIELD1 = TABLE1.FIELD1 AND t0.FIELD2 = TABLE1.FIELD2 AND t0.FIELD3 = TABLE1.FIELD3 AND t0.FIELD4 = TABLE1.FIELD4)

The resulting query is complex and does not seem to work as it should.

We have experienced issues also with other queries containing IN operator, and we are now implementing all such queries using native queries. However I am curious to know if this behavior is known. Often the queries generated from the JPQL have been so complex that it has been impossible to find out why they are not working as intended.

Entities look like this:

TABLE1:

@EmbeddedId
private Table1PK id;
... other fields

Table1PK:
private String field1;
private String field2;
private String field3;
private String field4;

TABLE2:

private String field1;
@id
private String field2;
private String field3;
private String field4;
private String period;
... other fields

I have obfuscated manually the field names so I apologize for any typos.

Report message to a moderator

[Message index]

		JPQL queries with IN operator and sub queries may be converted to incorrect SQL? By: Torben Putkonen on Wed, 21 October 2009 09:28
		Re: JPQL queries with IN operator and sub queries may be converted to incorrect SQL? By: James Sutherland on Wed, 21 October 2009 13:12

Previous Topic:	Using Eclipselink in a RIA; by passing session beans
Next Topic:	JPA and JAXB Annotated POJOs

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 25 02:40:26 EDT 2013