[jira] [Assigned] (TRAFODION-2415) wrong plan picked when using predicate on multiple columns of a multi columns INDEX

["Hans Zeller (JIRA)" <ji...@apache.org>]

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

Hans Zeller reassigned TRAFODION-2415:
--------------------------------------

    Assignee: Hans Zeller

> wrong plan picked when using predicate on multiple columns of a multi columns INDEX
> -----------------------------------------------------------------------------------
>
>                 Key: TRAFODION-2415
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-2415
>             Project: Apache Trafodion
>          Issue Type: Bug
>          Components: sql-cmp
>            Reporter: Eric Owhadi
>            Assignee: Hans Zeller
>
> create table t(
> a char(1) not null,
> b char(1) not null,
> c char(1) not null,
> d char(1) not null,
> e CHAR(1) NOT NULL,
> f SMALLINT UNSIGNED NOT NULL,		
> g SMALLINT UNSIGNED NOT NULL,	
> h INT UNSIGNED NOT NULL,
> customer CHAR(20) NOT NULL,
> count INT UNSIGNED,
> price LARGEINT,
> PRIMARY KEY (a,b,c,d,e,f,g,h,customer)
> )
> SALT USING 4 PARTITIONS; 
> CREATE INDEX t_idx_by_b ON t
> (b,count,price);
> CREATE INDEX t_idx_by_c ON t
> (c,count,price);
> CREATE INDEX t_idx_by_d ON t
> (d,count,price);
> CREATE INDEX t_idx_by_e ON t
> (e,count,price);
> CREATE INDEX t_idx_by_f ON t
> (f,count,price);
> CREATE INDEX t_idx_by_g ON t
> (g,count,price);
> CREATE INDEX t_idx_by_h ON t
> (h,count,price);
> CREATE INDEX t_idx_by_count ON t
> (customer,count,price);
> SELECT e, SUM(price)
> FROM t
> WHERE 
>                 b IN ('1','2','3') 
>                 AND 
>                 f IN (10,20, 30)
> GROUP BY 1;
> generate wrong plan doing full scan on t_idx_by_f
> while
> SELECT e, SUM(price)
> FROM t
> WHERE 
>                 f IN (10,20, 30)
> GROUP BY 1;
> generate good plan doing mdam on t_idx_by_f only.
> using cqd rangespec_transformation 'off';
> makes the problem go away.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)