[jira] [Commented] (TRAFODION-14) upsert or merge into a table with indexes can result in inconsistency between index and table

["Suresh Subbiah (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/TRAFODION-14?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14604942#comment-14604942 ] 

Suresh Subbiah commented on TRAFODION-14:
-----------------------------------------

Thank you very much for catching this issue.  
 
I think what you are saying is that if we update a table with an index, then we get a plan with a merge union with an index_delete and index_insert as its two children. Either one of these 2 children could execute first. If the index_insert executes first, then the delete could actually remove the newly inserted row into the index_table. Your suggestion is to add a timestamp predicate to the index_delete so that the newer row will never be deleted. I think that is a sound idea.
 
Please check if your solution will solve a similar issue for this testcase too.

--- testcase 1
create table t015t1 (a int not null, b int default 2 not null, primary key(a)) salt using 2 partitions ;
create index t015t1i2 on t015t1(b);
 
merge into t015t1 on a = 1 when matched then update set b = -1
                          when not matched then insert values (1,2);
 
select * from t015t1;
 
merge into t015t1 on a = 1 when matched then update set b = -1
                         when not matched then insert values (1,2);
 
-- this select will show 2 rows after a few runs
select * from t015t1 ;
delete from t015t1 ;
 
merge into t015t1 on a = 1 when matched then update set b = -1
                          when not matched then insert values (1,2);
 
select * from t015t1;
 
merge into t015t1 on a = 1 when matched then update set b = -1
                         when not matched then insert values (1,2);
 
-- this select will show 2 rows after a few runs
select * from t015t1 ;
delete from t015t1 ;
 

>From this explanation this problem should be present for an update statement and not for merge statement alone.

Also please take a look at Testcase 2 also. I think your solution (maybe with some tweaks such as setting the timestamp to a really small value so that no row will ever be deleted when a merge statement actually results in an insert into the base table) can apply to that also. 

-- Testcase 2
-- Merge onto a table with unique index and merge goes to when not matched clause
insert into t015t7 values (1,1,1) ;
merge into t015t7 on a = 2
when matched then update set (b,c) = (1,2) 
when not matched then insert values (2,1,2) ;
select * from t015t7;
select b from t015t7 order by 1;
-- index should have one row less. Check that first select is using base table
-- and second select is using index

 
Thanks again for finding such a good solution to the problem.

> upsert or merge into a table with indexes can result in inconsistency between index and table
> ---------------------------------------------------------------------------------------------
>
>                 Key: TRAFODION-14
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-14
>             Project: Apache Trafodion
>          Issue Type: Bug
>            Reporter: Selvaganesan
>            Priority: Critical
>
> Steps to reproduce this problem
> set schema seabase ;
> CREATE TABLE TRAFODION.SEABASE.TEST1
>   (
>     A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
>   , B                                INT DEFAULT NULL
>   , PRIMARY KEY (A ASC)
>   ) 
> ;
> CREATE INDEX IDX1 ON TRAFODION.SEABASE.TEST1
>   (
>     B ASC
>   )
> ;
> CREATE TABLE TRAFODION.SEABASE.TEST2
>   (
>     A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
>   , B                                INT DEFAULT NULL
>   , PRIMARY KEY (A ASC)
>   ) 
> ;
> insert into test2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10) ;
> prepare s2 from upsert into test1 select * from test2;
> 1) execute s2;
> 2) select * from test1 ;
> If you repeat steps 1 and 2 more than once, at times fewer than 10 rows are selected from the step 2.
> In this particular case select * from test1 involves index scan plan.



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