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

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: sql-cmp
• Assignee: Unassigned
• Reporter: Eric Owhadi
• Created: 22/Dec/16 00:07
• Updated: 22/Dec/16 02:03

Description

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.

Issue Links

• is related to: TRAFODION-1641 Apparent inappropriate choice of MDAM in query plan