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

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

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)