Wrong result with enabled index condition pushdown and disabled subquery materialization.
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
In Progress
|
High
|
Sergey Petrunia |
Bug Description
The following sequence of commands gives a wrong query result in MariaDB 5.3/5.5:
CREATE TABLE t1 (i1 INTEGER, i2 INTEGER, KEY k1 (i1));
INSERT INTO t1 VALUES (9,0), (7,1), (1,9), (7,101), (2,1), (9,102);
CREATE TABLE t2 (pk INTEGER, i2 INTEGER, PRIMARY KEY (pk));
INSERT INTO t2 VALUES (2,1), (3,2), (5,3), (6,4), (7,6), (9,4);
CREATE TABLE t3 (i1 INTEGER, i2 INTEGER);
INSERT INTO t3 VALUES (1,0), (1,1), (1,101), (1,102);
SET SESSION optimizer_
SET SESSION optimizer_
SELECT * FROM t3
WHERE (i1, i2) IN
( SELECT COUNT(DISTINCT t2.i2), t1.i2 FROM t1 JOIN t2 ON t1.i1 = t2.pk
MariaDB [test]> SELECT * FROM t3 WHERE (i1, i2) IN ( SELECT COUNT(DISTINCT t2.i2), t1.i2 FROM t1 JOIN t2 ON t1.i1 = t2.pk WHERE t2.pk BETWEEN 7 AND 9 GROUP BY t1.i2 );
+------+------+
| i1 | i2 |
+------+------+
| 1 | 0 |
| 1 | 101 |
| 1 | 102 |
+------+------+
The correct answer for the query is returned with these settings:
SET SESSION optimizer_
SET SESSION optimizer_
MariaDB [test]> SELECT * FROM t3 WHERE (i1, i2) IN ( SELECT COUNT(DISTINCT t2.i2), t1.i2 FROM t1 JOIN t2 ON t1.i1 = t2.pk WHERE t2.pk BETWEEN 7 AND 9 GROUP BY t1.i2 );
+------+------+
| i1 | i2 |
+------+------+
| 1 | 0 |
| 1 | 1 |
| 1 | 101 |
| 1 | 102 |
+------+------+
And with these settings also we have the correct answer
SET SESSION optimizer_
SET SESSION optimizer_
MariaDB [test]> SELECT * FROM t3 WHERE (i1, i2) IN ( SELECT COUNT(DISTINCT t2.i2), t1.i2 FROM t1 JOIN t2 ON t1.i1 = t2.pk WHERE t2.pk BETWEEN 7 AND 9 GROUP BY t1.i2 );
+------+------+
| i1 | i2 |
+------+------+
| 1 | 0 |
| 1 | 1 |
| 1 | 101 |
| 1 | 102 |
+------+------+
(See also bug #12667154 in mysql-5.6.5)
Changed in maria: | |
status: | New → Confirmed |
importance: | Undecided → High |
assignee: | nobody → Sergey Petrunia (sergefp) |
milestone: | none → 5.3 |
tags: | added: optimizer wrong-result |
Changed in maria: | |
status: | Confirmed → In Progress |
Changed in maria: | |
status: | In Progress → Fix Committed |
status: | Fix Committed → In Progress |
The problem is actually inherited from 5.2. There, one can also observe that
- the first invocation of find_all_keys() is done with quick_select!=NULL, and the quick select is used to read records
- the second invocation of find_all_keys() is done with quick_select==NULL, and full scan is used.
this is clearly not what was intended. However, the effect is only performance, the query produces correct result. MariaDB 5.3/ MySQL 5.6 add IndexConditionP ushdown, which is not cleaned up correctly and causes wrong query result.