Description john.vandale 2010-03-16 15:56:03 EDT

Build Identifier: 

With a DirectCollection mapping to a nullable varchar DB column (e.g.: Oracle
RDBMS) if you modify this collection to remove elements with null values the DB records with <null> are not deleted.

For example, consider the table:
FK_ID   NAME
-----   ----
1001    <null>
1001    test

The fetched Collection<String> is {null, 'test'}.
Update the collection by setting it with {'newname1', 'newname2'}.

The sequence of queries in the log is:

 DELETE FROM table WHERE ((fk_id = ?) AND (name = ?))
     bind => [1001, null]
 DELETE FROM table WHERE ((fk_id = ?) AND (name = ?))
     bind => [1001, test]
 INSERT INTO table (fk_id, name) VALUES (?, ?)
     bind => [1001, newname1]
 INSERT INTO table (fk_id, name) VALUES (?, ?)
     bind => [1001, newname2]

'...WHERE some_column = null' is not a valid instance for a number of
RDBMSs including Oracle. The correct one is '...WHERE some_column is null'.

As a result you get the following incorrect data:

FK_ID   NAME
-----   ----
1001    <null>
1001    newname1
1001    newname2 

Reproducible: Always

Steps to Reproduce:
1. Run a project that uses a DirectCollection mapping against an Oracle 10 or 11 instance.

2. In a UoW populate the referenced table with at least one null value

3. In a UoW retrieve the collection and remove the null value member from the collection object then commit this.

4. With logging enabled notice this executes the SQL:

     DELETE FROM DEPTCOLLECTION WHERE ((DEPTNO = 90) AND (VALUE = null))

   and that the null record is not removed from the table.

   The proper SQL to remove this is :

    DELETE FROM DEPTCOLLECTION WHERE ((DEPTNO = 90) AND (VALUE is null))

   You can verify this by executing these SQL statements directly on the DB.


For example given the following tables:

  create table deptcollection(
   deptno number,
   value varchar2(100)
  );

  create table dept
  (deptno number(2),
   dname varchar2(14) ,
   loc varchar(13)); 

with deptcollection having a null value record the following code should remove that record but it doesn't:

        ExpressionBuilder exp = new ExpressionBuilder();
        Expression exp2 = exp.get("deptno").equal(90);

        Vector depts = session.readAllObjects(Dept.class, exp2);
        Dept dept = (Dept)(depts.get(0));
        Vector v = dept.getEmpCollection();
        System.out.println(v);
        // This gives "abc" , "xyz", null        

        UnitOfWork uow = session.acquireUnitOfWork();

        Dept d = (Dept)uow.registerObject(dept);
        v.remove(null);
        d.setEmpCollection( v );
        uow.commit();