[
https://issues.apache.org/jira/browse/TRAFODION-1426?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14662470#comment-14662470
]
ASF GitHub Bot commented on TRAFODION-1426:
-------------------------------------------
GitHub user anoopsharma00 opened a pull request:
https://github.com/apache/incubator-trafodion/pull/49
[TRAFODION-1426] null values in RI constraint
null values in columns of referencing table incorrectly cause referential
constraint to fail
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/anoopsharma00/incubator-trafodion fix2
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/incubator-trafodion/pull/49.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #49
----
commit 8c94c6f0e4eeb0eaa278a5fadeb6906f08a46789
Author: Anoop Sharma <[email protected]>
Date: 2015-08-07T20:32:53Z
commit #1
commit 2b0b85f054ddddef2e18e8ae421337d72f894d99
Author: Anoop Sharma <[email protected]>
Date: 2015-08-07T20:33:19Z
Merge remote branch 'origin/master' into fix1
----
> 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
> Assignee: 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)
