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)