[jira] [Created] (TRAFODION-1587) Update of primary key on table with index when set clause has subquery gives wrong result

["Suresh Subbiah (JIRA)" <ji...@apache.org>]

Suresh Subbiah created TRAFODION-1587:
-----------------------------------------

             Summary: Update of primary key on table with index when set clause has subquery gives wrong result
                 Key: TRAFODION-1587
                 URL: https://issues.apache.org/jira/browse/TRAFODION-1587
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-cmp
    Affects Versions: 1.2-incubating
            Reporter: Suresh Subbiah
            Assignee: Suresh Subbiah


Updating primary key of a table with
a) an index
b) using a self-referencing subquery in set clause

gives wrong result as shown below, sometimes.

Problem found by Selva and analyzed by Dave Birdsall and Selva.

The Hash join in plan causes the delete to occur before subquery is evaluated, even though subquery scan is early in the the plan (node 1).
A fix will attempt to change the hash join to a tsj .

set schema mytest;
create schema mytest;
create table mytable (c1 char(1), c2 integer not null primary key);
CREATE INDEX MYTABLE_IDX ON MYTABLE(C1 ASC);
insert into mytable values ('A', 100), ('B', 200), ('C', 300);
select * from mytable order by 1;
prepare xx from update mytable set c2 = 
(select c from (select count(distinct c2) from mytable where c1 = 'A') dt(c))
where c2 = 100 ;
explain options 'f' xx ;
execute xx ;

>>explain options 'f' xx ;

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

12   .    13   root                            x                     1.00E+000
10   11   12   nested_join                                           1.00E+000
.    .    11   trafodion_insert                MYTABLE_IDX           1.00E+000
8    9    10   nested_join                                           1.00E+000
.    .    9    trafodion_insert                MYTABLE               1.00E+000
7    .    8    sort                                                  1.00E+000
6    3    7    hybrid_hash_join                                      1.00E+000
4    5    6    nested_anti_semi_joi                                  1.00E+000
.    .    5    trafodion_delete                MYTABLE_IDX           1.00E+000
.    .    4    trafodion_delete                MYTABLE               1.00E+000
2    .    3    sort_scalar_aggr                                      1.00E+000
1    .    2    sort_scalar_aggr                                      1.00E+000
.    .    1    trafodion_index_scan            MYTABLE_IDX           1.00E+001


>>select * from mytable ;

C1  C2         
--  -----------

A             0
B           200
C           300





--
This message was sent by Atlassian JIRA
(v6.3.4#6332)