[MDEV-447] Wrong output from the EXPLAIN command of the test case for lp bug #714999 Created: 2012-08-11  Updated: 2012-08-21  Resolved: 2012-08-21

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.25
Fix Version/s: 5.5.27

Type: Bug Priority: Major
Reporter: Igor Babaev Assignee: Timour Katchaounov (Inactive)
Resolution: Fixed Votes: 0
Labels: 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.

 Comments   
Comment by Timour Katchaounov (Inactive) [ 2012-08-14 ]

This should be fixed by backporting the relevant part of SHOW EXPLAIN.

Comment by Timour Katchaounov (Inactive) [ 2012-08-21 ]

Got approval from SergeyP, Merged, tested, pushed.

Generated at Thu Feb 08 06:28:49 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.