Eclipse Community Forums: EclipseLink » Why join on pk for IN SELECT?

Help

Home

Home » Eclipse Projects » EclipseLink » Why join on pk for IN SELECT?

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

Why join on pk for IN SELECT? [message #535378]

Sat, 22 May 2010 22:12

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.

Report message to a moderator

Re: Why join on pk for IN SELECT? [message #535613 is a reply to message #535378]

Mon, 24 May 2010 21:38

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

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 14:20:54 GMT 2024

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter