Bug #31563 Semi-joins: pullout of functionally-dependent tables is not transitive
Submitted: 12 Oct 2007 12:08 Modified: 20 Nov 2010 2:07
Reporter: Sergey Petrunya Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.2 OS:Any
Assigned to: Sergey Petrunya CPU Architecture:Any
Tags: pullout, semi-join, subqueries, subquery

[12 Oct 2007 12:08] Sergey Petrunya
Description:
The process of pull-out of functionally-dependent tables out of a semi-join doesn't pull out inner tables that are functionally dependent on other inner tables, which are, in turn, functionally dependent on the outer tables.

How to repeat:
Run this:

create table t1 (a int not null, b int, primary key (a));
create table t2 (a int not null, primary key (a));
create table t3 (a int not null, b int, primary key (a));
insert into t1 values (1,10), (2,20), (3,30),  (4,40);
insert into t2 values (2), (3), (4), (5);
insert into t3 values (10,3), (20,4), (30,5);

#
# In this query: 
#   t1 is functionally dependent on t2
#   t3 is functionally dependent on t1 (but w/o direct dependency on t2)
# this means that semi-join can be just converted to inner join.
#
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);

#
# Yet in EXPLAIN we see that no conversion took place (check "First Match" 
# in the EXPLAIN and "semi join" in SHOW WARNINGS:
#

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t2
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 75.00
        Extra: Using where; Using join buffer
*************************** 3. row ***************************
           id: 1
  select_type: PRIMARY
        table: t3
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: j993.t1.b
         rows: 1
     filtered: 100.00
        Extra: Using index; FirstMatch(t1)
3 rows in set, 1 warning (1.30 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `j993`.`t2`.`a` AS `a` from `j993`.`t2` semi join (`j993`.`t1` join `j993`.`t3`) where ((`j993`.`t1`.`a` = `j993`.`t2`.`a`) and (`j993`.`t3`.`a` = `j993`.`t1`.`b`))
1 row in set (0.01 sec)

Suggested fix:
Table pullout was designed to be transitive (see  http://forge.mysql.com/worklog/task.php?id=3740), make it actually work.
[12 Oct 2007 13:46] 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/35475

ChangeSet@1.2599, 2007-10-12 17:46:21+04:00, sergefp@mysql.com +3 -0
  BUG#31563: Semi-joins: pullout of functionally-dependent tables is not transitive
  Make eq_ref-based table pull-out be transitive.
[29 Oct 2007 8:50] Bugs System
Pushed into 6.0.4-alpha
[13 Nov 2007 19:24] Paul Dubois
Noted in 6.0.4 changelog.
[28 May 2008 10:01] Bugs System
Pushed into 6.0.6-alpha
[16 Aug 2010 6:34] 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:23] 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)
[20 Nov 2010 2:07] Paul Dubois
Noted in 5.6.1 changelog.
[23 Nov 2010 2:06] Paul Dubois
Correction: No 5.6.1 changelog entry. Bug does not appear in any released 5.6.x version.