Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Why join on pk for IN SELECT?
Why join on pk for IN SELECT? [message #535378] Sat, 22 May 2010 22:12 Go to next message
Philip Wilkinson is currently offline Philip Wilkinson
Messages: 18
Registered: July 2009
Junior Member
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.

Re: Why join on pk for IN SELECT? [message #535613 is a reply to message #535378] Mon, 24 May 2010 21:38 Go to previous message
Philip Wilkinson is currently offline Philip Wilkinson
Messages: 18
Registered: July 2009
Junior Member
OK now I get it. A simple mistake on my part...

y.numValue = 30) should be z.numValue = 30)

So now I have..

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 z.numValue = 30)


producing this sql..

SELECT COUNT(DISTINCT(t0.ISSUEID)) FROM ISSUE t0, ISSUETYPEFIELD t2, FIELD t1 WHERE (((((t2.ISSUETYPEFIELDID = ?) AND (t1.NUMVALUE = ?)) AND t0.ISSUEID IN (SELECT t3.ISSUEID FROM ISSUETYPEFIELD t5, FIELD t4, ISSUE t3 WHERE ((((t5.ISSUETYPEFIELDID = ?) AND (t4.NUMVALUE = ?)) AND (t4.DCOL = ?)) AND ((t5.ISSUETYPEFIELDID = t4.ISSUETYPEFIELDID) AND (t3.ISSUEID = t4.ISSUEID))))) AND (t1.DCOL = ?)) AND ((t2.ISSUETYPEFIELDID = t1.ISSUETYPEFIELDID) AND (t0.ISSUEID = t1.ISSUEID)))


which is much better. Razz
Previous Topic:Problem with installing EclipseLink 2.0.2 on Glassfish 3.0
Next Topic:does not return a ValueHolderInterface, but the mapping uses indirection
Goto Forum:
  


Current Time: Thu Oct 23 18:49:38 GMT 2014

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

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