Hans Zeller created TRAFODION-2438: -------------------------------------- Summary: 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)