[jira] [Resolved] (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>]

     [ https://issues.apache.org/jira/browse/TRAFODION-2574?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Suresh Subbiah resolved TRAFODION-2574.
---------------------------------------
    Resolution: Fixed

> 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)