| Bug #35160 | Subquery optimization: table pullout is not reflected in EXPLAIN EXTENDED | ||
|---|---|---|---|
| Submitted: | 8 Mar 2008 3:43 | Modified: | 23 Nov 2010 3:33 |
| Reporter: | Sergey Petrunya | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 6.0-bk | OS: | Any |
| Assigned to: | Sergey Petrunya | CPU Architecture: | Any |
[26 Mar 2008 12:51]
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/44447 ChangeSet@1.2591, 2008-03-26 15:49:01+03:00, sergefp@mysql.com +5 -0 BUG#35160 "Subquery optimization: table pullout is not reflected in EXPLAIN EXTENDED" - When we pull the tables out of a semi-join, re-attach them to the embedding join nest's NESTED_JOIN structure. This will cause the pull out to show up in EXPLAIN EXTENDED;SHOW WARNINGS output.
[28 May 2008 10:01]
Bugs System
Pushed into 6.0.6-alpha
[30 May 2008 18:59]
Paul DuBois
Noted in 6.0.6 changelog. The table pullout strategy was not reflected in EXPLAIN EXTENDED output if not all of the subquery tables were pulled out.
[16 Aug 2010 6:39]
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:26]
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)
[23 Nov 2010 3:33]
Paul DuBois
Bug does not appear in any released 5.6.x version. No 5.6.1 changelog entry needed.
Description: Table pullout strategy is not reflected in EXPLAIN EXTENDED if not all of the subquery tables were pulled out. How to repeat: Run this: create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4); create table t1 (a int, b int, key(a)); insert into t1 select a,a from t0; create table t2 (a int, b int, primary key(a)); insert into t2 select * from t1; -- Table t2 should be pulled out because t2.a=t0.a equality explain extended select * from t0 where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and t1.b=t2.b)\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: t0 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 5 filtered: 100.00 Extra: *************************** 2. row *************************** id: 1 select_type: PRIMARY table: t1 type: ref possible_keys: a key: a key_len: 5 ref: j101.t0.a rows: 1 filtered: 100.00 Extra: FirstMatch(t0) *************************** 3. row *************************** id: 1 select_type: PRIMARY table: t2 type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: j101.t0.a rows: 1 filtered: 100.00 Extra: Using where 3 rows in set, 2 warnings (0.00 sec) -- The EXPLAIN shows that it actually was pulled out -- However, SHOW WARNINGS still shows t2 on the inner side of the semi-join -- operation: mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1276 Message: Field or reference 'j101.t0.a' of SELECT #2 was resolved in SELECT #1 *************************** 2. row *************************** Level: Note Code: 1003 Message: select `j101`.`t0`.`a` AS `a` from `j101`.`t0` semi join (`j101`.`t1` join `j101`.`t2`) where ((`j101`.`t2`.`b` = `j101`.`t1`.`b`) an d (`j101`.`t1`.`a` = `j101`.`t0`.`a`) and (`j101`.`t2`.`a` = `j101`.`t0`.`a`)) 2 rows in set (0.00 sec)