Wrong result with index_condition_pushdown + GROUP BY/ORDER BY
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Igor Babaev |
Bug Description
This query:
SELECT a, MIN(d)
FROM t1
WHERE c = 'x'
AND d > 'x'
GROUP BY a;
when executed with ICP, returns rows that do not match the second, nonindexed part, of the WHERE predicate:
+------+--------+
| a | MIN(d) |
+------+--------+
| 2 | x |
| 5 | x |
+------+--------+
explain:
+----+-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-
| 1 | SIMPLE | t1 | ref | c | c | 4 | const | 1 | Using index condition; Using where; Using temporary; Using filesort |
+----+-
bzr version-info
revision-id: <email address hidden>
date: 2011-10-06 01:21:15 +0400
build-date: 2011-10-07 17:34:34 +0300
revno: 3213
minimal switch: index_condition
full switch: index_merge=
join_cache_level = 1
test case:
DROP TABLE t1;
CREATE TABLE t1 ( a int, c varchar(1), d varchar(1), KEY c (c));
INSERT INTO t1 VALUES (2,'x',
SET SESSION optimizer_
SELECT a, MIN(d)
FROM t1
WHERE c = 'x'
AND d > 'x'
GROUP BY a;
Changed in maria: | |
assignee: | nobody → Sergey Petrunia (sergefp) |
milestone: | none → 5.3 |
Changed in maria: | |
importance: | Undecided → High |
Changed in maria: | |
assignee: | Sergey Petrunia (sergefp) → Igor Babaev (igorb-seattle) |
status: | New → In Progress |
Changed in maria: | |
status: | In Progress → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |
Here is another example:
DROP TABLE IF EXISTS lineitem; l_linenumber) ) ; ,(551,3) ,(576,1) ,(576,2) ,(576,3) ,(576,4) ,(577,1) ,(577,2) ,(578,1) ,(578,2) ,(579,1) ,(579,2) ,(579,3) ,(579,4) ,(579,5) ,(579,6) ,(580,1) ,(580,2) ,(580,3) ,(581,1) ,(581,2) ,(581,3) ,(581,4) ,(582,1) ,(582,2) ,(582,3) ,(582,4) ;
CREATE TABLE lineitem ( l_orderkey int(11) NOT NULL , l_linenumber int(11) NOT NULL , PRIMARY KEY (l_orderkey,
INSERT IGNORE INTO lineitem VALUES (514,4)
DROP TABLE IF EXISTS orders;
CREATE TABLE orders ( o_orderkey int(11) NOT NULL , o_custkey int(11), KEY (o_custkey), PRIMARY KEY (o_orderkey)) ;
INSERT IGNORE INTO orders VALUES (513,7),(514,8);
DROP TABLE IF EXISTS customer;
CREATE TABLE customer ( c_custkey int(11) NOT NULL , c_nationkey int(11), KEY (c_nationkey), PRIMARY KEY (c_custkey)) ;
INSERT IGNORE INTO customer VALUES (8,17),(10,5);
DROP TABLE IF EXISTS supplier;
CREATE TABLE supplier ( s_nationkey int(11), KEY (s_nationkey)) ;
INSERT IGNORE INTO supplier VALUES (17);
SET SESSION optimizer_ switch= 'index_ condition_ pushdown= ON';
EXPLAIN SELECT c_custkey FROM lineitem JOIN orders ON ( l_orderkey = o_orderkey ) JOIN customer ON ( o_custkey = c_custkey ) JOIN nation ON ( c_nationkey = n_nationkey ) JOIN supplier ON ( s_nationkey = n_nationkey ) WHERE c_custkey IN ( 170 , 137 ) group by 1;
Note that the EXPLAIN of the query does not include any reference to ICP, and yet the query returns a wrong result. Every use of any optimization, ICP included, must be reflected in EXPLAIN. Otherwise, it is very difficult to test.