Issue Details (XML | Word | Printable)

Key: NUCRDBMS-398
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Minor Minor
Assignee: Andy Jefferson
Reporter: Areg Beketovski
Votes: 0
Watchers: 1
Operations

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

Many-To-One Bidirectional mapping generates unnecessary JOIN for the unbound variable

Created: 20/May/10 02:13 PM   Updated: 10/Dec/10 07:49 AM   Resolved: 16/Nov/10 11:33 AM
Component/s: Queries
Affects Version/s: 2.1.0.m3
Fix Version/s: 2.2.0.release

File Attachments: 1. Zip Archive testcase.zip (4 kB)


Datastore: Microsoft SQL Server


 Description  « Hide
Consider the following hierarchy of 5 classes and with "datanucleus.defaultInheritanceStrategy=TABLE_PER_CLASS", @Inheritance(strategy = InheritanceStrategy.SUPERCLASS_TABLE).

* Base
* BaseA extends Base, with field "@Persistent(mappedBy = "buyer") private Set<BaseB> buyers = new HashSet<BaseB>();"
* BaseB extends Base, with fiels "private BaseA buyer;"
* A extends BaseA
* B extends BaseB

The following JDOQL query:

"SELECT count(this) FROM org.datanucleus.test.A WHERE this == prod.buyer VARIABLES org.datanucleus.test.B prod"

results in:

"SELECT COUNT(`A0`.`ID`) FROM `BASEA` `A0` CROSS JOIN `BASEB` `VAR_PROD` LEFT OUTER JOIN `BASEA` `C0` ON `VAR_PROD`.`BUYER_ID_OID` = `C0`.`ID` WHERE `A0`.`DISCRIMINATOR` = 'org.datanucleus.test.A' AND `A0`.`ID` = `C0`.`ID`"

The second "LEFT OUTER JOIN" seems unnecessary and can cause performance problems if "BASEA" is a big table, which can be the case if "@Inheritance(strategy = InheritanceStrategy.SUPERCLASS_TABLE)" is used.


Sort Order: Ascending order - Click to sort in descending order
Areg Beketovski added a comment - 20/May/10 02:14 PM
Please see the testcase reproducing the issue attached.

Areg Beketovski added a comment - 20/May/10 08:04 PM
Same happens for query with bound variable:

SELECT count(this) FROM org.datanucleus.test.A WHERE this.buyers.contains(prod) VARIABLES org.datanucleus.test.B prod

it is translated into:

SELECT COUNT(`A0`.`ID`) FROM `BASEA` `A0` INNER JOIN `BASEB` `B0` ON `A0`.`ID` = `B0`.`BUYER_ID_OID` INNER JOIN `BASEB` `C0` ON `B0`.`ID` = `C0`.`ID` WHERE `A0`.`DISCRIMINATOR` = 'org.datanucleus.test.A'

Areg Beketovski added a comment - 21/May/10 02:43 PM - edited
Please see attached a patch which should prevent the double inner join for Collection.contains() case.

Andy Jefferson added a comment - 21/May/10 05:56 PM
The problem is unlikely to be one of same table. It is actually more generic of VariableExpression, ParameterExpression, PrimaryExpression and when deciding to join to the other side of a relation there is currently no check across the query expression trees of whether there is a reference to a field in the other table. That is the optimisation needed rather than some specific contains() code

Areg Beketovski added a comment - 21/May/10 06:46 PM - edited
Please disregard the patches (I deleted them), incorrect SQL is produced with them.

Andy Jefferson added a comment - 16/Nov/10 11:33 AM
SVN trunk no longer adds the extra join on N-1s when part of a comparison operation and not needing join further