MySQL Bugs: #21939: EXPLAIN EXTENDED does not return information "as advertised" in manual

Bug #21939	EXPLAIN EXTENDED does not return information "as advertised" in manual
Submitted:	31 Aug 2006 5:52	Modified:	5 Dec 2007 18:56
Reporter:	Arjen Lentz	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:	4.1 and up, 5.0.36-BK	OS:	Any (any)
Assigned to:		CPU Architecture:	Any
Tags:	explain, optimisation, optimiser, optimization, Optimizer

Description:
As I understand this command, it walks the Item tree after the optimizer is done with it, using the print methods to actually show what the tree looks like. Essentially, this would be like the "item tree dumper" I requested years ago.

From http://dev.mysql.com/doc/refman/5.0/en/explain.html
===
When the EXTENDED keyword is used, EXPLAIN produces extra information that can be viewed by issuing a SHOW WARNINGS  statement following the EXPLAIN statement. This information displays how the optimizer qualifies table and column names in the SELECT statement, what the SELECT looks like after the application of rewriting and optimization rules, and possibly other notes about the optimization process.
===

However, this does not appear to be what it actually shows.
It does not show const fields, it does not show changing join order.
These are just two easily recreated optimizations which I would expect and want/need to see visible.

It indeed appears to show how table and column names are qualified.
However, it does NOT appear to show the results of any rewriting/optimization rules, nor other notes about the optimization process.

How to repeat:
Example 1) not showing re-ordering of join tables:

EXPLAIN EXTENDED SELECT c.name FROM Country c JOIN City s ON c.code=s.country WHERE s.name='brisbane';
+----+-------------+-------+--------+---------------+---------+---------+-----------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref             | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+------+-------------+
|  1 | SIMPLE      | s     | ALL    | NULL          | NULL    | NULL    | NULL            | 4079 | Using where |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY       | PRIMARY | 3       | world.s.Country |    1 |             |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+------+-------------+

select `world`.`c`.`Name` AS `name` from `world`.`Country` `c` join `world`.`City` `s` where ((`world`.`c`.`Code` = `world`.`s`.`Country`) and (`world`.`s`.`name` = _latin1'brisbane'))

Example 2) not showing const optimization:

EXPLAIN EXTENDED SELECT c.name FROM Country c JOIN City s ON c.code=s.country WHERE c.code='AUS';

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | c     | const | PRIMARY       | PRIMARY | 3       | const |    1 |             |
|  1 | SIMPLE      | s     | ALL   | NULL          | NULL    | NULL    | NULL  | 4079 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

SHOW WARNINGS outputs:
select `world`.`c`.`Name` AS `name` from `world`.`Country` `c` join `world`.`City` `s` where ((`world`.`c`.`Code` = _latin1'AUS') and (`world`.`s`.`Country` = _latin1'AUS'))

Suggested fix:
Please fully implement what this command purports to do, as it would be VERY useful.

Thank you for a problem report.

Arjen, what do you mean "not showing const optimization" ?
It showed that the query

  ON c.code=s.country WHERE c.code='AUS'

was rewritten to

  WHERE Code = 'AUS' AND Country = 'AUS'

It looks to me like an example of constant propagation, do you mean some other "const optimization" here ?

As for "re-ordering of join tables" - indeed, it doesn't show it, but it's - as you noted - an "item tree dumper", and tables are not items, and they're not part of the item tree. Making EXLPAIN EXTENDED to show the join order is a feature request.

Sergei, you're right in it does indeed show the const optimisation in some sense, that it's put in the where clause and there's no longer a join condition in the where clause.
However, as I understand it, the optimiser actually picks up the data for the const row and replaces the entries in the query so the underlying table is no longer referenced. That is not reflected in the output.

So apart from join order are not being displayed, a table that's no longer referenced (since it's been replaced by constants) *is* displayed. There's also another bug report on it not actually displaying a where clause at all in certain situation (just shows () instead) perhaps the two issues can be sorted out together.

I appreciate that internally it may be true that a table is not part of the item tree, but that's quite irrelevant to a user. They want to see what's going on, this feature purports to do that, and does not.
It serves no purpose to debate architectural semantics, wouldn't you agree? ;-)

see also bug#22331

- This entry cannot be considered as a request as there is no specification
  of the required feature.
  
By the above reason I move the case to 'Not a bug'.

Hi Igor, thanks for your feedback.

The report comes from user experience, with EXPLAIN not actually being clear about what is going on.
The problem is described fairly well, I thought - Sergei also picked up on it and some other aspects were already fixed in a related bug report.
Now, how to make the unclear info clearer, there are various possibilities - you're the expert and a developer, coming up with solutions is probably your gig, right? I'd be happy to toss in some ideas, however I am not familiar with this part of the code so my suggestions may be unpractical.

In any case, given the bug title, I would guess that either EXPLAIN EXTENDED is adjusted, and/or the documention changed. Marking it as "not a bug" does not seem appropriate, as that would mean that the documentation still says stuff that is not actually correct. Thanks.

Based on Igor's explanation and Arjen's comment, I think, this is a valid documentation request/bug for 5.0 and 4.1, and/or a feature request for some future version, > 5.1.