| Why join on pk for IN SELECT? [message #535378] |
Sat, 22 May 2010 18:12  |
Eclipse User |
|
|
|
Hi I have a jpql query passed to eclipselink ....
SELECT COUNT(DISTINCT y.issue.issueId) AS XY FROM NumericField y WHERE y.issueTypeField.issueTypeFieldId = 4 AND y.numValue = 20 AND y.issue.issueId IN (SELECT z.issue.issueId FROM NumericField z WHERE z.issueTypeField.issueTypeFieldId = 5 AND y.numValue = 30)
Which produces the following SQL for Apache Derby (10.5.3.0)
SELECT COUNT(DISTINCT(t0.ISSUEID)) FROM ISSUE t0, ISSUETYPEFIELD t2, FIELD t1 WHERE (((((t2.ISSUETYPEFIELDID = 4) AND (t1.NUMVALUE = 20)) AND t0.ISSUEID IN (SELECT t3.ISSUEID FROM FIELD t6, ISSUETYPEFIELD t5, FIELD t4, ISSUE t3 WHERE (((((t5.ISSUETYPEFIELDID = 5) AND (t6.NUMVALUE = 30)) AND (t1.FIELDID = t6.FIELDID)) AND (t4.DCOL = 1)) AND (((t5.ISSUETYPEFIELDID = t4.ISSUETYPEFIELDID) AND (t6.DCOL = 1)) AND (t3.ISSUEID = t4.ISSUEID))))) AND (t1.DCOL = 1)) AND ((t2.ISSUETYPEFIELDID = t1.ISSUETYPEFIELDID) AND (t0.ISSUEID = t1.ISSUEID)))
What I don't understand is that there is a join (t1.FIELDID = t6.FIELDID) caused by the IN SELECT. t1 and t6 are the same FIELD table (NumericField).
As FIELDID is a primary key it will always be unique, so I am always getting 0 back from this query.
If I remove (t1.FIELDID = t6.FIELDID) from the sql and run it directly against Derby DB, I get the correct result.
Could anybody hint as to why this join is happening and especially suggest how I prevent it.
Cheers.
Phil.
|
|
|
|
Powered by
FUDForum. Page generated in 1.25451 seconds
Home
