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: Major Major
Assignee: Andy Jefferson
Reporter: Richard DiCroce
Votes: 0
Watchers: 1

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

Improper SQL generated for contains() and remove() on a set of interface with a join relationship

Created: 10/Jul/11 10:26 PM   Updated: 03/Oct/11 07:38 AM   Resolved: 07/Sep/11 09:14 PM
Component/s: None
Affects Version/s: 3.0.0.m6
Fix Version/s: 3.0.2

File Attachments: 1. Zip Archive (4 kB)

Datastore: MySQL

 Description  « Hide
Take two classes, FooA and FooB, both of which implement IFoo and contain a Set<IFoo>. Now create an instance of FooA and an instance of FooB, then add the FooB to the FooA's set and persist it. Clear your caches (or close your program and reopen it), then query the datastore and find the instances of FooA and FooB you just persisted.

Get the FooA's set of IFoo and ask if it contains the instance of FooB. The set will report that it does NOT contain the FooB, even though it actually does.

Now call the set's iterator() method. This will cause the contents of the set to be loaded (and contains() will work properly afterward). Remove the FooB from the set and persist the FooA. Clear your caches and re-retrieve the instances from the database. Get the FooA's set and call iterator() to make it load properly. Ask if the set contains the FooB. The set will report that it DOES contain the FooB, even though it should not, since you just tried to remove it.

In both cases, the culprit is the generation of improper SQL:


2682 DEBUG [main] DataNucleus.Datastore.Native - DELETE FROM `FOOA_STUFF` WHERE `FOOA_ID_OID` = <1> AND `STUFF_FOOB_FOOB_ID_EID` = <1> AND `STUFF_FOOA_FOOA_ID_EID` = <null>

Since FOOA_STUFF is a join table to an interface, DataNucleus has created a column and foreign key constraint for each implementation of said interface. Since the code is (first) asking whether the set contains an instance of FooB or (second) trying to delete an instance of FooB from the set, STUFF_FOOA_FOOA_ID_EID is indeed NULL in the relevant rows of the table.

However, checking for null using "x = null" is improper SQL. Instead, "x is null" should be generated.

Richard DiCroce made changes - 10/Jul/11 10:26 PM
Field Original Value New Value
Attachment [ 11476 ]
Andy Jefferson added a comment - 07/Sep/11 09:14 PM
SVN trunk corrects various places for Set/List of interface using join table.

Andy Jefferson made changes - 07/Sep/11 09:14 PM
Status Open [ 1 ] Resolved [ 5 ]
Assignee Andy Jefferson [ andy ]
Fix Version/s 3.0.2 [ 11310 ]
Resolution Fixed [ 1 ]
Andy Jefferson made changes - 03/Oct/11 07:38 AM
Status Resolved [ 5 ] Closed [ 6 ]