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

["Atanu Mishra (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/TRAFODION-1426?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Atanu Mishra closed TRAFODION-1426.
-----------------------------------
       Resolution: Fixed
    Fix Version/s: 1.2-incubating

> 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
>          Components: sql-exe
>            Reporter: Anoop Sharma
>            Assignee: Anoop Sharma
>             Fix For: 1.2-incubating
>
>
> 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)