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)

Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Minor Minor
Assignee: Andy Jefferson
Reporter: Areg Beketovski
Votes: 0
Watchers: 1

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 (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:


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