Details
-
Type:
Task
-
Status: Closed
-
Priority:
Minor
-
Resolution: Not a Bug
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
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;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Wrong output of EXPLAIN EXTENDED on subquery with unknown column error
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.
Re: Wrong output of EXPLAIN EXTENDED on subquery with unknown column error
I think that the importance of EXPLAIN/SHOW WARNINGs message for a query that produces error is very low.
Re: Wrong output of EXPLAIN EXTENDED on subquery with unknown column error
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.
Re: Wrong output of EXPLAIN EXTENDED on subquery with unknown column error
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.
Re: Wrong output of EXPLAIN EXTENDED on subquery with unknown column error
Agree, I don't understand why I reported this as a bug, it looks correct.
I must have been confused by :-/
Launchpad bug id: 430669
Re: Wrong output of EXPLAIN EXTENDED on subquery with unknown column error
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