[
https://issues.apache.org/jira/browse/TRAFODION-1254?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14715454#comment-14715454
]
Suresh Subbiah commented on TRAFODION-1254:
-------------------------------------------
This change was merged today with this commit.
https://github.com/apache/incubator-trafodion/commit/5928f31a1e20c70a528101487def3db0ef0ade9c
> 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)
