[jira] [Created] (TRAFODION-288) LP Bug: 1321479 - Updating char column violates a check constraint

["Alice Chen (JIRA)" <ji...@apache.org>]

Alice Chen created TRAFODION-288:
------------------------------------

             Summary: LP Bug: 1321479 - Updating char column violates a check constraint
                 Key: TRAFODION-288
                 URL: https://issues.apache.org/jira/browse/TRAFODION-288
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-cmp
            Reporter: Weishiun Tsai
            Assignee: Anoop Sharma
            Priority: Critical


In the following example, column b is a char column with a constraint to check that it is either ‘AB’ or ‘CD’.  But an update statement allows it to be updated to ‘EF’ as shown in the output.

This is seen on the datalake v40671 build.

========================================================================

Here is the script to reproduce this problem:

create table t (a int not null not droppable primary key, b char(10) check (b in ('AB', 'CD')));
insert into t values (1, 'AB');
update t set b = 'EF' where b = 'AB';
select * from t;

========================================================================

Here is the output of the execution:

>>create table t (a int not null not droppable primary key, b char(10) check (b in ('AB', 'CD')));

--- SQL operation complete.
>>insert into t values (1, 'AB');

--- 1 row(s) inserted.
>>update t set b = 'EF' where b = 'AB';

--- 1 row(s) updated.
>>select * from t;

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

          1  EF        

--- 1 row(s) selected.

========================================================================

For reference purpose, here is the behavior on SQ:

>>create table t (a int not null not droppable primary key, b char(10) check (b
in ('AB', 'CD')));

--- SQL operation complete.
>>insert into t values (1, 'AB');

--- 1 row(s) inserted.
>>update t set b = 'EF' where b = 'AB';

*** ERROR[8101] The operation is prevented by check constraint NEO.USR.T_5226969
13_6872 on table NEO.USR.T.

--- 0 row(s) updated.
>>select * from t;

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

          1  AB

--- 1 row(s) selected.



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