| Bug #30302 | Query from EXPLAIN EXTENDED .. SHOW WARNINGS returns different result | ||
|---|---|---|---|
| Submitted: | 8 Aug 2007 12:41 | Modified: | 17 May 2010 16:21 |
| Reporter: | Baron Schwartz (Basic Quality Contributor) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.0.40, 5.1, 5.2, 5.0 BK | OS: | Any |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | qc, regression | ||
[8 Aug 2007 22:58]
Sveta Smirnova
Thank you for the report. Verified as described using our sakila database. Version 4.1 is not affected.
[9 Aug 2007 17:37]
Sergei Golubchik
Note that WHERE should not be shown, it was optimized away. the SELECT list makes it clear that values were substituted with constants. The bug is that FROM clause is shown, while it should not be - the table was optimized away.
[6 Oct 2008 16:35]
Jeremy Cole
What's the status of this bug?
[16 Feb 2010 20:50]
Sergey Petrunya
There seems to be two problems with the fix:
~/dev/mysql-next-mr/sql$ grep -rin '\<optimized_away\>' *.h *.cc
table.h:1495: bool optimized_away;
sql_select.cc:2737: s->table->pos_in_table_list->optimized_away= TRUE;
sql_select.cc:16851: if (!t->optimized_away)
sql_select.cc:16867: if (tmp->optimized_away)
1. optimized_away is set to TRUE in one location, where 0 or 1-record constant tables are read. Why is it not set where we read constant tables that are constant because of the presence of tbl.primary_key=const clause ?
2. 1-record constant table may be not constant anymore on the next execution of the prepared statement. TABLE_LIST elements survive across prepared statement execution, this means that after we have done
pos_in_table_list->optimized_away=TRUE
it will remain true for all subsequent PS re-executions, even if the table will cease to be constant.
[12 Mar 2013 3:13]
MySQL Verification Team
this bug has never actually been fixed! mysql> create table t(a int primary key)engine=innodb; Query OK, 0 rows affected (2.63 sec) mysql> insert into t values (1),(2),(3); Query OK, 3 rows affected (0.20 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> explain extended select a from t where a=1; +----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | t | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index | +----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> show warnings; +-------+------+----------------------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------------------+ | Note | 1003 | /* select#1 */ select '1' AS `a` from `test`.`t` where 1 | +-------+------+----------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select version(); +-----------+ | version() | +-----------+ | 5.6.10 | +-----------+ 1 row in set (0.00 sec)
Description: The query returned by EXPLAIN EXTENDED ... SHOW WARNINGS doesn't show the WHERE clause on a 'const' query. How to repeat: This query will return a single row, single column: mysql> explain extended select film_id from sakila.film where film_id = 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: film type: const possible_keys: PRIMARY key: PRIMARY key_len: 2 ref: const rows: 1 Extra: Using index 1 row in set, 1 warning (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: select '1' AS `film_id` from `sakila`.`film` where 1 1 row in set (0.00 sec) The query shown in the message will return 1000 rows. The two queries aren't equivalent.