#At file:///home/tsk/mprog/src/5.3-mwl68-merge.base-mwl68/ based on revid:timour@askmonty.org-20100309101406-xygkt2sgftvjvevg 2766 timour@askmonty.org 2010-03-09 [merge] MWL#68 Subquery optimization: Efficient NOT IN execution with NULLs Automerge with 5.3-subqueries modified: mysql-test/r/join_cache.result mysql-test/r/subselect_sj.result mysql-test/r/subselect_sj2.result mysql-test/r/subselect_sj2_jcl6.result mysql-test/r/subselect_sj_jcl6.result mysql-test/suite/pbxt/r/group_min_max.result mysql-test/suite/pbxt/r/subselect.result mysql-test/t/join_cache.test mysql-test/t/subselect_sj.test mysql-test/t/subselect_sj2.test mysql-test/t/subselect_sj_jcl6.test sql/item.cc sql/opt_subselect.cc sql/sql_join_cache.cc sql/sql_select.cc sql/sql_select.h === modified file 'mysql-test/r/join_cache.result' --- a/mysql-test/r/join_cache.result 2010-02-11 21:59:32 +0000 +++ b/mysql-test/r/join_cache.result 2010-03-06 19:14:55 +0000 @@ -4142,3 +4142,46 @@ c1 c2 c1 c2 LENGTH(t2.c1) LENGTH(t2.c2) 2 2 tt uu 2 2 set join_cache_level=default; DROP TABLE t1,t2; +# +# Bug #51092: linked join buffer is used for a 3-way cross join query +# that selects only records of the first table +# +create table t1 (a int, b int); +insert into t1 values (1,1),(2,2); +create table t2 (a int, b int); +insert into t2 values (1,1),(2,2); +create table t3 (a int, b int); +insert into t3 values (1,1),(2,2); +explain select t1.* from t1,t2,t3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using join buffer +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using join buffer +select t1.* from t1,t2,t3; +a b +1 1 +2 2 +1 1 +2 2 +1 1 +2 2 +1 1 +2 2 +set join_cache_level=2; +explain select t1.* from t1,t2,t3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using join buffer +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using join buffer +select t1.* from t1,t2,t3; +a b +1 1 +2 2 +1 1 +2 2 +1 1 +2 2 +1 1 +2 2 +set join_cache_level=default; +drop table t1,t2,t3; === modified file 'mysql-test/r/subselect_sj.result' --- a/mysql-test/r/subselect_sj.result 2010-02-21 07:53:12 +0000 +++ b/mysql-test/r/subselect_sj.result 2010-02-24 11:33:42 +0000 @@ -824,3 +824,50 @@ a 3 2 drop table t1, t2, t3; +# +# Bug#49198 Wrong result for second call of procedure +# with view in subselect. +# +CREATE TABLE t1 (t1field integer, primary key (t1field)); +CREATE TABLE t2 (t2field integer, primary key (t2field)); +CREATE TABLE t3 (t3field integer, primary key (t3field)); +CREATE VIEW v2 AS SELECT * FROM t2; +CREATE VIEW v3 AS SELECT * FROM t3; +INSERT INTO t1 VALUES(1),(2); +INSERT INTO t2 VALUES(1),(2); +INSERT INTO t3 VALUES(1),(2); +PREPARE stmt FROM +" +SELECT t1field +FROM t1 +WHERE t1field IN (SELECT * FROM v2); +"; +EXECUTE stmt; +t1field +1 +2 +EXECUTE stmt; +t1field +1 +2 +PREPARE stmt FROM +" +EXPLAIN +SELECT t1field +FROM t1 +WHERE t1field IN (SELECT * FROM v2) + AND t1field IN (SELECT * FROM v3) +"; +EXECUTE stmt; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +EXECUTE stmt; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +DROP TABLE t1, t2, t3; +DROP VIEW v2, v3; +# End of Bug#49198 === modified file 'mysql-test/r/subselect_sj2.result' --- a/mysql-test/r/subselect_sj2.result 2010-02-17 10:47:55 +0000 +++ b/mysql-test/r/subselect_sj2.result 2010-03-07 15:41:45 +0000 @@ -264,8 +264,8 @@ explain select * from t0 where a in (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t0 ALL NULL NULL NULL NULL 10 -1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Start temporary; Using join buffer +1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Start temporary +1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Using join buffer 1 PRIMARY t2 ref a a 5 test.t1.a 1 Using index 1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary drop table t0, t1,t2,t3; === modified file 'mysql-test/r/subselect_sj2_jcl6.result' --- a/mysql-test/r/subselect_sj2_jcl6.result 2010-02-17 10:47:55 +0000 +++ b/mysql-test/r/subselect_sj2_jcl6.result 2010-03-07 15:41:45 +0000 @@ -268,8 +268,8 @@ explain select * from t0 where a in (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t0 ALL NULL NULL NULL NULL 10 -1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Start temporary; Using join buffer +1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Start temporary +1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Using join buffer 1 PRIMARY t2 ref a a 5 test.t1.a 1 Using index 1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary drop table t0, t1,t2,t3; @@ -421,20 +421,23 @@ 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); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 -1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 Start temporary; Using join buffer +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Start temporary +1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 Using join buffer 1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 Using where; End temporary; Using join buffer Warnings: Note 1276 Field or reference 'test.t0.a' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t0`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) join `test`.`t0` where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = `test`.`t0`.`a`) and (`test`.`t2`.`a` = `test`.`t0`.`a`)) update t1 set a=3, b=11 where a=4; update t2 set b=11 where a=3; - +# Not anymore: # The following query gives wrong result due to Bug#49129 select * from t0 where t0.a in (select t1.a from t1, t2 where t2.a=t0.a and t1.b=t2.b); a 0 +1 +2 +3 drop table t0, t1, t2; CREATE TABLE t1 ( id int(11) NOT NULL, @@ -713,9 +716,9 @@ c2 in (select 1 from t3, t2) and c1 in (select convert(c6,char(1)) from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 1 -1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch(t2) +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using join buffer +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch(t2); Using join buffer drop table t2, t3; set join_cache_level=default; show variables like 'join_cache_level'; === modified file 'mysql-test/r/subselect_sj_jcl6.result' --- a/mysql-test/r/subselect_sj_jcl6.result 2010-02-21 07:53:12 +0000 +++ b/mysql-test/r/subselect_sj_jcl6.result 2010-03-07 15:41:45 +0000 @@ -374,8 +374,8 @@ WHERE PNUM IN (SELECT PNUM FROM PROJ)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY STAFF ALL NULL NULL NULL NULL 5 -1 PRIMARY PROJ ALL NULL NULL NULL NULL 6 -1 PRIMARY WORKS ALL NULL NULL NULL NULL 12 Using where; FirstMatch(STAFF) +1 PRIMARY PROJ ALL NULL NULL NULL NULL 6 Using join buffer +1 PRIMARY WORKS ALL NULL NULL NULL NULL 12 Using where; FirstMatch(STAFF); Using join buffer SELECT EMPNUM, EMPNAME FROM STAFF WHERE EMPNUM IN @@ -828,6 +828,84 @@ a 3 2 drop table t1, t2, t3; +# +# Bug#49198 Wrong result for second call of procedure +# with view in subselect. +# +CREATE TABLE t1 (t1field integer, primary key (t1field)); +CREATE TABLE t2 (t2field integer, primary key (t2field)); +CREATE TABLE t3 (t3field integer, primary key (t3field)); +CREATE VIEW v2 AS SELECT * FROM t2; +CREATE VIEW v3 AS SELECT * FROM t3; +INSERT INTO t1 VALUES(1),(2); +INSERT INTO t2 VALUES(1),(2); +INSERT INTO t3 VALUES(1),(2); +PREPARE stmt FROM +" +SELECT t1field +FROM t1 +WHERE t1field IN (SELECT * FROM v2); +"; +EXECUTE stmt; +t1field +1 +2 +EXECUTE stmt; +t1field +1 +2 +PREPARE stmt FROM +" +EXPLAIN +SELECT t1field +FROM t1 +WHERE t1field IN (SELECT * FROM v2) + AND t1field IN (SELECT * FROM v3) +"; +EXECUTE stmt; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +EXECUTE stmt; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +DROP TABLE t1, t2, t3; +DROP VIEW v2, v3; +# End of Bug#49198 +# +# BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off +# +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; +UPDATE t1 SET a=3, b=11 WHERE a=4; +UPDATE t2 SET b=11 WHERE a=3; +set @save_optimizer_switch=@@optimizer_switch; +set optimizer_switch='firstmatch=off'; +The following should use a join order of t0,t1,t2, with DuplicateElimination: +explain +SELECT * FROM t0 WHERE t0.a IN +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 Start temporary +1 PRIMARY t1 ref a a 5 test.t0.a 1 Using join buffer +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using where; End temporary; Using join buffer +SELECT * FROM t0 WHERE t0.a IN +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b); +a +0 +1 +2 +3 +set optimizer_switch=@save_optimizer_switch; +drop table t0, t1, t2; +# End set join_cache_level=default; show variables like 'join_cache_level'; Variable_name Value === modified file 'mysql-test/suite/pbxt/r/group_min_max.result' --- a/mysql-test/suite/pbxt/r/group_min_max.result 2009-08-17 15:57:58 +0000 +++ b/mysql-test/suite/pbxt/r/group_min_max.result 2010-02-23 09:22:02 +0000 @@ -2257,7 +2257,7 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer WHE a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1_outer index NULL a 10 NULL 15 Using where; Using index -2 DEPENDENT SUBQUERY t1 index NULL a 10 NULL 1 Using index +2 SUBQUERY t1 index NULL a 10 NULL 15 Using index EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra === modified file 'mysql-test/suite/pbxt/r/subselect.result' --- a/mysql-test/suite/pbxt/r/subselect.result 2009-12-16 09:28:51 +0000 +++ b/mysql-test/suite/pbxt/r/subselect.result 2010-02-23 09:22:02 +0000 @@ -1293,31 +1293,31 @@ a 4 explain extended select * from t2 where t2.a in (select a from t1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index +1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 4 75.00 Using where; Using index; Using join buffer Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`) select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a 2 4 explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using where +1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30)) select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); a 2 3 explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 func 1 100.00 -2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Using index +1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer +1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Using index Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t3` join `test`.`t2` where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`b`)) drop table t1, t2, t3; create table t1 (a int, b int, index a (a,b)); create table t2 (a int, index a (a)); @@ -1332,31 +1332,31 @@ a 4 explain extended select * from t2 where t2.a in (select a from t1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1 100.00 Using index +1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index +1 PRIMARY t1 ref a a 5 test.t2.a 1 100.00 Using index; FirstMatch(t2) Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where (`test`.`t1`.`a` = `test`.`t2`.`a`) select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a 2 4 explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1 100.00 Using index; Using where +1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index +1 PRIMARY t1 ref a a 5 test.t2.a 1 100.00 Using where; Using index; FirstMatch(t2) Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30)) select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); a 2 3 explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 ref a a 5 func 1 100.00 Using index -2 DEPENDENT SUBQUERY t3 ref a a 5 test.t1.b 1 100.00 Using index +1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index +1 PRIMARY t1 ref a a 5 test.t2.a 1 100.00 Using index +1 PRIMARY t3 ref a a 5 test.t1.b 1 100.00 Using index; FirstMatch(t2) Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1` join `test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`b`)) insert into t1 values (3,31); select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a @@ -1369,10 +1369,10 @@ a 4 explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1 100.00 Using index; Using where +1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index +1 PRIMARY t1 ref a a 5 test.t2.a 1 100.00 Using where; Using index; FirstMatch(t2) Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))) +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30)) drop table t1, t2, t3; create table t1 (a int, b int); create table t2 (a int, b int); @@ -2823,10 +2823,10 @@ Warnings: Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Using where -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; FirstMatch(t1) Warnings: -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = 'N') and (<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) and (<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`)))) +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`two` = `test`.`t1`.`two`) and (`test`.`t2`.`one` = `test`.`t1`.`one`) and (`test`.`t2`.`flag` = 'N')) explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 @@ -3412,7 +3412,7 @@ EXPLAIN SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort +2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort ALTER TABLE t1 ADD INDEX(a); SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); a b @@ -3423,7 +3423,7 @@ EXPLAIN SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where -2 DEPENDENT SUBQUERY t1 index NULL a 8 NULL 1 Using filesort +2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort DROP TABLE t1; create table t1( f1 int,f2 int); insert into t1 values (1,1),(2,2); @@ -4213,8 +4213,8 @@ CREATE INDEX I1 ON t1 (a); CREATE INDEX I2 ON t1 (b); EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY t1 index_subquery I1 I1 2 func 1 Using index; Using where +1 PRIMARY t1 index I1 I1 2 NULL 2 Using index; LooseScan +1 PRIMARY t1 ref I2 I2 13 test.t1.a 1 Using where SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); a b CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10)); @@ -4223,15 +4223,15 @@ CREATE INDEX I1 ON t2 (a); CREATE INDEX I2 ON t2 (b); EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY t2 index_subquery I1 I1 4 func 1 Using index; Using where +1 PRIMARY t2 index I1 I1 4 NULL 2 Using index; LooseScan +1 PRIMARY t2 ref I2 I2 13 test.t2.a 1 Using where SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2); a b EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY t1 index_subquery I1 I1 2 func 1 Using index; Using where +1 PRIMARY t1 index I1 I1 2 NULL 2 Using where; Using index; LooseScan +1 PRIMARY t1 ref I2 I2 13 test.t1.a 1 Using where SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500); a b DROP TABLE t1,t2; === modified file 'mysql-test/t/join_cache.test' --- a/mysql-test/t/join_cache.test 2009-12-21 02:26:15 +0000 +++ b/mysql-test/t/join_cache.test 2010-03-06 19:14:55 +0000 @@ -1823,3 +1823,27 @@ SELECT t1.*, t2.*, LENGTH(t2.c1), LENGTH set join_cache_level=default; DROP TABLE t1,t2; + +--echo # +--echo # Bug #51092: linked join buffer is used for a 3-way cross join query +--echo # that selects only records of the first table +--echo # + +create table t1 (a int, b int); +insert into t1 values (1,1),(2,2); +create table t2 (a int, b int); +insert into t2 values (1,1),(2,2); +create table t3 (a int, b int); +insert into t3 values (1,1),(2,2); + +explain select t1.* from t1,t2,t3; +select t1.* from t1,t2,t3; + +set join_cache_level=2; + +explain select t1.* from t1,t2,t3; +select t1.* from t1,t2,t3; + +set join_cache_level=default; + +drop table t1,t2,t3; === modified file 'mysql-test/t/subselect_sj.test' --- a/mysql-test/t/subselect_sj.test 2010-02-21 07:53:12 +0000 +++ b/mysql-test/t/subselect_sj.test 2010-02-24 11:33:42 +0000 @@ -728,3 +728,45 @@ where a in (select c from t2 where d >= drop table t1, t2, t3; +--echo # +--echo # Bug#49198 Wrong result for second call of procedure +--echo # with view in subselect. +--echo # + +CREATE TABLE t1 (t1field integer, primary key (t1field)); +CREATE TABLE t2 (t2field integer, primary key (t2field)); +CREATE TABLE t3 (t3field integer, primary key (t3field)); + +CREATE VIEW v2 AS SELECT * FROM t2; +CREATE VIEW v3 AS SELECT * FROM t3; + +INSERT INTO t1 VALUES(1),(2); +INSERT INTO t2 VALUES(1),(2); +INSERT INTO t3 VALUES(1),(2); + +PREPARE stmt FROM +" +SELECT t1field +FROM t1 +WHERE t1field IN (SELECT * FROM v2); +"; + +EXECUTE stmt; +EXECUTE stmt; + +PREPARE stmt FROM +" +EXPLAIN +SELECT t1field +FROM t1 +WHERE t1field IN (SELECT * FROM v2) + AND t1field IN (SELECT * FROM v3) +"; + +EXECUTE stmt; +EXECUTE stmt; + +DROP TABLE t1, t2, t3; +DROP VIEW v2, v3; + +--echo # End of Bug#49198 === modified file 'mysql-test/t/subselect_sj2.test' --- a/mysql-test/t/subselect_sj2.test 2010-01-17 14:51:10 +0000 +++ b/mysql-test/t/subselect_sj2.test 2010-03-07 15:41:45 +0000 @@ -583,7 +583,7 @@ update t2 set b=11 where a=3; if (`select @@join_cache_level=6`) { - --echo + --echo # Not anymore: --echo # The following query gives wrong result due to Bug#49129 } select * from t0 where t0.a in === modified file 'mysql-test/t/subselect_sj_jcl6.test' --- a/mysql-test/t/subselect_sj_jcl6.test 2010-01-17 14:51:10 +0000 +++ b/mysql-test/t/subselect_sj_jcl6.test 2010-03-07 15:41:45 +0000 @@ -7,5 +7,33 @@ show variables like 'join_cache_level'; --source t/subselect_sj.test +--echo # +--echo # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off +--echo # +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; +UPDATE t1 SET a=3, b=11 WHERE a=4; +UPDATE t2 SET b=11 WHERE a=3; + +set @save_optimizer_switch=@@optimizer_switch; +set optimizer_switch='firstmatch=off'; + +--echo The following should use a join order of t0,t1,t2, with DuplicateElimination: +explain +SELECT * FROM t0 WHERE t0.a IN + (SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b); + +SELECT * FROM t0 WHERE t0.a IN + (SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b); + +set optimizer_switch=@save_optimizer_switch; +drop table t0, t1, t2; + +--echo # End + set join_cache_level=default; show variables like 'join_cache_level'; === modified file 'sql/item.cc' --- a/sql/item.cc 2010-02-21 06:32:23 +0000 +++ b/sql/item.cc 2010-02-24 11:33:42 +0000 @@ -6491,11 +6491,9 @@ void Item_outer_ref::fix_after_pullout(s void Item_ref::fix_after_pullout(st_select_lex *new_parent, Item **refptr) { + (*ref)->fix_after_pullout(new_parent, ref); if (depended_from == new_parent) - { - (*ref)->fix_after_pullout(new_parent, ref); depended_from= NULL; - } } === modified file 'sql/opt_subselect.cc' --- a/sql/opt_subselect.cc 2010-02-19 21:55:57 +0000 +++ b/sql/opt_subselect.cc 2010-03-09 10:36:15 +0000 @@ -531,7 +531,6 @@ static bool replace_where_subcondition(J *expr= new_cond; if (do_fix_fields) new_cond->fix_fields(join->thd, expr); - join->select_lex->where= *expr; return FALSE; } @@ -3031,10 +3030,24 @@ int setup_semijoin_dups_elimination(JOIN forwards, but do not destroy other duplicate elimination methods. */ uint first_table= i; + uint join_cache_level= join->thd->variables.join_cache_level; for (uint j= i; j < i + pos->n_sj_tables; j++) { - if (join->best_positions[j].use_join_buffer && j <= no_jbuf_after) + /* + When we'll properly take join buffering into account during + join optimization, the below check should be changed to + "if (join->best_positions[j].use_join_buffer && + j <= no_jbuf_after)". + For now, use a rough criteria: + */ + JOIN_TAB *js_tab=join->join_tab + j; + if (j != join->const_tables && js_tab->use_quick != 2 && + j <= no_jbuf_after && + ((js_tab->type == JT_ALL && join_cache_level != 0) || + (join_cache_level > 4 && (tab->type == JT_REF || + tab->type == JT_EQ_REF)))) { + /* Looks like we'll be using join buffer */ first_table= join->const_tables; break; } @@ -3112,7 +3125,12 @@ int setup_semijoin_dups_elimination(JOIN JOIN_TAB *j, *jump_to= tab-1; for (j= tab; j != tab + pos->n_sj_tables; j++) { - if (!tab->emb_sj_nest) + /* + NOTE: this loop probably doesn't do the right thing for the case + where FirstMatch's duplicate-generating range is interleaved with + "unrelated" tables (as specified in WL#3750, section 2.2). + */ + if (!j->emb_sj_nest) jump_to= tab; else { === modified file 'sql/sql_join_cache.cc' --- a/sql/sql_join_cache.cc 2010-02-15 21:53:06 +0000 +++ b/sql/sql_join_cache.cc 2010-03-07 15:41:45 +0000 @@ -31,6 +31,8 @@ #include "sql_select.h" #include "opt_subselect.h" +#define NO_MORE_RECORDS_IN_BUFFER (uint)(-1) + /***************************************************************************** * Join cache module @@ -407,8 +409,10 @@ void JOIN_CACHE::set_constants() However at this moment we don't know whether we have referenced fields for the cache or not. Later when a referenced field is registered for the cache we adjust the value of the flag 'with_length'. - */ - with_length= is_key_access() || with_match_flag; + */ + with_length= is_key_access() || + join_tab->is_inner_table_of_semi_join_with_first_match() || + join_tab->is_inner_table_of_outer_join(); /* At this moment we don't know yet the value of 'referenced_fields', but in any case it can't be greater than the value of 'fields'. @@ -604,7 +608,12 @@ int JOIN_CACHE_BKA::init() copy_end= cache->field_descr+cache->fields; for (copy= cache->field_descr+cache->flag_fields; copy < copy_end; copy++) { - if (copy->field->table == tab->table && + /* + (1) - when we store rowids for DuplicateWeedout, they have + copy->field==NULL + */ + if (copy->field && // (1) + copy->field->table == tab->table && bitmap_is_set(key_read_set, copy->field->field_index)) { *copy_ptr++= copy; @@ -1235,7 +1244,7 @@ bool JOIN_CACHE::get_record() prev_rec_ptr= prev_cache->get_rec_ref(pos); } curr_rec_pos= pos; - if (!(res= read_all_record_fields() == 0)) + if (!(res= read_all_record_fields() == NO_MORE_RECORDS_IN_BUFFER)) { pos+= referenced_fields*size_of_fld_ofs; if (prev_cache) @@ -1304,7 +1313,7 @@ bool JOIN_CACHE::get_match_flag_by_pos(u uchar *prev_rec_ptr= prev_cache->get_rec_ref(rec_ptr); return prev_cache->get_match_flag_by_pos(prev_rec_ptr); } - DBUG_ASSERT(1); + DBUG_ASSERT(0); return FALSE; } @@ -1324,7 +1333,8 @@ bool JOIN_CACHE::get_match_flag_by_pos(u read data. RETURN - length of the data read from the join buffer + (-1) - if there is no more records in the join buffer + length of the data read from the join buffer - otherwise */ uint JOIN_CACHE::read_all_record_fields() @@ -1332,7 +1342,7 @@ uint JOIN_CACHE::read_all_record_fields( uchar *init_pos= pos; if (pos > last_rec_pos || !records) - return 0; + return NO_MORE_RECORDS_IN_BUFFER; /* First match flag, read null bitmaps and null_row flag for each table */ read_flag_fields(); @@ -1538,12 +1548,12 @@ bool JOIN_CACHE::read_referenced_field(C bool JOIN_CACHE::skip_record_if_match() { - DBUG_ASSERT(with_match_flag && with_length); + DBUG_ASSERT(with_length); uint offset= size_of_rec_len; if (prev_cache) offset+= prev_cache->get_size_of_rec_offset(); /* Check whether the match flag is on */ - if (test(*(pos+offset))) + if (get_match_flag_by_pos(pos+offset)) { pos+= size_of_rec_len + get_rec_length(pos); return TRUE; === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-02-19 21:55:57 +0000 +++ b/sql/sql_select.cc 2010-03-09 10:36:15 +0000 @@ -5635,7 +5635,11 @@ void calc_used_field_length(THD *thd, JO uint blob_length=(uint) (join_tab->table->file->stats.mean_rec_length- (join_tab->table->s->reclength-rec_length)); rec_length+=(uint) max(4,blob_length); - } + } + /* + psergey-todo: why we don't count here rowid that we might need to store + when using DuplicateElimination? + */ join_tab->used_fields=fields; join_tab->used_fieldlength=rec_length; join_tab->used_blobs=blobs; @@ -6355,10 +6359,17 @@ make_outerjoin_info(JOIN *join) } if (!tab->first_inner) tab->first_inner= nested_join->first_nested; + if (tab->table->reginfo.not_exists_optimize) + tab->first_inner->table->reginfo.not_exists_optimize= 1; if (++nested_join->counter < nested_join->n_tables) break; /* Table tab is the last inner table for nested join. */ nested_join->first_nested->last_inner= tab; + if (tab->first_inner->table->reginfo.not_exists_optimize) + { + for (JOIN_TAB *join_tab= tab->first_inner; join_tab <= tab; join_tab++) + join_tab->table->reginfo.not_exists_optimize= 1; + } } } DBUG_VOID_RETURN; @@ -7112,18 +7123,14 @@ uint check_join_cache_usage(JOIN_TAB *ta if (tab->use_quick == 2) goto no_join_cache; /* - Use join cache with FirstMatch semi-join strategy only when semi-join - contains only one table. - */ - if (tab->is_inner_table_of_semi_join_with_first_match() && - !tab->is_single_inner_of_semi_join_with_first_match()) - goto no_join_cache; - /* Non-linked join buffers can't guarantee one match */ - if (force_unlinked_cache && - (tab->is_inner_table_of_outer_join() && - !tab->is_single_inner_of_outer_join())) + if (force_unlinked_cache && + (!tab->type == JT_ALL || cache_level <= 4) && + ((tab->is_inner_table_of_semi_join_with_first_match() && + !tab->is_single_inner_of_semi_join_with_first_match()) || + (tab->is_inner_table_of_outer_join() && + !tab->is_single_inner_of_outer_join()))) goto no_join_cache; /* === modified file 'sql/sql_select.h' --- a/sql/sql_select.h 2010-02-15 21:53:06 +0000 +++ b/sql/sql_select.h 2010-03-05 18:54:48 +0000 @@ -321,8 +321,8 @@ typedef struct st_join_table { } bool check_only_first_match() { - return last_sj_inner_tab == this || - (first_inner && first_inner->last_inner == this && + return is_inner_table_of_semi_join_with_first_match() || + (is_inner_table_of_outer_join() && table->reginfo.not_exists_optimize); } bool is_last_inner_table()