MySQL Bugs: #48134: EXPLAIN EXTENDED + SHOW WARNINGS not executable

Bug #48134	EXPLAIN EXTENDED + SHOW WARNINGS not executable
Submitted:	18 Oct 2009 12:48	Modified:	14 May 2012 17:38
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.1.39, 5.1.41, 5.4.3	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	qc

Description:
EXPLAIN EXTENDED + SHOW WARNINGS dos not (always) return an executable statement. Instead some symblics enclosed in <brackets> referring server/optimizer internals occur.  This is stated  with a SELECT .. WHERE IN (SELECT ...) .. but other types of (sub)queries may be affected.

How to repeat:
CREATE TABLE `parent` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `blah` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `child` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pointsout` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_child` (`pointsout`),
  CONSTRAINT `FK_child` FOREIGN KEY (`pointsout`) REFERENCES `parent` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Optimized output (formatted a little) from EXPLAIN EXTENDED + SHOW WARNINGS

1) SELECT id FROM child WHERE pointsout IN (SELECT id FROM parent):
select `fktest`.`child`.`id` AS `id`
from `fktest`.`child`
where  < in_optimizer > (`fktest`.`child`.`pointsout`, < exists > ( < primary_index_lookup > ( < cache > (`fktest`.`child`.`pointsout`)in parent on PRIMARY)))

2) SELECT id FROM child WHERE pointsout = (SELECT id FROM parent):
select `fktest`.`child`.`id` AS `id`
from `fktest`.`child`
where (`fktest`.`child`.`pointsout` 
  = (select `fktest`.`parent`.`id` AS `id`
     from `fktest`.`parent`))

3) SELECT child.id FROM child, parent WHERE child.pointsout = parent.id:
select `fktest`.`child`.`id` AS `id`
from `fktest`.`child`
  join `fktest`.`parent`
where (`fktest`.`parent`.`id` = `fktest`.`child`.`pointsout`)
(and it is same with the full JOIN syntax (" .. JOIN .. = ...")

Suggested fix:
Always return an executable statement.

I checked several documentation pages including:
http://dev.mysql.com/doc/refman/5.1/en/using-explain.html
http://dev.mysql.com/doc/refman/5.1/en/explain.html

.. and I do not think I found a proper reason and/or explanation for this.

If it matters I am using the built-in InnoDB - not the plugin.

I had verified that your example #1 had produced some code that is not executable "as is":

mysql> explain extended  SELECT id FROM child WHERE pointsout IN (SELECT id FROM parent)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: child
         type: index
possible_keys: NULL
          key: FK_child
      key_len: 5
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: parent
         type: unique_subquery
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: func
         rows: 1
     filtered: 100.00
        Extra: Using index
2 rows in set, 1 warning (0.36 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `test`.`child`.`id` AS `id` from `test`.`child` where <in_optimizer>(`test`.`child`.`pointsout`,<exists>(<primary_index_lookup>(<cache>(`test`.`child`.`pointsout`) in parent on PRIMARY)))
1 row in set (0.00 sec)

mysql> select `test`.`child`.`id` AS `id` from `test`.`child` where <in_optimizer>(`test`.`child`.`pointsout`,<exists>(<primary_index_lookup>(<cache>(`test`.`child`.`pointsout`) in parent on PRIMARY)));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<in_optimizer>(`test`.`child`.`pointsout`,<exists>(<primary_index_lookup>(<cache' at line 1

But I do *not* see any text in the manual that claims that the results of SHOW WARNINGS after EXPLAIN EXTENDED should be always executable as is. So, this is the feature request at best.

Also neither LEFT JOIN or RIGHT JOIN will return such <symbolics>.

However http://dev.mysql.com/doc/refman/5.1/en/using-explain.html says 

"EXPLAIN EXTENDED also displays the filtered column as of MySQL 5.1.12."

.. but if this is the meaning of this passage I really request an elaboration of 
* when such <symbolics> occur
* the exact meaning of each

Maybe it is a feature request but there is no information about when such occur and what every possible <symbolics> exactly mean. It looks too much like a 'hack' to me. At least this should be documented to the same degree as the 'extra' column in plain EXPLAIN.  

But I would prefer a 'clean' output telling what is actually executed in an executable form.  If I strip the <symbolics> it does not execute (and then of course /*comment*/ could have been used instead).

Anyway .. the optimizer team probably has something to say here too!

I agree that all these:

<exists>
<primary_index_lookup>
<cache>

and other terms of EXPLAIN EXTENDED results should be explained in the manual.

Also a subquery in the FROM clause does not use such <symbolics>:

example: 
SELECT child.id, derived.id FROM child, (SELECT * FROM parent) AS derived;

Optimized:
select `fktest`.`child`.`id` AS `id`, '0' AS `id`
from `fktest`.`child`
  join (select `fktest`.`parent`.`id` AS `id`, `fktest`.`parent`.`blah` AS `blah`
        from `fktest`.`parent`) `derived`

Actually I request a little more than that.  

*is this intended behaviour?
*except for WHERE IN what other constructions are affected?

I would find it fine if 'hints' were added as /*comments*/ inside an executabel statement, but I still do no see why one particular statement should be affected (ie. returning non-executable statement) as almost identical statements are not.

So I do not necessarily agree with reclassification from 'optimizer' to 'docs'. But anyway, let optimizer team explain!

Will be covered by WL#5144.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

http://dev.mysql.com/doc/refman/5.5/en/explain-extended.html:

"
Because the statement produced as output may contain special markers to provide information about query rewriting or optimizer actions, the statement is not necessarily valid SQL and is not intended to be executed.
"