| Bug #44932 | EXPLAIN EXTENDED wrong output of SHOW WARNINGS | ||
|---|---|---|---|
| Submitted: | 18 May 2009 12:47 | Modified: | 17 Feb 2014 18:40 |
| Reporter: | Peter Laursen (Basic Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.1.34, 5.1.36-bzr | OS: | Any (MS Win7 RC - 64 bit, Linux) |
| Assigned to: | Paul DuBois | CPU Architecture: | Any |
| Tags: | qc | ||
[18 May 2009 12:50]
Peter Laursen
Corrected typo in synopsis!
[18 May 2009 18:31]
Valeriy Kravchuk
Thank you for the problem report. Verified just as described with recent 5.1.36 from bzr on Mac OS X: mysql> SELECT t1_id FROM v_t1_t2 WHERE t2_id = 1; +-------+ | t1_id | +-------+ | 1 | | 2 | | 3 | +-------+ 3 rows in set (0.00 sec) mysql> explain extended SELECT t1_id FROM v_t1_t2 WHERE t2_id = 1; +----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | t2 | const | PRIMARY | PRIMARY | 2 | const | 1 | 100.00 | Using index | | 1 | SIMPLE | t1 | index | NULL | PRIMARY | 2 | NULL | 3 | 100.00 | Using index | +----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec) Index is used, by the way... mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: select `test`.`t1`.`id` AS `t1_id` from `test`.`t1` join `test`.`t2` where 1 1 row in set (0.00 sec) mysql> select `test`.`t1`.`id` AS `t1_id` from `test`.`t1` join `test`.`t2` where 1; +-------+ | t1_id | +-------+ | 1 | | 2 | | 3 | | 1 | | 2 | | 3 | | 1 | | 2 | | 3 | +-------+ 9 rows in set (0.00 sec) mysql> select version(); +--------------+ | version() | +--------------+ | 5.1.36-debug | +--------------+ 1 row in set (0.00 sec)
[28 Sep 2009 8:47]
Sveta Smirnova
Bug #47669 was marked as duplicate of this one.
[20 Dec 2009 13:23]
Georgi Kodinov
Another case of such behavior : +CREATE TABLE t1 (a VARCHAR(10), FULLTEXT KEY a (a)); +EXPLAIN EXTENDED +SELECT a FROM t1 +UNION +SELECT a FROM t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found +2 UNION t1 system NULL NULL NULL NULL 0 0.00 const row not found +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 select '' AS `a` from `test`.`t1` union select '' AS `a` from `test`.`t1`
[17 Feb 2014 18:40]
Paul DuBois
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.
[17 Feb 2014 18:44]
Paul DuBois
From the optimizer team:
-----------------------------
mysql> EXPLAIN EXTENDED SELECT t2.id FROM t2 WHERE t2.id=3;
+----+-------------+-------+------------+-------+---------------+---------+---
------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key |
key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---
------+-------+------+----------+-------------+
| 1 | SIMPLE | t2 | NULL | const | PRIMARY | PRIMARY | 2
| const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---
------+-------+------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
mysql> show warnings;
+---------+------+------------------------------------------------------------
-------+
| Level | Code | Message
|
+---------+------+------------------------------------------------------------
-------+
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future
release. |
| Note | 1003 | /* select#1 */ select '3' AS `id` from `d2`.`t2` where 1
|
+---------+------+------------------------------------------------------------
-------+
2 rows in set (0.00 sec)
Case 1:
--------
mysql> EXPLAIN EXTENDED SELECT t1.id FROM t1,t2 WHERE t2.id=3 and t1.id=2;
+----+-------------+-------+------------+-------+---------------+---------+---
------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key |
key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---
------+-------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | const | PRIMARY | PRIMARY | 2
| const | 1 | 100.00 | Using index |
| 1 | SIMPLE | t2 | NULL | const | PRIMARY | PRIMARY | 2
| const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---
------+-------+------+----------+-------------+
2 rows in set, 2 warnings (10.87 sec)
mysql> show warnings;
+---------+------+------------------------------------------------------------
-----------------+
| Level | Code | Message
|
+---------+------+------------------------------------------------------------
-----------------+
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future
release. |
| Note | 1003 | /* select#1 */ select '2' AS `id` from `test`.`t1` join
`test`.`t2` where 1 |
+---------+------+------------------------------------------------------------
-----------------+
2 rows in set (0.00 sec)
[17 Feb 2014 18:45]
Paul DuBois
More:
Case 2:
--------
mysql> EXPLAIN EXTENDED SELECT t1.id FROM t1,t2 WHERE t2.id=3 and t1.id>2;
+----+-------------+-------+------------+-------+---------------+---------+---
------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key |
key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---
------+-------+------+----------+--------------------------+
| 1 | SIMPLE | t2 | NULL | const | PRIMARY | PRIMARY | 2
| const | 1 | 100.00 | Using index |
| 1 | SIMPLE | t1 | NULL | range | PRIMARY | PRIMARY | 2
| NULL | 1 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---
------+-------+------+----------+--------------------------+
2 rows in set, 2 warnings (0.00 sec)
mysql> show warnings;
+---------+------+------------------------------------------------------------
-----------------------------------------------------+
| Level | Code | Message
|
+---------+------+------------------------------------------------------------
-----------------------------------------------------+
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future
release. |
| Note | 1003 | /* select#1 */ select `test`.`t1`.`id` AS `id` from
`test`.`t1` join `test`.`t2` where ((`test`.`t1`.`id` > 2)) |
+---------+------+------------------------------------------------------------
-----------------------------------------------------+
2 rows in set (0.00 sec)
Case 3:
-------
mysql> EXPLAIN EXTENDED SELECT t1.id FROM t1,t2 WHERE t2.id=3 or t1.id>2;
+----+-------------+-------+------------+-------+---------------+---------+---
------+------+------+----------+----------------------------------------
-------------------------+
| id | select_type | table | partitions | type | possible_keys | key |
key_len | ref | rows | filtered | Extra
|
+----+-------------+-------+------------+-------+---------------+---------+---
------+------+------+----------+----------------------------------------
-------------------------+
| 1 | SIMPLE | t1 | NULL | index | PRIMARY | PRIMARY | 2
| NULL | 3 | 100.00 | Using index
|
| 1 | SIMPLE | t2 | NULL | index | PRIMARY | PRIMARY | 2
| NULL | 3 | 100.00 | Using where; Using index; Using join buffer
(Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+---------+---
------+------+------+----------+----------------------------------------
-------------------------+
2 rows in set, 2 warnings (0.00 sec)
mysql> show warnings;
+---------+------+------------------------------------------------------------
------------------------------------------------------------------------
-------+
| Level | Code | Message
|
+---------+------+------------------------------------------------------------
------------------------------------------------------------------------
-------+
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future
release.
|
| Note | 1003 | /* select#1 */ select `test`.`t1`.`id` AS `id` from
`test`.`t1` join `test`.`t2` where ((`test`.`t2`.`id` = 3) or
(`test`.`t1`.`id` > 2)) |
+---------+------+------------------------------------------------------------
------------------------------------------------------------------------
-------+
2 rows in set (0.00 sec)
[17 Feb 2014 18:45]
Paul DuBois
More:
Case 4:
-------
mysql> EXPLAIN EXTENDED SELECT t1.id FROM t1,t2 WHERE t2.id=3 or t1.id=2;
+----+-------------+-------+------------+-------+---------------+---------+---
------+------+------+----------+----------------------------------------
-------------------------+
| id | select_type | table | partitions | type | possible_keys | key |
key_len | ref | rows | filtered | Extra
|
+----+-------------+-------+------------+-------+---------------+---------+---
------+------+------+----------+----------------------------------------
-------------------------+
| 1 | SIMPLE | t1 | NULL | index | PRIMARY | PRIMARY | 2
| NULL | 3 | 100.00 | Using index
|
| 1 | SIMPLE | t2 | NULL | index | PRIMARY | PRIMARY | 2
| NULL | 3 | 100.00 | Using where; Using index; Using join buffer
(Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+---------+---
------+------+------+----------+----------------------------------------
-------------------------+
2 rows in set, 2 warnings (0.00 sec)
mysql> show warnings;
+---------+------+------------------------------------------------------------
------------------------------------------------------------------------
-------+
| Level | Code | Message
|
+---------+------+------------------------------------------------------------
------------------------------------------------------------------------
-------+
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future
release.
|
| Note | 1003 | /* select#1 */ select `test`.`t1`.`id` AS `id` from
`test`.`t1` join `test`.`t2` where ((`test`.`t2`.`id` = 3) or
(`test`.`t1`.`id` = 2)) |
+---------+------+------------------------------------------------------------
------------------------------------------------------------------------
-------+
2 rows in set (0.00 sec)
Case 5:
-------
mysql> EXPLAIN EXTENDED SELECT t1.id FROM t1,t2 WHERE t1.id<2;
+----+-------------+-------+------------+-------+---------------+---------+---
------+------+------+----------+----------------------------------------
------------+
| id | select_type | table | partitions | type | possible_keys | key |
key_len | ref | rows | filtered | Extra
|
+----+-------------+-------+------------+-------+---------------+---------+---
------+------+------+----------+----------------------------------------
------------+
| 1 | SIMPLE | t1 | NULL | range | PRIMARY | PRIMARY | 2
| NULL | 1 | 100.00 | Using where; Using index
|
| 1 | SIMPLE | t2 | NULL | index | NULL | PRIMARY | 2
| NULL | 3 | 100.00 | Using index; Using join buffer (Block Nested
Loop) |
+----+-------------+-------+------------+-------+---------------+---------+---
------+------+------+----------+----------------------------------------
------------+
2 rows in set, 2 warnings (0.00 sec)
mysql> show warnings;
+---------+------+------------------------------------------------------------
---------------------------------------------------+
| Level | Code | Message
|
+---------+------+------------------------------------------------------------
---------------------------------------------------+
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future
release. |
| Note | 1003 | /* select#1 */ select `test`.`t1`.`id` AS `id` from
`test`.`t1` join `test`.`t2` where (`test`.`t1`.`id` < 2) |
+---------+------+------------------------------------------------------------
---------------------------------------------------+
2 rows in set (0.00 sec)
------When we use the equality as one of conditions with 'AND' operation:
case | format | output of WARNINGS
-------+-------------------------+-------------------
case 0 | equality | where 1
Case 1 | equality AND equality | where 1
Case 2 | equality AND inequality | (`test`.`t1`.`id` > 2)
Case 3 | equality OR inequality | ((`test`.`t2`.`id` = 3) or
(`test`.`t1`.`id` > 2))
Case 4 | equality OR equality | ((`test`.`t2`.`id` = 3) or
(`test`.`t1`.`id` = 2))
Case 5 | inequality | (`test`.`t1`.`id` < 2)
-------+-------------------------+--------------------
Analyze:
--------
What happens here is that the optimizer determines that the table t2 is
const.
The single qualifying row of t2 is read, the value column t2_id is
substituted
with its real value (1) and the equality "t2_id = 1" can be transformed to "1
= 1", which is further replaced with TRUE. Hence, the EXPLAIN output is
correct, given that table t2 is pre-read as const values.
Fix suggestion:
---------------
We think this is not a bug. EXPLAIN operates on the query after const
substitution. However, we should probably warn about this more explicitly. My
suggestion is that we change this to a doc bug and ask that the following
note
is added to chapter 8.8.3:
"When some tables are of const or system type, expressions involving columns
from these tables are evaluated early by the optimizer and will not be part
of
the displayed statement. Notice however that with FORMAT=JSON, some const
table accesses will be displayed as a "ref" access that uses a const value."
Description: 1) execute SELECT from a VIEW 2) EXPLAIN EXTENDED (statement from above) + SHOW WARNINGS 3) EXECUTE the optimized statement from 2) above 1) and 3) returns different results. How to repeat: DROP TABLE IF EXISTS t1; CREATE TABLE `t1` ( `id` SMALLINT(6) NOT NULL DEFAULT '99', `txt` TEXT, PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8; INSERT INTO t1 (id,txt) VALUES (1,'a'), (2,'b'), (3,'c'); DROP TABLE IF EXISTS t2; CREATE TABLE `t2` ( `id` SMALLINT(6) NOT NULL DEFAULT '99', `txt` TEXT, PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8; INSERT INTO t2 (id,txt) VALUES (1,'x'), (2,'y'), (3,'x'); DROP VIEW IF EXISTS v_t1_t2; CREATE VIEW v_t1_t2 AS SELECT t1.id AS t1_id, t1.txt AS t1_txt, t2.id AS t2_id, t2.txt AS t2_txt FROM t1, t2; SELECT * FROM v_t1_t2; /* the VIEW contains a cartesian product of the tables t1_id t1_txt t2_id t2_txt ------ ------ ------ ------ 1 a 1 x 2 b 1 x 3 c 1 x 1 a 2 y 2 b 2 y 3 c 2 y 1 a 3 x 2 b 3 x 3 c 3 x */ EXPLAIN EXTENDED SELECT t1_id FROM v_t1_t2; /* id select_type table type possible_keys key key_len ref rows Extra ------ ----------- ------ ------ ------------- ------- ------- ------ ------ ------------------------------ 1 SIMPLE t1 index (NULL) PRIMARY 2 (NULL) 3 Using index 1 SIMPLE t2 index (NULL) PRIMARY 2 (NULL) 3 Using index; Using join buffer */ SHOW WARNINGS; /* query is optimixed to a JOIN Level Code Message ------ ------ -------------------------------------------------------------------- Note 1003 select `test`.`t1`.`id` AS `t1_id` from `test`.`t1` join `test`.`t2` */ -- now we will add a WHERE-clause SELECT t1_id FROM v_t1_t2 WHERE t2_id = 1; -- returns 3 rows as expected EXPLAIN EXTENDED SELECT t1_id FROM v_t1_t2 WHERE t2_id = 1; /* id select_type table type possible_keys key key_len ref rows filtered Extra ------ ----------- ------ ------ ------------- ------- ------- ------ ------ -------- ----------- 1 SIMPLE t2 const PRIMARY PRIMARY 2 const 1 100.00 Using index 1 SIMPLE t1 ALL (NULL) (NULL) (NULL) (NULL) 3 100.00 .. btw a sideremark: why now not index used on t1? */ SHOW WARNINGS; /* Level Code Message ------ ------ ---------------------------------------------------------------------------- Note 1003 select `test`.`t1`.`id` AS `t1_id` from `test`.`t1` join `test`.`t2` where 1 */ -- now execute the optimized statement! SELECT `test`.`t1`.`id` AS `t1_id` FROM `test`.`t1` JOIN `test`.`t2` WHERE 1; -- returns 9 rows Suggested fix: In my understanding it must be a bug that the optimized statement returned does not return the same as the original statement!