Wrong output of EXPLAIN EXTENDED on subquery with unknown column error

[Kristian Nielsen]

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;

[Hakan Küçükyılmaz (hakan-askmonty)]

Kristian,

it looks like that MySQL Bug#37362 (http://bugs.mysql.com/bug.php?id=37362) is already fixed. Do we have this fix in MariaDB, too?

Thanks,

Hakan

[Kristian Nielsen (knielsen)]

I think we have the a for MySQL Bug#37362, since the query does not crash the server.

But note that this bug is about a different problem with the query, and it is not fixed in latest 5.2.

MySQL 5.1 does not have this bug.

[Sergey Petrunia (sergefp)]

I think that the importance of EXPLAIN/SHOW WARNINGs message for a query that produces error is very low.

[Sergey Petrunia (sergefp)]

Bug report says> 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.

I don't think this is the case. Let's look at the query again:

01 EXPLAIN EXTENDED SELECT c
02 FROM
03 (
04 SELECT
05 (SELECT COUNT(a)
06 FROM
07 (SELECT COUNT(b) FROM t1) AS x
08 GROUP BY c)
09 FROM
10 t1
11 GROUP BY b
12 ) AS y;

The "GROUP BY c" clause we're talking about is on line 8.
it belongs to the subquery that is located on lines 5-8
that subquery is a scalar-context subquery that is located in the select list of the subquery that is located on lines 4-11.
The subquery on lines 4-11 has a table named "t1", with column t1.c.
So, the "GROUP BY c" on line 8 refers to the t1.c of table t1 mentioned on line 10.

The "unknown column c" error is produced for the reference to column "c" made from line 1.

[Sergey Petrunia (sergefp)]

Didn't see anything wrong while investigating the above in debugger. I don't think anything that we observe here qualifies as a bug, or something that we'd like to fix.

Changing status to invalid.

[Kristian Nielsen (knielsen)]

Agree, I don't understand why I reported this as a bug, it looks correct.
I must have been confused by :-/