Bug #38489 EXPLAIN EXTENDED SELECT generates invalid query

• Submitted: 31 Jul 2008 12:27
• Modified: 8 Feb 2018 23:10
• Reporter: Vladimir Kolesnikov
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.0, 5.1
• OS: Any
• CPU Architecture: Any

[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.