| 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: | |
| 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 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.
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.