Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » JPQL queries with IN operator and sub queries may be converted to incorrect SQL?
JPQL queries with IN operator and sub queries may be converted to incorrect SQL? [message #492708] Wed, 21 October 2009 09:28 Go to next message
Torben Putkonen is currently offline 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.
Re: JPQL queries with IN operator and sub queries may be converted to incorrect SQL? [message #492781 is a reply to message #492708] Wed, 21 October 2009 13:12 Go to previous message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

What is the issue with the generated SQL, I assume the extra TABLE2 join, "TABLE2 t2, TABLE2 t1"?

Assuming your model is a simple as you show, this would seem to be a bug. Please verify the issue on the latest release and log the bug.



James : Wiki : Book : Blog : Twitter
Previous Topic:Using Eclipselink in a RIA; by passing session beans
Next Topic:JPA and JAXB Annotated POJOs
Goto Forum:
  


Current Time: Sun Apr 20 23:04:16 EDT 2014

Powered by FUDForum. Page generated in 0.01471 seconds