Eric Owhadi created TRAFODION-2415:
--------------------------------------
Summary: 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
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)
