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_switch="index_condition_pushdown=on";
SET SESSION optimizer_switch="materialization=off";
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 );
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_switch="index_condition_pushdown=off";
SET SESSION optimizer_switch="materialization=off";
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_switch="index_condition_pushdown=on";
SET SESSION optimizer_switch="materialization=on";
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)
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