Index created prior to ‘upsert using load’ causes query to return wrong results

[Weishiun Tsai]

If an index is created prior to using ‘upsert using load’ to load a table, the index is not populated. This only happens with ‘upsert using load’. An index created this way is populated fine with ‘upsert’ or ‘insert’.

This poses a query correctness problem as a query would return wrong results if the query plan selected uses index_scan to get the data. This problem can be seen on the 0830_0830 build installed on a workstation.

------------------------------------------

Here is the entire script to reproduce this problem:

create table t (a int, b int, c int);

create index myindex on t (a, b, c);

upsert using load into table t values (1, 1, 1), (2, 2, 2), (3, 3, 3);

prepare xx from select * from t;

explain options 'f' xx;

execute xx;

set parserflags 1;

select * from table(INDEX_TABLE myindex);

------------------------------------------

Here is the execution output of the script:

>>create table t (a int, b int, c int);

--- SQL operation complete.
>>
>>create index myindex on t (a, b, c);

--- SQL operation complete.
>>
>>upsert using load into table t values (1, 1, 1), (2, 2, 2), (3, 3, 3);

--- 3 row(s) inserted.
>>
>>prepare xx from select * from t;

--- SQL command prepared.
>>
>>explain options 'f' xx;

LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------

1 . 2 root 1.00E+002
. . 1 trafodion_index_scan T 1.00E+002

--- SQL operation complete.
>>
>>execute xx;

--- 0 row(s) selected.
>>
>>set parserflags 1;

--- SQL operation complete.
>>
>>select * from table(INDEX_TABLE myindex);

--- 0 row(s) selected.

[Anoop Sharma (anoop-sharma)]

"upsert using load" is a special syntax that should only be used if the table is empty to start with otherwise it will
overwrite existing rows. This operation is non-transactional and will leave rows in target table in case of an error.

It also cannot be used if there are any dependent objects like index, constraints, etc on the table.
We were not enforcing these preconditions and are relying on users to do that.
That has been fixed by doing transactional inserts instead of non-transactional upsert load
if the table has indexes or other dependent objects (like unique or referential constraints).

[Weishiun Tsai (wei-shiun-tsai)]

Verified on the v0909_0830 build installed on a workstation. This problem has been fixed:

>>create table t (a int, b int, c int);

--- SQL operation complete.
>>
>>create index myindex on t (a, b, c);

--- SQL operation complete.
>>
>>upsert using load into table t values (1, 1, 1), (2, 2, 2), (3, 3, 3);

--- 3 row(s) inserted.
>>
>>prepare xx from select * from t;

--- SQL command prepared.
>>
>>explain options 'f' xx;

LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------

1 . 2 root 1.00E+002
. . 1 trafodion_index_scan T 1.00E+002

--- SQL operation complete.
>>
>>execute xx;

A B C
----------- ----------- -----------

          1 1 1
          2 2 2
          3 3 3

--- 3 row(s) selected.
>>
>>set parserflags 1;

--- SQL operation complete.
>>
>>select * from table(INDEX_TABLE myindex);

A@ B@ C@ SYSKEY
----------- ----------- ----------- --------------------

          1 1 1 140219451826616608
          2 2 2 140219451826619302
          3 3 3 140219451826620069

--- 3 row(s) selected.
>>