maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #02450
Rev 2766: MWL#68 Subquery optimization: Efficient NOT IN execution with NULLs in file:///home/tsk/mprog/src/5.3-mwl68-merge.base-mwl68/
At file:///home/tsk/mprog/src/5.3-mwl68-merge.base-mwl68/
------------------------------------------------------------
revno: 2766 [merge]
revision-id: timour@xxxxxxxxxxxx-20100309103615-dzmm6xt7ye5xfs25
parent: timour@xxxxxxxxxxxx-20100309101406-xygkt2sgftvjvevg
parent: psergey@xxxxxxxxxxxx-20100307154145-ksby2b1l0sqm1xne
committer: timour@xxxxxxxxxxxx
branch nick: 5.3-mwl68-merge.base-mwl68
timestamp: Tue 2010-03-09 12:36:15 +0200
message:
MWL#68 Subquery optimization: Efficient NOT IN execution with NULLs
Automerge with 5.3-subqueries
modified:
mysql-test/r/join_cache.result join_cache.result-20091221012827-jfu65h0x5bmixhh3-1
mysql-test/r/subselect_sj.result subselect_sj.result-20100117143926-nrop4ku355g3kv8b-1
mysql-test/r/subselect_sj2.result subselect_sj2.result-20100117143927-4k8x8d6czjviugog-1
mysql-test/r/subselect_sj2_jcl6.result subselect_sj2_jcl6.r-20100117143927-r3uxj2zuyjtrnokh-1
mysql-test/r/subselect_sj_jcl6.result subselect_sj_jcl6.re-20100117143928-7vzk51yaf29cdavp-1
mysql-test/suite/pbxt/r/group_min_max.result group_min_max.result-20090402100035-4ilk9i91sh65vjcb-69
mysql-test/suite/pbxt/r/subselect.result subselect.result-20090402100035-4ilk9i91sh65vjcb-146
mysql-test/t/join_cache.test join_cache.test-20091221012705-n3szmbc9blgmmu84-1
mysql-test/t/subselect_sj.test subselect_sj.test-20100117143931-qp396ufpe3k0scre-1
mysql-test/t/subselect_sj2.test subselect_sj2.test-20100117143932-vxp9ugyy3s0mdo5j-1
mysql-test/t/subselect_sj_jcl6.test subselect_sj_jcl6.te-20100117144012-tmbazng78xjyw6m1-1
sql/item.cc sp1f-item.cc-19700101030959-u7hxqopwpfly4kf5ctlyk2dvrq4l3dhn
sql/opt_subselect.cc opt_subselect.cc-20100215190428-nekkl8wisp0k6nlk-1
sql/sql_join_cache.cc sql_join_cache.cc-20091221012625-ipp8zu28iijhjmq2-1
sql/sql_select.cc sp1f-sql_select.cc-19700101030959-egb7whpkh76zzvikycs5nsnuviu4fdlb
sql/sql_select.h sp1f-sql_select.h-19700101030959-oqegfxr76xlgmrzd6qlevonoibfnwzoz
=== 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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
(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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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 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 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 @@
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 @@
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 @@
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 @@
--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_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 @@
*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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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::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 @@
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 @@
}
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 @@
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 @@
}
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()
