Bug #38489 EXPLAIN EXTENDED SELECT generates invalid query
Submitted: 31 Jul 2008 12:27 Modified: 8 Feb 2018 23:10
Reporter: Vladimir Kolesnikov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.0, 5.1 OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[31 Jul 2008 12:27] Vladimir Kolesnikov
Description:
Query produced by EXPLAIN EXTENDED SELECT/SHOW WARNINGS is invalid. I know sometimes invalid queries are produced for good reasons, (e.g. trigcond), but this is another case.

How to repeat:
create table t1 (a int not null auto_increment primary key);
insert into t1 values ();
insert into t1 values ();
insert into t1 values ();

mysql> explain extended select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 using ( a ) \G

...

mysql> show warnings \G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `test`.`t2`.`a` AS `a` from `test`.`t1` `t2` left join `test`.`t1` `t3` on(((`test`.`t3`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`a` =
`test`.`t2`.`a`))) join `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`a`)

1 row in set (0.00 sec)

mysql> select `test`.`t2`.`a` AS `a` from `test`.`t1` `t2` left join `test`.`t1` `t3` on(((`test`.`t3`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`a` = `
test`.`t2`.`a`))) join `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`a`);
ERROR 1054 (42S22): Unknown column 'test.t1.a' in 'on clause'
[31 Jul 2008 12:41] Susanne Ebrecht
Verified as described ... SHOW warning displays an invalid statement.
[8 Feb 2018 23:10] Roy Lyseng
Posted by developer:
 
EXPLAIN appears to generate a valid query in 5.6 and up.