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:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:6.0-bk OS:Any
Assigned to: Sergey Petrunya CPU Architecture:Any
Triage: D3 (Medium)

[8 Mar 2008 3:43] Sergey Petrunya
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)
[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.