Issue Details (XML | Word | Printable)

Key: NUCRDBMS-321
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Unassigned
Reporter: Andy Jefferson
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
DataNucleus Store RDBMS

JDOQL2 : Queries with variables with badly ordered clauses can result in CROSS JOIN rather than INNER JOIN

Created: 03/Mar/10 10:37 AM   Updated: 26/Apr/10 03:38 PM   Resolved: 13/Apr/10 02:34 PM
Component/s: Queries
Affects Version/s: None
Fix Version/s: 2.1.0.m2


 Description  « Hide
LOok at JDOQLContainerTest (application identity) and the test
testQueryUsesContainsSetRangeVariableAlreadyInQuery

This has a JDOQL query
SELECT FROM org.jpox.samples.models.nightlabs_payments.ServerPaymentProcessor
WHERE modeOfPaymentFlavour.organisationID == paramOrganisationID &&
  modeOfPaymentFlavour.modeOfPaymentFlavourID == paramModeOfPaymentFlavourID &&
  modeOfPayment == modeOfPaymentFlavour.modeOfPayment &&
  this.modeOfPaymentsSet.contains(modeOfPayment)
VARIABLES ModeOfPaymentFlavour modeOfPaymentFlavour; ModeOfPayment modeOfPayment
PARAMETERS java.lang.String paramOrganisationID, java.lang.String paramModeOfPaymentFlavourID
import org.jpox.samples.models.nightlabs_payments.ModeOfPaymentFlavour;
import org.jpox.samples.models.nightlabs_payments.ModeOfPayment

and creates SQL of
SELECT 'org.jpox.samples.models.nightlabs_payments.ServerPaymentProcessor' AS NUCLEUS_TYPE,A0.ORGANISATIONID,A0.SERVERPAYMENTPROCESSORID
FROM SERVERPAYMENTPROCESSOR A0
CROSS JOIN MODEOFPAYMENTFLAVOUR VAR_MODEOFPAYMENTFLAVOUR
CROSS JOIN MODEOFPAYMENT VAR_MODEOFPAYMENT
INNER JOIN SERVERPAYMENTPROCESSOR_MODEOFPAYMENTSSET E0 ON A0.ORGANISATIONID = E0.ORGANISATIONID_OID AND A0.SERVERPAYMENTPROCESSORID = E0.SERVERPAYMENTPROCESSORID_OID
INNER JOIN MODEOFPAYMENT F0 ON E0.MODEOFPAYMENTID_EID = F0.MODEOFPAYMENTID AND E0.ORGANISATIONID_EID = F0.ORGANISATIONID
WHERE F0.MODEOFPAYMENTID = VAR_MODEOFPAYMENT.MODEOFPAYMENTID
AND F0.ORGANISATIONID = VAR_MODEOFPAYMENT.ORGANISATIONID
AND VAR_MODEOFPAYMENTFLAVOUR.ORGANISATIONID = ?
AND VAR_MODEOFPAYMENTFLAVOUR.MODEOFPAYMENTFLAVOURID = ?
AND VAR_MODEOFPAYMENT.MODEOFPAYMENTID = VAR_MODEOFPAYMENT.MODEOFPAYMENTID
AND VAR_MODEOFPAYMENT.ORGANISATIONID = VAR_MODEOFPAYMENT.ORGANISATIONID

The result is incorrect, so likely related to type of join used.



For reference, "Legacy" JDOQL creates the following SQL

