Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
-
None
-
None
Description
The following test case produces a wrong result set in MariaDB 5.3:
CREATE TABLE t1 (a int, b int, INDEX idx(a)); |
INSERT INTO t1 VALUES (9,0), (7,1), (1,9), (7,3), (2,1); |
|
|
CREATE TABLE t2 (a int, b int, INDEX idx(a)); |
INSERT INTO t2 VALUES (2,1), (6,4), (7,6), (9,4); |
|
|
CREATE TABLE t3 (a int, b int); |
INSERT INTO t3 VALUES (1,0), (1,1), (1,3); |
|
|
SELECT * FROM t3 |
WHERE a = (SELECT COUNT(DISTINCT t2.b) FROM t1, t2 |
WHERE t1.a = t2.a AND t2.a BETWEEN 7 AND 9 |
AND t3.b = t1.b |
GROUP BY t1.b); |
The returned result for the above query is:
MariaDB [test]> SELECT * FROM t3 |
-> WHERE a = (SELECT COUNT(DISTINCT t2.b) FROM t1, t2 |
-> WHERE t1.a = t2.a AND t2.a BETWEEN 7 AND 9 |
-> AND t3.b = t1.b |
-> GROUP BY t1.b); |
+------+------+ |
| a | b |
|
+------+------+ |
| 1 | 0 |
|
| 1 | 3 |
|
+------+------+ |
The EXPLAIN output for this query is:
|
MariaDB [test]> EXPLAIN
|
-> SELECT * FROM t3 |
-> WHERE a = (SELECT COUNT(DISTINCT t2.b) FROM t1, t2 |
-> WHERE t1.a = t2.a AND t2.a BETWEEN 7 AND 9 |
-> AND t3.b = t1.b |
-> GROUP BY t1.b); |
+----+--------------------+-------+-------+---------------+------+---------+-----------+------+----------------------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+----+--------------------+-------+-------+---------------+------+---------+-----------+------+----------------------------------------------------+ |
| 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 3 | Using where | |
| 2 | DEPENDENT SUBQUERY | t1 | range | idx | idx | 5 | NULL | 2 | Using index condition; Using where; Using filesort | |
| 2 | DEPENDENT SUBQUERY | t2 | ref | idx | idx | 5 | test.t1.a | 2 | Using index condition | |
+----+--------------------+-------+-------+---------------+------+---------+-----------+------+----------------------------------------------------+ |
3 rows in set (0.00 sec) |
If ICP is turned off the result returned by the query is correct:
MariaDB [test]> set optimizer_switch='index_condition_pushdown=off'; |
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [test]> SELECT * FROM t3 |
-> WHERE a = (SELECT COUNT(DISTINCT t2.b) FROM t1, t2 |
-> WHERE t1.a = t2.a AND t2.a BETWEEN 7 AND 9 |
-> AND t3.b = t1.b |
-> GROUP BY t1.b); |
+------+------+ |
| a | b |
|
+------+------+ |
| 1 | 0 |
|
| 1 | 1 |
|
| 1 | 3 |
|
+------+------+ |
3 rows in set (0.00 sec) |
The above test case is a simplified version of the test case for Oracle's bug#12667154 that can be found in http://lists.mysql.com/commits/143149
The fix itself (without any test case) was pulled into MariaDB 5.5.
The validity of the fix should be re-checked.