Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.25
-
None
-
None
Description
In MariaDB 5.3 the output from the EXPLAIN of the test case for lp bug #714999 is
MariaDB [test]> EXPLAIN SELECT f2 FROM t3 WHERE (
-> SELECT MAX( pk ) FROM t1
-> WHERE EXISTS (
-> SELECT max(f1)
-> FROM t2 GROUP BY f1
-> )
-> ) IS NULL ;
-------------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-------------------------------------------------------------------------------------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
| 2 | SUBQUERY | t1 | system | NULL | NULL | NULL | NULL | 1 | |
| 3 | SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 2 | Using temporary; Using filesort |
-------------------------------------------------------------------------------------------------------------+
So the expected number of examined rows from t2 is 2 and that is correct.
Yet in MariaDB 5.5 the EXPLAIN output for the same test case is
MariaDB [test]> EXPLAIN SELECT f2 FROM t3 WHERE (
-> SELECT MAX( pk ) FROM t1
-> WHERE EXISTS (
-> SELECT max(f1)
-> FROM t2 GROUP BY f1
-> )
-> ) IS NULL ;
----------------------------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
----------------------------------------------------------------------------------------------------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
| 2 | SUBQUERY | t1 | system | NULL | NULL | NULL | NULL | 1 | |
| 3 | SUBQUERY | internal_tmp_table | ALL | group_key | NULL | NULL | NULL | 1 | Using temporary; Using filesort |
----------------------------------------------------------------------------------------------------------------------------+
Here the name of the table in the third line is 'internal_tmp_table' and the expected number of the examined rows is 1.
Apparently this is not correct. This line of the EXPLAIN output is for the plan of the uncorrelated sub-query 'SELECT max(f1)FROM t2 GROUP BY f1'.
This change in the plan appeared after the patch that fixes lp bug #944706.
I consider this as a serious regression bug.