LP:430669 - Wrong output of EXPLAIN EXTENDED on subquery with unknown column error

Seen in lp:maria in revision revid:<email address hidden> (after push of table elimination):

CREATE TABLE t1 (a INT, b INT, c INT);
INSERT INTO t1 VALUES (1,1,1), (1,1,1);
EXPLAIN EXTENDED
SELECT c FROM
( SELECT
(SELECT COUNT(a) FROM
(SELECT COUNT(b) FROM t1) AS x GROUP BY c
) FROM t1 GROUP BY b
) AS y;
ERROR 42S22: Unknown column 'c' in 'field list'
SHOW WARNINGS;
Level Code Message
Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2
Note 1276 Field or reference 'test.t1.c' of SELECT #3 was resolved in SELECT #2
Error 1054 Unknown column 'c' in 'field list'
Note 1003 select `c` AS `c` from (select (select count(`test`.`t1`.`a`) AS `COUNT(a)` from (select count(`test`.`t1`.`b`) AS `COUNT(b)` from `test`.`t1`) `x` group by `t1`.`c`) AS `(SELECT COUNT(a) FROM
(SELECT COUNT(b) FROM t1) AS x GROUP BY c
)` from `test`.`t1` group by `test`.`t1`.`b`) `y`

The problem here is this output "group by `t1`.`c`". The column `c` is unknown at that place in the query, so it makes no sense to qualify it with the table name t1.

This is related to MySQL Bug#37362 (http://bugs.mysql.com/bug.php?id=37362), from which the test case originates.

Before push of table elimination, the test case crashes the server, so the table elimination push fixes the crash, but produces wrong/strange output.

I will push the test case into lp:maria with wrong/strange result file (to not block the merge of MySQL 5.1.38). This needs to be updated after fixing the bug:

=== modified file 'mysql-test/r/subselect3.result'
— mysql-test/r/subselect3.result 2009-08-13 20:33:00 +0000
+++ mysql-test/r/subselect3.result 2009-09-16 11:27:55 +0000
@@ -864,7 +864,7 @@ Level Code Message
Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2
Note 1276 Field or reference 'test.t1.c' of SELECT #3 was resolved in SELECT #2
Error 1054 Unknown column 'c' in 'field list'
+Note 1003 select `c` AS `c` from (select (select count(`test`.`t1`.`a`) AS `COUNT(a)` from (select count(`test`.`t1`.`b`) AS `COUNT(b)` from `test`.`t1`) `x` group by `t1`.`c`) AS `(SELECT COUNT(a) FROM
-Note 1003 select `c` AS `c` from (select (select count(`test`.`t1`.`a`) AS `COUNT(a)` from (select count(`test`.`t1`.`b`) AS `COUNT(b)` from `test`.`t1`) `x` group by `c`) AS `(SELECT COUNT(a) FROM
(SELECT COUNT(b) FROM t1) AS x GROUP BY c
)` from `test`.`t1` group by `test`.`t1`.`b`) `y`
DROP TABLE t1;