SELECT 'org.jpox.samples.models.nightlabs_payments.ServerPaymentProcessor' AS NUCLEUS_TYPE,THIS.ORGANISATIONID,THIS.SERVERPAYMENTPROCESSORID
FROM MODEOFPAYMENTFLAVOUR UNBOUND_MODEOFPAYMENTFLAVOUR
INNER JOIN MODEOFPAYMENT UNBOUND_MODEOFPAYMENT ON UNBOUND_MODEOFPAYMENT.MODEOFPAYMENTID = UNBOUND_MODEOFPAYMENTFLAVOUR.MODEOFPAYMENT_MODEOFPAYMENTID_OID AND UNBOUND_MODEOFPAYMENT.ORGANISATIONID = UNBOUND_MODEOFPAYMENTFLAVOUR.MODEOFPAYMENT_ORGANISATIONID_OID , SERVERPAYMENTPROCESSOR THIS , SERVERPAYMENTPROCESSOR_MODEOFPAYMENTSSET THIS_MODEOFPAYMENTSSET
WHERE THIS_MODEOFPAYMENTSSET.ORGANISATIONID_OID = THIS.ORGANISATIONID
AND THIS_MODEOFPAYMENTSSET.SERVERPAYMENTPROCESSORID_OID = THIS.SERVERPAYMENTPROCESSORID
AND UNBOUND_MODEOFPAYMENTFLAVOUR.ORGANISATIONID = <'orga00.jfire.org'>
AND UNBOUND_MODEOFPAYMENTFLAVOUR.MODEOFPAYMENTFLAVOURID = <'Dollar'>
AND UNBOUND_MODEOFPAYMENT.MODEOFPAYMENTID = UNBOUND_MODEOFPAYMENTFLAVOUR.MODEOFPAYMENT_MODEOFPAYMENTID_OID
AND UNBOUND_MODEOFPAYMENT.ORGANISATIONID = UNBOUND_MODEOFPAYMENTFLAVOUR.MODEOFPAYMENT_ORGANISATIONID_OID
AND UNBOUND_MODEOFPAYMENT.MODEOFPAYMENTID = THIS_MODEOFPAYMENTSSET.MODEOFPAYMENTID_EID
AND UNBOUND_MODEOFPAYMENT.ORGANISATIONID = THIS_MODEOFPAYMENTSSET.ORGANISATIONID_EID

Andy Jefferson added a comment - 22/Mar/10 11:45 AM
The real problem here is where the user hasn't defined their query filter components in a logical order to promote sensible binding of variables. There are two main areas

1. Where they introduce some variable, and then later on just happen to mention "collField.contains(var)". If they had put that first then it would have been bound using an INNER JOIN (or SUBQUERY) and so by the time it got to the other use of the variable it would have it bound. Instead it does CROSS JOIN for the first, and then just uses that in the contains().

2. Where they introduce some variable, and then later on just happen to mention some 1-1 clause with a field.

Andy Jefferson added a comment - 22/Mar/10 03:31 PM
The first part of this is in SVN trunk now. When a variable has been (prematurely) bound as CROSS JOIN and it encounters a Collection.contains(var) needing an INNER JOIN then it replaces the CROSS JOIN with this. Same for Map.containsKey, Map.containsValue. Doesn't do anything for the same case where a SUBQUERY is needed yet though. User could easily reorder their query components for that.

Andy Jefferson made changes - 22/Mar/10 03:32 PM
Field Original Value New Value
Summary JDOQL2 : Use of variables in 1-1 and 1-N relations creates incorrect query (JDOQLContainerTest 5 tests) JDOQL2 : Queries with variables with badly ordered clauses can result in CROSS JOIN rather than INNER JOIN
Andy Jefferson added a comment - 24/Mar/10 06:52 PM
All affected tests in JDOQLContainerTest now pass. Leaving this JIRA open for now since not done anything particular for the part 2 above. Close if no testcases appear within a month or so

Andy Jefferson made changes - 01/Apr/10 10:41 AM
Fix Version/s 2.1.0.m2 [ 10912 ]
Fix Version/s 2.1.0.release [ 10832 ]
Andy Jefferson added a comment - 13/Apr/10 02:34 PM
Marking as fixed since no outstanding issue of this nature. Raise a new JIRA if anything comes up

Andy Jefferson made changes - 13/Apr/10 02:34 PM
Status Open [ 1 ] Resolved [ 5 ]
Resolution Fixed [ 1 ]
Andy Jefferson made changes - 26/Apr/10 03:38 PM
Status Resolved [ 5 ] Closed [ 6 ]