Issue Details (XML | Word | Printable)

Key: NUCRDBMS-544
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Andy Jefferson
Reporter: Richard DiCroce
Votes: 0
Watchers: 1
Operations

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 TestCase.zip (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:

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

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.

Sort Order: Ascending order - Click to sort in descending order
Andy Jefferson added a comment - 07/Sep/11 09:14 PM
SVN trunk corrects various places for Set/List of interface using join table.