DataNucleus JIRA is now in read-only mode. Raise any new issues in GitHub against the plugin that it applies to. DataNucleus JIRA will remain for the foreseeable future but will eventually be discontinued
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.


Areg Beketovski added a comment - 20/May/10 02:14 PM
Please see the testcase reproducing the issue attached.

Areg Beketovski made changes - 20/May/10 02:14 PM
Field Original Value New Value
Attachment testcase.zip [ 11176 ]
Andy Jefferson made changes - 20/May/10 02:32 PM
Priority Major [ 3 ] Minor [ 4 ]
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.

Areg Beketovski made changes - 21/May/10 02:43 PM
Attachment CollectionContainsMethod.patch [ 11178 ]
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 made changes - 21/May/10 06:18 PM
Attachment CollectionContainsMethod.patch [ 11178 ]
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.

Areg Beketovski made changes - 21/May/10 06:46 PM
Attachment CollectionContainsMethod.patch [ 11179 ]
Attachment QueryToSQLMapper.patch [ 11180 ]
Areg Beketovski made changes - 21/May/10 07:31 PM
Attachment QueryToSQLMapper.patch [ 11180 ]
Areg Beketovski made changes - 21/May/10 08:41 PM
Attachment QueryToSQLMapper.patch [ 11181 ]
Andy Jefferson made changes - 26/May/10 07:48 PM
Fix Version/s 2.2.0.release [ 10932 ]
Areg Beketovski made changes - 17/Jun/10 02:46 PM
Attachment QueryToSQLMapper.patch [ 11181 ]
Areg Beketovski made changes - 17/Jun/10 02:46 PM
Attachment CollectionContainsMethod.patch [ 11179 ]
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

Andy Jefferson made changes - 16/Nov/10 11:33 AM
Status Open [ 1 ] Resolved [ 5 ]
Assignee Andy Jefferson [ andy ]
Resolution Fixed [ 1 ]
Andy Jefferson made changes - 10/Dec/10 07:49 AM
Status Resolved [ 5 ] Closed [ 6 ]