UPSERT with indexes can cause inconsistent index

[Hans Zeller]

When we do UPSERT statements, some of the rows we insert are new and some may overwrite already existing rows. In the index maintenance tree, we need to insert a new index row for both cases, but we also need to delete any existing index rows. Right now this is not happening, and it partially defeats the whole idea of upsert, which is mostly used to avoid the more expensive check and put operation.

This bug affects UPSERT INTO <table> and also the new index maintenance method introduced in changes https://review.trafodion.org/#/c/1694/ and https://review.trafodion.org/#/c/1705/. It does not affect the UPSERT USING LOAD method that recreates the indexes instead of incrementally maintaining them.

Here is a test case:

-- create a simple table with an index
create table upsertx(id int not null, a int, b int,
                          primary key (id));
create index ix1 on upsertx(a);

-- insert 10,000 unique rows
prepare s from
upsert into upsertx
select num, num+10000, num+10000
from (select 1000*e3 + 100*e2 + 10*e1 + e0
      from (values (0)) seed(s)
      transpose 0,1,2,3,4,5,6,7,8,9 as e0
      transpose 0,1,2,3,4,5,6,7,8,9 as e1
      transpose 0,1,2,3,4,5,6,7,8,9 as e2
      transpose 0,1,2,3,4,5,6,7,8,9 as e3) t(num);
explain options 'f' s;
execute s;

-- now insert 100 duplicate rows
prepare s from
upsert into upsertx
select 100*num, 100*num+20000, 100*num+20000
from (select 10*e1 + e0
      from (values (0)) seed(s)
      transpose 0,1,2,3,4,5,6,7,8,9 as e0
      transpose 0,1,2,3,4,5,6,7,8,9 as e1) t(num);
explain options 'f' s;
execute s;

-- table has 10,000 rows in it
select count(*) from upsertx;

-- the index has 10,100 rows!!
set parserflags 1;
select count(*) from table(index_table IX1);

-- show some of the duplicate rows in the index
select * from table(index_table IX1)
where id in (100, 101, 200, 201)
order by id;