[jira] [Created] (TRAFODION-1426) null values in columns of referencing table incorrectly cause referential constraint to fail

["Anoop Sharma (JIRA)" <ji...@apache.org>]

Anoop Sharma created TRAFODION-1426:
---------------------------------------

             Summary: null values in columns of referencing table incorrectly cause referential constraint to fail 
                 Key: TRAFODION-1426
                 URL: https://issues.apache.org/jira/browse/TRAFODION-1426
             Project: Apache Trafodion
          Issue Type: Bug
            Reporter: Anoop Sharma


When creating an referential constraint on a table with existing rows
containing null values, those null values should not cause RI constraint
creation to fail. 
For correct behavior, those null values need to be ignored and constraint
creation should succeed.

This issue doesnt show up if the constraint is created on an empty
table and null values are later added to the referencing table.

This example shows the behavior with and without data:

***** constraint fails if null values already exist in the referencing table ******
>>create table t1 (a int, b int);

--- SQL operation complete.
>>
>>create table t2 (a int not null, b int not null, primary key(a,b));

--- SQL operation complete.
>>
>>insert into t1 values (1,null), (null, null);

--- 2 row(s) inserted.
>>
>>alter table t1 add constraint t1c1 foreign key (a,b) references t2(a,b);

*** ERROR[1143] Validation of constraint TRAFODION.SEABASE.T1C1 failed; incompatible data exists in referencing base table T1 and referenced base table T2.  To display the data that violates the constraint, please use the following DML statement: select count(*) from "TRAFODION"."SEABASE"."T1" where not (("A", "B") in (select "A", "B" from "TRAFODION"."SEABASE"."T2")) or "A" is null or "B" is null ;

--- SQL operation failed with errors.
>>
>>delete from t1;

--- 2 row(s) deleted.
>>
>>insert into t1 values (1,null), (null, null);

--- 2 row(s) inserted.
>>
>>log;
>>select * from t1;

A            B          
-----------  -----------

          1            ?
          ?            ?

--- 2 row(s) selected.
>>

**** constraint correctly created and evaluated on an empty table ****
Trafodion Conversational Interface 1.2.0
(c) Copyright 2014 Hewlett-Packard Development Company, LP.
>>create table t1 (a int, b int);

--- SQL operation complete.
>>create table t2 (a int not null, b int not null, primary key(a,b));

--- SQL operation complete.

>>alter table t1 add constraint t1c1 foreign key (a,b) references t2(a,b);

--- SQL operation complete.

>>
>>insert into t1 values (1,null), (null, null);

--- 2 row(s) inserted.
>>

*******************************************************************
Fix it to change the expression from "or a is null or b is null"
to "and a is not null and b is not null" in the expression that is
created to validate foreign key constraint during creation.




--
This message was sent by Atlassian JIRA
(v6.3.4#6332)