[jira] [Resolved] (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 resolved TRAFODION-1254.
---------------------------------------
    Resolution: Fixed

This issue is now resolved though further improvements are possible

1) For UPSERT INTO statements with an index, the statement is now internally transformed into a MERGE statement, which can delete old rows from the index_table, if the row has been updated instead of inserted. This transformation is done is the table does not have a syskey column or a identity key (always type) in the key.

2) For LOAD INTO statements on a table with an index, the new way of maintaining the indexes incrementally is allowed by default only when the table has a syskey. So we don't get into the problem situation by default. If the user is certain that there are no duplicate rows (between old and new rows) then they can use cqd TRAF_LOAD_ALLOW_RISKY_INDEX_MAINTENANCE 'ON'



> 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)