[TRAFODION-2574] Index plan not chosen for UPDATE when WHERE clause and SET clause are on the same index column

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: any
• Fix Version/s: 2.2.0
• Component/s: sql-cmp
• Assignee: Suresh Subbiah
• Reporter: Suresh Subbiah
• Created: 03/Apr/17 18:14
• Updated: 15/Sep/18 07:30
• Resolved: 24/May/17 14:47

Description

create table tbl (
k1 int not null,
k2 int not null,
ts timestamp not null,
a char(10),
b varchar(30),
c largeint,
primary key (k1,k2,ts))
salt using 8 partitions
division by (date_trunc('MONTH', ts)) ;
upsert using load into tbl
select num/1000, num, DATEADD(SECOND,-num,CURRENT_TIMESTAMP),cast(num as char(10)), cast(num as varchar(30)), num*1000
from (select 10000000*x10000000+1000000*x1000000+100000*x100000+10000*x10000+1000*x1000+100*x100+10*x10+x1 as num
from (values (0)) seed(c)
transpose 0,1,2,3,4,5,6,7,8,9 as x1
transpose 0,1,2,3,4,5,6,7,8,9 as x10
transpose 0,1,2,3,4,5,6,7,8,9 as x100
transpose 0,1,2,3,4,5,6,7,8,9 as x1000
transpose 0,1,2,3,4,5,6,7,8,9 as x10000
transpose 0,1,2,3,4,5,6,7,8,9 as x100000
transpose 0,1,2,3,4,5,6,7,8,9 as x1000000
transpose 0,1,2,3,4,5,6,7,8,9 as x10000000
) T
;
create index tbl_idx1_b on tbl(b) salt like table;

create index tbl_idx1_ab on tbl(a,b) salt like table;

update statistics for table tbl on every column sample;

– should choose tbl_idx1_b
update tbl set b = 'haha' where b = 'sad' ;

– should choose tbl_idx1_b, at least with CQS
update tbl set b = 'haha' where b > 'sad' ;

– should choose tbl_idx1_ab
update tbl set a = 'haha' where a = 'sad' ;

– should choose tbl_idx1_ab, at least with CQS
update tbl set a = 'haha' where a > 'sad' ;