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

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Fix Version/s: 2.0-incubating
• Assignee: Selvaganesan Govindarajan
• Reporter: Selvaganesan Govindarajan
• Created: 27/Jun/15 18:34
• Updated: 15/Sep/18 07:32
• Resolved: 06/Aug/15 15:09

Description

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.