[ https://issues.apache.org/jira/browse/TRAFODION-2438?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15832263#comment-15832263 ]
ASF GitHub Bot commented on TRAFODION-2438: ------------------------------------------- GitHub user sureshsubbiah opened a pull request: https://github.com/apache/incubator-trafodion/pull/922 [TRAFODION-2438] Unnecessary and sometimes wrong delete during upsert… …/merge with IM This change is done by Hans. Design by Selva. Please see JIRA for an explanation. You can merge this pull request into a Git repository by running: $ git pull https://github.com/sureshsubbiah/incubator-trafodion upsert1 Alternatively you can review and apply these changes as the patch at: https://github.com/apache/incubator-trafodion/pull/922.patch To close this pull request, make a commit to your master/trunk branch with (at least) the following in the commit message: This closes #922 ---- commit 1450e83627b5bdde6f947c963152ce00f9d5e907 Author: Suresh Subbiah <sure...@apache.org> Date: 2017-01-20T18:15:06Z [TRAFODION-2438] Unnecessary and sometimes wrong delete during upsert/merge with IM This change is done by Hans. Design by Selva. Please see JIRA for an explanation. ---- > Missing index rows with UPSERT, executor/test015 > ------------------------------------------------ > > Key: TRAFODION-2438 > URL: https://issues.apache.org/jira/browse/TRAFODION-2438 > Project: Apache Trafodion > Issue Type: Bug > Components: sql-cmp > Affects Versions: 2.0-incubating > Reporter: Hans Zeller > Assignee: Hans Zeller > Fix For: 2.1-incubating > > > Selva found the cause of this problem and suggested the fix. > The problem was discovered as a failure of regression test executor/test015, > with some missing index rows. > Selva found that this is because we insert a "tombstone" into each alternate > index for every row, even if it is a new row. Those tombstones for the > non-existing old rows contain junk or NULL values, and they can potentially > wipe out existing index rows. > Selva's suggested fix is to use the "pre-condition" for the index delete to > check whether an old row actually existed, and only do a delete if it did. > Here is a simple test case, also from Selva: > {noformat} > drop table t015t8 cascade ; > create table t015t8 (i int not null, j int, k int, primary key(i)); > create unique index t015t8i1 on t015t8(j); > insert into t015t8 values (4,null,4); > select * from t015t8; > -- enable this CQD to try the new, efficient tree > -- cqd TRAF_UPSERT_TO_EFF_TREE 'on'; > prepare s1 from upsert into t015t8 values (1,2,3),(11,12,13) ; > execute s1 ; > set parserflags 1; > select * from t015t8; > select * from table (index_table t015t8i1); > -- wrong answer, 2 rows instead of 3 > {noformat} > If this does not reproduce the error or if you want to see the extra > tombstones, do an HBase raw scan to see them: > {noformat} > hbase shell > scan 'TRAFODION.SCH.T015T8I1' , {RAW=>true} > {noformat} -- This message was sent by Atlassian JIRA (v6.3.4#6332)