| 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 | ||
| Category: | Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.0.40, 5.1, 5.2, 5.0 BK | OS: | Any |
| Assigned to: | Evgeny Potemkin | Target Version: | |
| Tags: | regression, qc | ||
| Triage: | Triaged: D4 (Minor) / R2 (Low) / E2 (Low) | ||
[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.
[20 Feb 2008 21:02]
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/42709 ChangeSet@1.2584, 2008-02-20 23:54:44+03:00, evgen@moonbone.local +10 -0 Bug#30302: Tables that were optimized away are printed in the EXPLAIN EXTENDED warning. Query optimizer searches for the constant tables and optimizes them away. This means that fields of such tables are substituted for their values and on later phases they are treated as constants. After this constant tables are removed from the query execution plan. Nevertheless constant tables were shown in the EXPLAIN EXTENDED warning thus producing query that might be not an equivalent of the original query. Now the print_join function skips all tables that were optimized away from printing to the EXPLAIN EXTENDED warning. If all tables were optimized away it produces the 'FROM dual' clause.
[6 Oct 2008 16:35]
Jeremy Cole
What's the status of this bug?
[28 Sep 2009 15:59]
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/84895 2841 Evgeny Potemkin 2009-09-28 Bug#30302: Tables that were optimized away are printed in the EXPLAIN EXTENDED warning. Query optimizer searches for the constant tables and optimizes them away. This means that fields of such tables are substituted for their values and on later phases they are treated as constants. After this constant tables are removed from the query execution plan. Nevertheless constant tables were shown in the EXPLAIN EXTENDED warning thus producing query that might be not an equivalent of the original query. Now the print_join function skips all tables that were optimized away from printing to the EXPLAIN EXTENDED warning. If all tables were optimized away it produces the 'FROM dual' clause. @ mysql-test/r/explain.result A test case added for the bug#30302. @ mysql-test/r/func_default.result Adjusted test case result after fix for the bug#30302. @ mysql-test/r/func_regexp.result Adjusted test case result after fix for the bug#30302. @ mysql-test/r/func_test.result Adjusted test case result after fix for the bug#30302. @ mysql-test/r/having.result Adjusted test case result after fix for the bug#30302. @ mysql-test/r/select.result Adjusted test case result after fix for the bug#30302. @ mysql-test/r/select_jcl6.result Adjusted test case result after fix for the bug#30302. @ mysql-test/r/subselect.result Adjusted test case result after fix for the bug#30302. @ mysql-test/r/subselect_no_mat.result Adjusted test case result after fix for the bug#30302. @ mysql-test/r/subselect_no_opts.result Adjusted test case result after fix for the bug#30302. @ mysql-test/r/subselect_no_semijoin.result Adjusted test case result after fix for the bug#30302. @ mysql-test/r/type_datetime.result Adjusted test case result after fix for the bug#30302. @ mysql-test/t/explain.test A test case added for the bug#30302. @ sql/sql_select.cc Bug#30302: Tables that were optimized away are printed in the EXPLAIN EXTENDED warning. Now the print_join function skips all tables that were optimized away from printing to the EXPLAIN EXTENDED warning. If all tables were optimized away it produces the 'FROM dual' clause. @ sql/table.h Adjusted test case result after fix for the bug#30302. The optimized_away flag is added to the TABLE_LIST struct.
[28 Sep 2009 16:24]
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/84896 3625 Evgeny Potemkin 2009-09-28 [merge] Auto-merged fix for the bug#30302.
[9 Oct 2009 8:47]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@ibmvm-20091009083208-0o0f0i9w1sq3c1kn) (version source revid:jon.hauglid@sun.com-20090929073341-4ev88lvoxsil8t41) (merge vers: 6.0.14-alpha) (pib:12)
[13 Oct 2009 13:46]
Paul DuBois
Noted in 6.0.14 changelog. For const tables that were optimized away EXPLAIN EXTENDED displayed them in the FROM clause. Now they are not displayed. If all tables are optimized away, FROM DUAL is displayed.
[19 Oct 2009 11:14]
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/87288 2901 Evgeny Potemkin 2009-10-19 Bug#30302: Tables that were optimized away are printed in the EXPLAIN EXTENDED warning. Query optimizer searches for the constant tables and optimizes them away. This means that fields of such tables are substituted for their values and on later phases they are treated as constants. After this constant tables are removed from the query execution plan. Nevertheless constant tables were shown in the EXPLAIN EXTENDED warning thus producing query that might be not an equivalent of the original query. Now the print_join function skips all tables that were optimized away from printing to the EXPLAIN EXTENDED warning. If all tables were optimized away it produces the 'FROM dual' clause. @ mysql-test/r/explain.result A test case added for the bug#30302. @ mysql-test/r/func_default.result Adjusted test case result after fix for the bug#30302. @ mysql-test/r/func_regexp.result Adjusted test case result after fix for the bug#30302. @ mysql-test/r/func_test.result Adjusted test case result after fix for the bug#30302. @ mysql-test/r/having.result Adjusted test case result after fix for the bug#30302. @ mysql-test/r/select.result Adjusted test case result after fix for the bug#30302. @ mysql-test/r/subselect.result Adjusted test case result after fix for the bug#30302. @ mysql-test/r/subselect3.result Adjusted test case result after fix for the bug#30302. @ mysql-test/r/type_datetime.result Adjusted test case result after fix for the bug#30302. @ mysql-test/t/explain.test A test case added for the bug#30302. @ sql/sql_select.cc Bug#30302: Tables that were optimized away are printed in the EXPLAIN EXTENDED warning. Now the print_join function skips all tables that were optimized away from printing to the EXPLAIN EXTENDED warning. If all tables were optimized away it produces the 'FROM dual' clause. @ sql/table.h Adjusted test case result after fix for the bug#30302. The optimized_away flag is added to the TABLE_LIST struct.
[19 Oct 2009 11:43]
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/87294 2901 Evgeny Potemkin 2009-10-19 [merge] Auto-merged fix for the bug#30302.
[31 Oct 2009 8:18]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091031081410-qkxmjsdzjmj840aq) (version source revid:serg@mysql.com-20091019190146-2g2j9i8aqhnd4a1u) (merge vers: 6.0.14-alpha) (pib:13)
[31 Oct 2009 17:28]
Paul DuBois
Setting report to NDI pending push to 5.5.x
[12 Nov 2009 8:19]
Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:mikael@mysql.com-20091102100915-a2nbfxaqprpgptfw) (merge vers: 5.5.0-beta) (pib:13)
[12 Nov 2009 18:56]
Paul DuBois
Noted in 5.5.0 changelog.
[11 Dec 2009 21:07]
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/93764 2944 Alexey Kopytov 2009-12-12 Streamlined the test case for bug #49199 in mysql-trunk-merge to take into account the changes introduced by the fix for bug #30302.
[19 Dec 2009 8:29]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091219082307-f3i4fn0tm8trb3c0) (version source revid:alik@sun.com-20091216181636-4z4tj1h72l0j6lk6) (merge vers: 6.0.14-alpha) (pib:15)
[19 Dec 2009 8:32]
Bugs System
Pushed into 5.5.1-m2 (revid:alik@sun.com-20091219082021-f34nq4jytwamozz0) (version source revid:alexey.kopytov@sun.com-20091211210654-y9l0u49361sfby1l) (merge vers: 5.5.0-beta) (pib:15)
[19 Dec 2009 8:37]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20091219082213-nhjjgmphote4ntxj) (version source revid:alik@sun.com-20091216180221-a5ps59gajad3pip9) (pib:15)
[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 May 2010 15:42]
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/108173 3168 Evgeny Potemkin 2010-05-12 Corrected fix for the bug#30302.
[16 Aug 2010 6:37]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:19]
Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
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.