[jira] [Created] (TRAFODION-280) LP Bug: 1320411 - Merge sees error and leaves the table half updated

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

Alice Chen created TRAFODION-280:
------------------------------------

             Summary: LP Bug: 1320411 - Merge sees error and leaves the table half updated
                 Key: TRAFODION-280
                 URL: https://issues.apache.org/jira/browse/TRAFODION-280
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-exe
            Reporter: Weishiun Tsai
            Assignee: Anoop Sharma
            Priority: Critical


The following sequence of statements saw error 8102 at the MERGE statement.  The statement was not completely rolled back after the error and left the table half updated.  The table started out as an empty table, but was left with 1 row after the error happened.

This was seen on the datalake v40535 build.

Here is the entire script to reproduce this problem:

create table ttf1 (
sky largeint not null not droppable
, vch7 varchar(7)
, nint smallint
, ch3 char(3)
, nnum9 numeric(9,2)
, ch4 char(4)
, nnum5 numeric(5,1)
, vch5 varchar(5)
, nsint smallint signed
, primary key(sky));

insert into ttf1 (sky,vch7,nint,ch3,nnum9,ch4,nnum5,vch5,nsint) values
(1,'a',      1,'a'  ,0.9,       NULL,NULL  ,NULL,0),
(2,'cc'     ,2,'cc' ,2.00,      'cc',2.0   ,'cc',2),
(3,'abcdefg',3,'cc' ,0.09,    'alph',2     ,'cc',1),
(4,'b',      4,'c'  ,1234567.89,'e' ,1234.5,'c' ,12345),
(5,'abcdefg',5,'cc' ,0.09,      'cc',2     ,'cc',2);

create table ttf2(
sk largeint not null not droppable
, v1   varchar(7)
, n2   smallint
, c3   char(3)
, n4   numeric(9,2)
, c5   char(4)
, n6   numeric(5,1)
, v7   varchar(5)
, n8   smallint
, primary key (sk));

merge into ttf2
using (select * from ttf1) as z
on sk = 1
when matched then update set (v1,n2,c3)=(z.vch7,z.nint,z.ch3)
when not matched then insert (sk,v1,n2,c3,n4,c5,n6,v7,n8) values
(2,z.vch7,z.nint,z.ch3,z.nnum9,z.ch4,z.nnum5,z.vch5,z.nsint);

select * from ttf2 order by sk;

--------------------------------------------------------------------------------

Here is the execution output on Trafodion:

>>create table ttf1 (
+>sky largeint not null not droppable
+>, vch7 varchar(7)
+>, nint smallint
+>, ch3 char(3)
+>, nnum9 numeric(9,2)
+>, ch4 char(4)
+>, nnum5 numeric(5,1)
+>, vch5 varchar(5)
+>, nsint smallint signed
+>, primary key(sky));

--- SQL operation complete.
>>
>>insert into ttf1 (sky,vch7,nint,ch3,nnum9,ch4,nnum5,vch5,nsint) values
+>(1,'a',      1,'a'  ,0.9,       NULL,NULL  ,NULL,0),
+>(2,'cc'     ,2,'cc' ,2.00,      'cc',2.0   ,'cc',2),
+>(3,'abcdefg',3,'cc' ,0.09,    'alph',2     ,'cc',1),
+>(4,'b',      4,'c'  ,1234567.89,'e' ,1234.5,'c' ,12345),
+>(5,'abcdefg',5,'cc' ,0.09,      'cc',2     ,'cc',2);

--- 5 row(s) inserted.
>>
>>create table ttf2(
+>sk largeint not null not droppable
+>, v1   varchar(7)
+>, n2   smallint
+>, c3   char(3)
+>, n4   numeric(9,2)
+>, c5   char(4)
+>, n6   numeric(5,1)
+>, v7   varchar(5)
+>, n8   smallint
+>, primary key (sk));

--- SQL operation complete.
>>
>>merge into ttf2
+>using (select * from ttf1) as z
+>on sk = 1
+>when matched then update set (v1,n2,c3)=(z.vch7,z.nint,z.ch3)
+>when not matched then insert (sk,v1,n2,c3,n4,c5,n6,v7,n8) values
+>(2,z.vch7,z.nint,z.ch3,z.nnum9,z.ch4,z.nnum5,z.vch5,z.nsint);

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) updated.
>>
>>select * from ttf2 order by sk;

SK                    V1       N2      C3   N4            C5    N6            V7     N8
--------------------  -------  ------  ---  ------------  ----  ------------  -----  ------

                   2  a             1  a             .90  ?                ?  ?           0

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

--------------------------------------------------------------------------------

For reference purpose, here is the execution output on SQ (notice that it is an 8595 error on SQ)

>>create table ttf1 (
+>sky largeint not null not droppable
+>, vch7 varchar(7)
+>, nint smallint
+>, ch3 char(3)
+>, nnum9 numeric(9,2)
+>, ch4 char(4)
+>, nnum5 numeric(5,1)
+>, vch5 varchar(5)
+>, nsint smallint signed
+>, primary key(sky));

--- SQL operation complete.
>>
>>insert into ttf1 (sky,vch7,nint,ch3,nnum9,ch4,nnum5,vch5,nsint) values
+>(1,'a',      1,'a'  ,0.9,       NULL,NULL  ,NULL,0),
+>(2,'cc'     ,2,'cc' ,2.00,      'cc',2.0   ,'cc',2),
+>(3,'abcdefg',3,'cc' ,0.09,    'alph',2     ,'cc',1),
+>(4,'b',      4,'c'  ,1234567.89,'e' ,1234.5,'c' ,12345),
+>(5,'abcdefg',5,'cc' ,0.09,      'cc',2     ,'cc',2);

--- 5 row(s) inserted.
>>
>>create table ttf2(
+>sk largeint not null not droppable
+>, v1   varchar(7)
+>, n2   smallint
+>, c3   char(3)
+>, n4   numeric(9,2)
+>, c5   char(4)
+>, n6   numeric(5,1)
+>, v7   varchar(5)
+>, n8   smallint
+>, primary key (sk));

--- SQL operation complete.
>>
>>merge into ttf2
+>using (select * from ttf1) as z
+>on sk = 1
+>when matched then update set (v1,n2,c3)=(z.vch7,z.nint,z.ch3)
+>when not matched then insert (sk,v1,n2,c3,n4,c5,n6,v7,n8) values
+>(2,z.vch7,z.nint,z.ch3,z.nnum9,z.ch4,z.nnum5,z.vch5,z.nsint);

*** ERROR[8595] Key values specified in the INSERT part of a MERGE statement must be the same as those specified in the ON clause.

--- 0 row(s) updated.
>>
>>select * from ttf2 order by sk;

--- 0 row(s) selected.



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