[jira] [Closed] (TRAFODION-1254) LP Bug: 1460771 - UPSERT with indexes can cause inconsistent index

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

     [ https://issues.apache.org/jira/browse/TRAFODION-1254?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Suresh Subbiah closed TRAFODION-1254.
-------------------------------------

> LP Bug: 1460771 - UPSERT with indexes can cause inconsistent index
> ------------------------------------------------------------------
>
>                 Key: TRAFODION-1254
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-1254
>             Project: Apache Trafodion
>          Issue Type: Bug
>          Components: sql-cmp
>            Reporter: Hans Zeller
>            Assignee: Suresh Subbiah
>            Priority: Critical
>             Fix For: 2.0-incubating
>
>
> 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;



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