Suresh Subbiah created TRAFODION-2574:
-----------------------------------------
Summary: Index plan not chosen for UPDATE when WHERE clause and
SET clause are on the same index column
Key: TRAFODION-2574
URL: https://issues.apache.org/jira/browse/TRAFODION-2574
Project: Apache Trafodion
Issue Type: Bug
Components: sql-cmp
Affects Versions: any
Reporter: Suresh Subbiah
Assignee: Suresh Subbiah
Fix For: 2.2-incubating
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' ;
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
