[MDEV-3791] LP:430669 - Wrong output of EXPLAIN EXTENDED on subquery with unknown column error Created: 2009-09-16  Updated: 2012-10-04  Resolved: 2012-10-04

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

Type: Task Priority: Minor
Reporter: Kristian Nielsen Assignee: Sergei Petrunia
Resolution: Not a Bug Votes: 0
Labels: Launchpad, MariaDB_5.1

Attachments: XML File LPexportBug430669.xml    

 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;

 Comments   
Comment by Hakan Küçükyılmaz (Inactive) [ 2010-05-26 ]

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

Comment by Kristian Nielsen [ 2010-06-08 ]

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.

Comment by Sergei Petrunia [ 2011-06-15 ]

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.

Comment by Sergei Petrunia [ 2011-06-15 ]

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.

Comment by Sergei Petrunia [ 2011-06-15 ]

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.

Comment by Rasmus Johansson (Inactive) [ 2011-06-16 ]

Launchpad bug id: 430669

Comment by Kristian Nielsen [ 2011-06-16 ]

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 :-/

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