Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
None
-
None
-
None
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_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)