Bug #22331 EXPLAIN EXTENDED does not always return full query information
Submitted: 14 Sep 2006 1:01 Modified: 15 Mar 2007 3:24
Reporter: Nicholas Ring Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.26-BK, 5.0.24a OS:Linux (Linux, WinXP)
Assigned to: Evgeny Potemkin CPU Architecture:Any
Tags: explain, extended, Views

[14 Sep 2006 1:01] Nicholas Ring
Description:
(found during Arjen's Sydney "MySQL 5.0 for DBAs" training course)

EXPLAIN EXTENDED / SHOW WARNINGS does not always returns all of the query that will be executed.

How to repeat:
create view ant as select code, name, Continent from country where Continent = 'Antarctica';

select * from ant where code = 'ATF';

explain extended select * from ant where code = 'ATF';

show warnings;

-- Error in the where clause ---->
select `world`.`country`.`Code` AS `code`,`world`.`country`.`Name` AS `name`,`world`.`country`.`Continent` AS `Continent` from `world`.`country` where ();

As you see here, the where clause is shown as empty (which would be a syntax error even!) rather than showing the continent='antarctica' and code='atf'....
[16 Sep 2006 12:42] Valeriy Kravchuk
Thank you for a bug report. Verified just as described with 5.0.26-BK on Linux:

mysql> use world;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.26-debug |
+--------------+
1 row in set (0.00 sec)

mysql> create view ant as select code, name, Continent from `Country` where Con
tinent = 'Antarctica';
Query OK, 0 rows affected (0.01 sec)

mysql> select * from ant where code = 'ATF';
+------+-----------------------------+------------+
| code | name                        | Continent  |
+------+-----------------------------+------------+
| ATF  | French Southern territories | Antarctica |
+------+-----------------------------+------------+
1 row in set (0.00 sec)

mysql> explain extended select * from ant where code = 'ATF';
+----+-------------+---------+-------+---------------+---------+---------+------
-+------+-------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref
 | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+------
-+------+-------+
|  1 | PRIMARY     | Country | const | PRIMARY       | PRIMARY | 3       | const
 |    1 |       |
+----+-------------+---------+-------+---------------+---------+---------+------
-+------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `world`.`Country`.`Code` AS `code`,`world`.`Country`.`Name` AS `
name`,`world`.`Country`.`Continent` AS `Continent` from `world`.`Country` where
()
1 row in set (0.00 sec)
[6 Oct 2006 16:55] Sergei Golubchik
see also bug#21939
[8 Feb 2007 19:09] Evgeny Potemkin
Already fixed. Tested on 5.0.36.
[9 Feb 2007 11:17] Sergei Golubchik
Thank you for your bug report. This issue has already been fixed in the latest released version of that product
[20 Feb 2007 14:22] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/20184

ChangeSet@1.2411, 2007-02-20 17:19:50+03:00, evgen@moonbone.local +11 -0
  Bug#22331: EXPLAIN EXTENDED does not show conditions that were optimized away.
  
  During optimization stage WHERE and HAVING conditions can be changed or even
  be removed at all. Thus they aren't showed in the EXPLAIN EXTENDED which
  prints conditions after optimization.
  
  Now WHERE and HAVING conditions are printed from saved copies - prep_where
  and prep_having, if they are available. The copying is done before
  optimization and all conditions are printed as is.
[28 Feb 2007 21:23] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/20831

ChangeSet@1.2411, 2007-03-01 00:21:21+03:00, evgen@moonbone.local +3 -0
  Bug#22331: Wrong WHERE in EXPLAIN EXTENDED when all expressions were optimized
  away.
  
  During optimization stage WHERE and HAVING conditions can be changed or even
  be removed at all. Thus they aren't showed in the EXPLAIN EXTENDED which
  prints conditions after optimization.
  
  Now if all elements of an Item_cond were removed this Item_cond is substituted
  for an Item_int with the int value of the Item_cond.
[2 Mar 2007 12:10] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21013

ChangeSet@1.2411, 2007-03-02 15:07:18+03:00, evgen@moonbone.local +3 -0
  Bug#22331: Wrong WHERE in EXPLAIN EXTENDED when all expressions were optimized
  away.
  
  During optimization stage the WHERE conditions can be changed or even
  be removed at all. Thus they aren't showed in the EXPLAIN EXTENDED which
  prints conditions after optimization.
  
  Now if all elements of an Item_cond were removed this Item_cond is substituted
  for an Item_int with the int value of the Item_cond.
[5 Mar 2007 19:34] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21174

ChangeSet@1.2411, 2007-03-05 22:32:49+03:00, evgen@moonbone.local +10 -0
  Bug#22331: Wrong WHERE in EXPLAIN EXTENDED when all expressions were optimized
  away.
  
  During optimization stage the WHERE conditions can be changed or even
  be removed at all if they know for sure to be true of false. Thus they aren't
  showed in the EXPLAIN EXTENDED which prints conditions after optimization.
  
  Now if all elements of an Item_cond were removed this Item_cond is substituted
  for an Item_int with the int value of the Item_cond.
  If a condition found to be always true or false now it is substituted for
  an Item_int with the value of the condition.
[7 Mar 2007 18:46] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21407

ChangeSet@1.2411, 2007-03-07 21:44:58+03:00, evgen@moonbone.local +8 -0
  Bug#22331: Wrong WHERE in EXPLAIN EXTENDED when all expressions were optimized
  away.
  
  During optimization stage the WHERE conditions can be changed or even
  be removed at all if they know for sure to be true of false. Thus they aren't
  showed in the EXPLAIN EXTENDED which prints conditions after optimization.
  
  Now if all elements of an Item_cond were removed this Item_cond is substituted
  for an Item_int with the int value of the Item_cond.
  If there were conditions that were totally optimized away then values of the
  saved cond_value and having_value will be printed instead.
[7 Mar 2007 21:29] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21427

ChangeSet@1.2434, 2007-03-08 00:27:42+03:00, evgen@moonbone.local +3 -0
  sql_select.cc:
    Postfix for bug#22331 for windows platform.
  explain.test, explain.result:
    Cleanup after bugfix#22331.
[8 Mar 2007 23:52] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21547

ChangeSet@1.2435, 2007-03-08 19:38:21+03:00, evgen@moonbone.local +1 -0
  sql_select.cc:
    Postfix for bug#22331.
[8 Mar 2007 23:52] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21548

ChangeSet@1.2435, 2007-03-09 02:50:45+03:00, evgen@moonbone.local +15 -0
  Bug#22331: Wrong WHERE in EXPLAIN EXTENDED when all expressions were optimized
  away.
  
  Additional fix for bug#22331. Now Item_field prints its value in the case of
  the const field.
[9 Mar 2007 21:30] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21654

ChangeSet@1.2435, 2007-03-10 00:29:02+03:00, evgen@moonbone.local +12 -0
  Bug#22331: Wrong WHERE in EXPLAIN EXTENDED when all expressions were optimized
  away.
  
  Additional fix for bug#22331. Now Item_field prints its value in the case of
  the const field.
[12 Mar 2007 5:07] Igor Babaev
Pushed to 5.0.38, 5.1.17
[15 Mar 2007 3:24] Paul Dubois
Noted in 5.0.38, 5.1.17 changelogs.

EXPLAIN EXTENDED did not show WHERE conditions that were optimized
away.