UPSERT with indexes can cause inconsistent index

Bug #1460771 reported by Hans Zeller on 2015-06-01
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Trafodion
In Progress
High
Suresh Subbiah

Bug Description

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;

Changed in trafodion:
assignee: nobody → Suresh Subbiah (suresh-subbiah)
Changed in trafodion:
status: New → In Progress
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers