#At file:///home/spetrunia/dev/mysql-6.0-subq-r20/ based on revid:sergefp@stripped
2745 Sergey Petrunia 2008-12-22
WL#4688: Merge Batched Key Access and Subquery Optimizations, Step#1
modified:
mysql-test/r/subselect3.result
mysql-test/r/subselect3_jcl6.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
sql/sql_join_cache.cc
sql/sql_select.cc
per-file messages:
mysql-test/r/subselect3.result
WL#4688: Merge Batched Key Access and Subquery Optimizations
- Updated test results
mysql-test/r/subselect3_jcl6.result
WL#4688: Merge Batched Key Access and Subquery Optimizations
- Updated test results
mysql-test/r/subselect_sj.result
WL#4688: Merge Batched Key Access and Subquery Optimizations
- Updated test results
mysql-test/r/subselect_sj2.result
WL#4688: Merge Batched Key Access and Subquery Optimizations
- Updated test results
mysql-test/r/subselect_sj2_jcl6.result
WL#4688: Merge Batched Key Access and Subquery Optimizations
- Updated test results
mysql-test/r/subselect_sj_jcl6.result
WL#4688: Merge Batched Key Access and Subquery Optimizations
- Updated test results
sql/sql_join_cache.cc
WL#4688: Merge Batched Key Access and Subquery Optimizations
- Make BKA code honor join_tab->keep_current_rowid
sql/sql_select.cc
WL#4688: Merge Batched Key Access and Subquery Optimizations, Step#1
- Make subquery optimization code produce flags that BKA needs
=== modified file 'mysql-test/r/subselect3.result'
--- a/mysql-test/r/subselect3.result 2008-12-13 20:01:27 +0000
+++ b/mysql-test/r/subselect3.result 2008-12-22 19:03:25 +0000
@@ -969,7 +969,7 @@ insert into t3 select A.a + 10*B.a from
explain select * from t3 where a in (select kp1 from t1 where kp1<20);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; LooseScan
-1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer
create table t4 (pk int primary key);
insert into t4 select a from t3;
explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20
@@ -977,7 +977,7 @@ and t4.pk=t1.c);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using index condition; Using MRR; LooseScan
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t1)
-1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer
drop table t1, t3, t4;
create table t1 (a int) as select * from t0 where a < 5;
set @save_max_heap_table_size=@@max_heap_table_size;
@@ -1102,11 +1102,11 @@ create table t2 as select * from t1;
explain select * from t2 where a in (select b from t1 where a=3);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range a a 5 NULL 8 Using where; Using index; LooseScan
-1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where
+1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer
explain select * from t2 where (b,a) in (select a,b from t1 where a=3);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range a a 5 NULL 8 Using where; Using index; LooseScan
-1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where
+1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer
drop table t1,t2;
create table t1 (a int, b int);
insert into t1 select a,a from t0;
@@ -1152,3 +1152,29 @@ a
2008-01-01
2008-02-02
drop table t0, t1;
+
+BUG#40118 Crash when running Batched Key Access and requiring one match for each key
+
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int, key(a));
+insert into t1 select * from t0;
+alter table t1 add b int not null, add filler char(200);
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+set @save_join_cache_level=@@join_cache_level;
+set join_cache_level=6;
+select * from t0 where t0.a in (select t1.a from t1 where t1.b=0);
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+set join_cache_level=@save_join_cache_level;
+drop table t0, t1;
=== modified file 'mysql-test/r/subselect3_jcl6.result'
--- a/mysql-test/r/subselect3_jcl6.result 2008-12-08 21:15:06 +0000
+++ b/mysql-test/r/subselect3_jcl6.result 2008-12-22 19:03:25 +0000
@@ -2,7 +2,7 @@ set join_cache_level=6;
show variables like 'join_cache_level';
Variable_name Value
join_cache_level 6
-drop table if exists t0, t1, t2, t3, t4, t5;
+drop table if exists t0, t1, t2, t3, t4, t5, t11, t12, t21, t22;
create table t1 (oref int, grp int, ie int) ;
insert into t1 (oref, grp, ie) values
(1, 1, 1),
@@ -103,7 +103,7 @@ oref a
1 1
show status like '%Handler_read_rnd_next';
Variable_name Value
-Handler_read_rnd_next 11
+Handler_read_rnd_next 5
delete from t2;
insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0);
flush status;
@@ -842,6 +842,321 @@ t1.a < (select t4.a+10
from t4, t5 limit 2));
ERROR 21000: Subquery returns more than 1 row
drop table t0, t1, t2, t3, t4, t5;
+CREATE TABLE t1 (
+a int(11) NOT NULL,
+b int(11) NOT NULL,
+c datetime default NULL,
+PRIMARY KEY (a),
+KEY idx_bc (b,c)
+);
+INSERT INTO t1 VALUES
+(406989,67,'2006-02-23 17:08:46'), (150078,67,'2005-10-26 11:17:45'),
+(406993,67,'2006-02-27 11:20:57'), (245655,67,'2005-12-08 15:59:08'),
+(406994,67,'2006-02-27 11:26:46'), (256,67,NULL),
+(398341,67,'2006-02-20 04:48:44'), (254,67,NULL),(1120,67,NULL),
+(406988,67,'2006-02-23 17:07:22'), (255,67,NULL),
+(398340,67,'2006-02-20 04:38:53'),(406631,67,'2006-02-23 10:49:42'),
+(245653,67,'2005-12-08 15:59:07'),(406992,67,'2006-02-24 16:47:18'),
+(245654,67,'2005-12-08 15:59:08'),(406995,67,'2006-02-28 11:55:00'),
+(127261,67,'2005-10-13 12:17:58'),(406991,67,'2006-02-24 16:42:32'),
+(245652,67,'2005-12-08 15:58:27'),(398545,67,'2006-02-20 04:53:13'),
+(154504,67,'2005-10-28 11:53:01'),(9199,67,NULL),(1,67,'2006-02-23 15:01:35'),
+(223456,67,NULL),(4101,67,NULL),(1133,67,NULL),
+(406990,67,'2006-02-23 18:01:45'),(148815,67,'2005-10-25 15:34:17'),
+(148812,67,'2005-10-25 15:30:01'),(245651,67,'2005-12-08 15:58:27'),
+(154503,67,'2005-10-28 11:52:38');
+create table t11 select * from t1 where b = 67 AND (c IS NULL OR c > NOW()) order by 3 asc;
+create table t12 select * from t1 where b = 67 AND (c IS NULL OR c > NOW()) order by 3 desc;
+create table t21 select * from t1 where b = 67 AND (c IS NULL OR c > '2005-12-08') order by 3 asc;
+create table t22 select * from t1 where b = 67 AND (c IS NULL OR c > '2005-12-08') order by 3 desc;
+update t22 set c = '2005-12-08 15:58:27' where a = 255;
+explain select t21.* from t21,t22 where t21.a = t22.a and
+t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort; Start materialize; Scan
+1 PRIMARY t12 ALL NULL NULL NULL NULL 8 Using where; End materialize; Using join buffer
+1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer
+1 PRIMARY t22 ALL NULL NULL NULL NULL 32 Using where; Using join buffer
+select t21.* from t21,t22 where t21.a = t22.a and
+t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
+a b c
+256 67 NULL
+256 67 NULL
+drop table t1, t11, t12, t21, t22;
+create table t1(a int);
+insert into t1 values (0),(1);
+set @@optimizer_switch='no_firstmatch';
+explain
+select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) as subq from t1 X;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY X ALL NULL NULL NULL NULL 2
+2 DEPENDENT SUBQUERY Y ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY Z ALL NULL NULL NULL NULL 2 Materialize
+select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) as subq from t1 X;
+subq
+NULL
+0
+set @@optimizer_switch='';
+drop table t1;
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 as select * from t0;
+insert into t1 select a+10 from t0;
+set @@optimizer_switch='no_firstmatch,no_materialization';
+insert into t0 values(2);
+explain select * from t1 where 2 in (select a from t0);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 ALL NULL NULL NULL NULL 11 Using where; Start temporary; End temporary
+1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using join buffer
+select * from t1 where 2 in (select a from t0);
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
+set @@optimizer_switch='no_materialization';
+explain select * from t1 where 2 in (select a from t0);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 ALL NULL NULL NULL NULL 11 Using where; FirstMatch
+1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using join buffer
+select * from t1 where 2 in (select a from t0);
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
+set @@optimizer_switch='';
+explain select * from (select a from t0) X where a in (select a from t1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 11
+1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using where; FirstMatch(<derived2>); Using join buffer
+2 DERIVED t0 ALL NULL NULL NULL NULL 11
+drop table t0, t1;
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (kp1 int, kp2 int, c int, filler char(100), key(kp1, kp2));
+insert into t1 select A.a+10*(B.a+10*C.a), 0, 0, 'filler' from t0 A, t0 B, t0 C;
+insert into t1 select * from t1 where kp1 < 20;
+create table t3 (a int);
+insert into t3 select A.a + 10*B.a from t0 A, t0 B;
+explain select * from t3 where a in (select kp1 from t1 where kp1<20);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; LooseScan
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer
+create table t4 (pk int primary key);
+insert into t4 select a from t3;
+explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20
+and t4.pk=t1.c);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using index condition; Using MRR; LooseScan
+1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t1)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer
+drop table t1, t3, t4;
+create table t1 (a int) as select * from t0 where a < 5;
+set @save_max_heap_table_size=@@max_heap_table_size;
+set @@optimizer_switch='no_firstmatch,no_materialization';
+set @@max_heap_table_size= 16384;
+explain select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY E ALL NULL NULL NULL NULL 5 Start temporary
+1 PRIMARY A ALL NULL NULL NULL NULL 10 Using join buffer
+1 PRIMARY B ALL NULL NULL NULL NULL 10 Using join buffer
+1 PRIMARY C ALL NULL NULL NULL NULL 10 Using join buffer
+1 PRIMARY D ALL NULL NULL NULL NULL 10 Using where; End temporary; Using join buffer
+flush status;
+select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E);
+count(*)
+4999
+show status like 'Created_tmp_disk_tables';
+Variable_name Value
+Created_tmp_disk_tables 1
+set @save_max_heap_table_size=@@max_heap_table_size;
+set @@optimizer_switch=default;
+drop table t0, t1;
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2(a int);
+insert into t2 values (1),(2);
+create table t3 ( a int , filler char(100), key(a));
+insert into t3 select A.a + 10*B.a, 'filler' from t0 A, t0 B;
+explain select * from t3 where a in (select a from t2) and (a > 5 or a < 10);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Materialize; Scan
+1 PRIMARY t3 ref a a 5 test.t2.a 1 Using join buffer
+select * from t3 where a in (select a from t2);
+a filler
+1 filler
+2 filler
+drop table t0, t2, t3;
+set @@optimizer_switch='no_firstmatch,no_materialization';
+create table t1 (a date);
+insert into t1 values ('2008-01-01'),('2008-01-01'),('2008-02-01'),('2008-02-01');
+create table t2 (a int);
+insert into t2 values (1),(2);
+create table t3 (a char(10));
+insert into t3 select * from t1;
+insert into t3 values (1),(2);
+explain select * from t2 where a in (select a from t1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; End temporary; Using join buffer
+explain select * from t2 where a 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 Start temporary
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer
+explain select * from t2 where a in (select a from t3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY t3 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer
+explain select * from t1 where a in (select a from t3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Start temporary
+1 PRIMARY t3 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer
+drop table t1, t2, t3;
+create table t1 (a decimal);
+insert into t1 values (1),(2);
+explain select * from t1 where a 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 Start temporary
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer
+drop table t1;
+set @@optimizer_switch=default;
+create table t1 (a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 as select * from t1;
+create table t3 (a int, b int, filler char(100), key(a));
+insert into t3 select A.a + 10*B.a, A.a + 10*B.a, 'filler' from t1 A, t1 B, t1 C;
+explain select * from t1, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30) and t1.a =3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; Materialize; Scan
+1 PRIMARY t3 ref a a 5 test.t2.a 10 Using join buffer
+explain select straight_join * from t1 A, t1 B where A.a in (select a from t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY A ALL NULL NULL NULL NULL 10 Using where
+1 PRIMARY B ALL NULL NULL NULL NULL 10 Using join buffer
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 10
+explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
+2 SUBQUERY A ALL NULL NULL NULL NULL 10
+2 SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer
+explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
+2 SUBQUERY A ALL NULL NULL NULL NULL 10
+2 SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer
+explain select straight_join * from t2 X, t2 Y
+where X.a in (select straight_join A.a from t1 A, t1 B);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY X ALL NULL NULL NULL NULL 10 Using where
+1 PRIMARY Y ALL NULL NULL NULL NULL 10 Using join buffer
+2 SUBQUERY A ALL NULL NULL NULL NULL 10
+2 SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer
+create table t0 (a int, b int);
+insert into t0 values(1,1);
+explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 system NULL NULL NULL NULL 1
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; Materialize; Scan
+1 PRIMARY t3 ref a a 5 test.t2.a 10 Using join buffer
+create table t4 as select a as x, a as y from t1;
+explain select * from t0, t3 where (t3.a, t3.b) in (select x,y from t4) and (t3.a < 10 or t3.a >30);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 system NULL NULL NULL NULL 1
+1 PRIMARY t4 ALL NULL NULL NULL NULL 10 Using where; Materialize; Scan
+1 PRIMARY t3 ref a a 5 test.t4.x 10 Using where; Using join buffer
+drop table t0,t1,t2,t3,t4;
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int, b int, filler char(100), key(a,b));
+insert into t1 select A.a, B.a, 'filler' from t0 A, t0 B;
+create table t2 as select * from t1;
+explain select * from t2 where a in (select b from t1 where a=3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 range a a 5 NULL 8 Using where; Using index; LooseScan
+1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer
+explain select * from t2 where (b,a) in (select a,b from t1 where a=3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 range a a 5 NULL 8 Using where; Using index; LooseScan
+1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer
+drop table t1,t2;
+create table t1 (a int, b int);
+insert into t1 select a,a from t0;
+create table t2 (a int, b int);
+insert into t2 select A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B;
+set @@optimizer_switch='no_firstmatch';
+explain select * from t1 where (a,b) in (select a,b from t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10
+1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Materialize
+set @save_optimizer_search_depth=@@optimizer_search_depth;
+set @@optimizer_search_depth=63;
+explain select * from t1 where (a,b) in (select a,b from t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10
+1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Materialize
+set @@optimizer_search_depth=@save_optimizer_search_depth;
+set @@optimizer_switch='';
+drop table t0, t1, t2;
+create table t0 (a decimal(4,2));
+insert into t0 values (10.24), (22.11);
+create table t1 as select * from t0;
+insert into t1 select * from t0;
+explain select * from t0 where a in (select a from t1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 ALL NULL NULL NULL NULL 2
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(t0); Using join buffer
+select * from t0 where a in (select a from t1);
+a
+10.24
+22.11
+drop table t0, t1;
+create table t0(a date);
+insert into t0 values ('2008-01-01'),('2008-02-02');
+create table t1 as select * from t0;
+insert into t1 select * from t0;
+explain select * from t0 where a in (select a from t1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 ALL NULL NULL NULL NULL 2
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(t0); Using join buffer
+select * from t0 where a in (select a from t1);
+a
+2008-01-01
+2008-02-02
+drop table t0, t1;
BUG#40118 Crash when running Batched Key Access and requiring one match for each key
=== modified file 'mysql-test/r/subselect_sj.result'
--- a/mysql-test/r/subselect_sj.result 2008-12-13 20:01:27 +0000
+++ b/mysql-test/r/subselect_sj.result 2008-12-22 19:03:25 +0000
@@ -72,7 +72,7 @@ select * from t1 left join (t2 A, t2 B)
id select_type tABle type possiBle_keys key key_len ref rows filtered ExtrA
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
1 PRIMARY A ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY B ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY B ALL NULL NULL NULL NULL 3 100.00 Using where
2 DEPENDENT SUBQUERY t10 unique_suBquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
Note 1003 select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`A`.`A` AS `A`,`test`.`A`.`B` AS `B`,`test`.`B`.`A` AS `A`,`test`.`B`.`B` AS `B` from `test`.`t1` left join (`test`.`t2` `A` join `test`.`t2` `B`) on(((`test`.`A`.`A` = `test`.`t1`.`A`) And <in_optimizer>(`test`.`B`.`A`,<exists>(<primAry_index_lookup>(<cAche>(`test`.`B`.`A`) in t10 on PRIMARY))))) where 1
=== modified file 'mysql-test/r/subselect_sj2.result'
--- a/mysql-test/r/subselect_sj2.result 2008-12-13 20:01:27 +0000
+++ b/mysql-test/r/subselect_sj2.result 2008-12-22 19:03:25 +0000
@@ -262,7 +262,7 @@ 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
+1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Start temporary; 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 2008-11-05 00:53:38 +0000
+++ b/mysql-test/r/subselect_sj2_jcl6.result 2008-12-22 19:03:25 +0000
@@ -36,8 +36,8 @@ a b
9 5
explain select * from t2 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 3 Start temporary
-1 PRIMARY t2 ref b b 5 test.t1.a 2 End temporary; Using join buffer
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Materialize; Scan
+1 PRIMARY t2 ref b b 5 test.t1.a 2 Using join buffer
select * from t2 where b in (select a from t1);
a b
1 1
@@ -54,8 +54,8 @@ primary key(pk1, pk2, pk3)
insert into t3 select a,a, a,a,a from t0;
explain select * from t3 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 3 Start temporary
-1 PRIMARY t3 ref b b 5 test.t1.a 1 End temporary; Using join buffer
+1 PRIMARY t3 ALL b NULL NULL NULL 10
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; FirstMatch(t3); Using join buffer
select * from t3 where b in (select a from t1);
a b pk1 pk2 pk3
1 1 1 1 1
@@ -77,8 +77,8 @@ A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a
from t0 A, t0 B where B.a <5;
explain select * from t3 where b in (select a from t0);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Start temporary
-1 PRIMARY t3 ref b b 5 test.t0.a 1 End temporary; Using join buffer
+1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Materialize; Scan
+1 PRIMARY t3 ref b b 5 test.t0.a 1 Using join buffer
select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5);
a b pk1 pk2
0 0 0 0
@@ -99,8 +99,8 @@ set join_buffer_size= @save_join_buffer_
set max_heap_table_size= @save_max_heap_table_size;
explain select * from t1 where a in (select b from t2);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 index b b 5 NULL 10 Using index; LooseScan
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3
+1 PRIMARY t2 index b b 5 NULL 10 Using index; Materialize
select * from t1;
a b
1 1
@@ -127,8 +127,8 @@ explain select
a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z
from t1 ot where a in (select a from t2 it);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY it ALL NULL NULL NULL NULL 22 Start temporary
-1 PRIMARY ot ALL NULL NULL NULL NULL 32 Using where; End temporary; Using join buffer
+1 PRIMARY it ALL NULL NULL NULL NULL 22 Materialize; Scan
+1 PRIMARY ot ALL NULL NULL NULL NULL 32 Using where; Using join buffer
select
a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z
from t1 ot where a in (select a from t2 it);
@@ -159,8 +159,8 @@ explain select
a, mid(filler1, 1,10), length(filler1)=length(filler2)
from t2 ot where a in (select a from t1 it);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY ot ALL NULL NULL NULL NULL 22 Start temporary
-1 PRIMARY it ALL NULL NULL NULL NULL 32 Using where; End temporary; Using join buffer
+1 PRIMARY ot ALL NULL NULL NULL NULL 22
+1 PRIMARY it ALL NULL NULL NULL NULL 32 Materialize
select
a, mid(filler1, 1,10), length(filler1)=length(filler2)
from t2 ot where a in (select a from t1 it);
@@ -184,8 +184,8 @@ a mid(filler1, 1,10) length(filler1)=len
16 filler1234 1
17 filler1234 1
18 filler1234 1
-3 duplicate 1
19 filler1234 1
+3 duplicate 1
19 duplicate 1
insert into t1 select a+20, 'filler123456', 'filler123456' from t0;
insert into t1 select a+20, 'filler123456', 'filler123456' from t0;
@@ -193,8 +193,8 @@ explain select
a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z
from t1 ot where a in (select a from t2 it);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY it ALL NULL NULL NULL NULL 22 Start temporary
-1 PRIMARY ot ALL NULL NULL NULL NULL 52 Using where; End temporary; Using join buffer
+1 PRIMARY it ALL NULL NULL NULL NULL 22 Materialize; Scan
+1 PRIMARY ot ALL NULL NULL NULL NULL 52 Using where; Using join buffer
select
a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z
from t1 ot where a in (select a from t2 it);
@@ -225,8 +225,8 @@ explain select
a, mid(filler1, 1,10), length(filler1)=length(filler2)
from t2 ot where a in (select a from t1 it);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY ot ALL NULL NULL NULL NULL 22 Start temporary
-1 PRIMARY it ALL NULL NULL NULL NULL 52 Using where; End temporary; Using join buffer
+1 PRIMARY ot ALL NULL NULL NULL NULL 22
+1 PRIMARY it ALL NULL NULL NULL NULL 52 Materialize
select
a, mid(filler1, 1,10), length(filler1)=length(filler2)
from t2 ot where a in (select a from t1 it);
@@ -250,8 +250,8 @@ a mid(filler1, 1,10) length(filler1)=len
16 filler1234 1
17 filler1234 1
18 filler1234 1
-3 duplicate 1
19 filler1234 1
+3 duplicate 1
19 duplicate 1
drop table t1, t2;
create table t1 (a int, b int, key(a));
@@ -265,8 +265,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 Start temporary
-1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Using join buffer
+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 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;
@@ -304,9 +304,9 @@ t2.Code IN (SELECT Country FROM t3
WHERE Language='English' AND Percentage > 10 AND
t2.Population > 100000);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 range Population,Country Population 4 NULL 1 Using index condition; Using MRR; Start temporary
-1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t1.Country 1 Using where; End temporary; Using join buffer
-1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t2.Code,const 1 Using index condition; Using where; Using join buffer
+1 PRIMARY t1 range Population,Country Population 4 NULL 1 Using index condition; Using MRR
+1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using index condition; Using where; Using join buffer
+1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t3.Country 1 Using where; Using join buffer
DROP TABLE t1,t2,t3;
CREATE TABLE t1 (
Code char(3) NOT NULL DEFAULT '',
@@ -341,8 +341,8 @@ EXPLAIN SELECT Name FROM t1
WHERE t1.Code IN (
SELECT t2.CountryCode FROM t2 WHERE Population > 5000000);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL CountryCode NULL NULL NULL 545 Using where; Start temporary
-1 PRIMARY t1 eq_ref PRIMARY PRIMARY 3 test.t2.CountryCode 1 End temporary; Using join buffer
+1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 31
+1 PRIMARY t2 ALL CountryCode NULL NULL NULL 545 Using where; Materialize
SELECT Name FROM t1
WHERE t1.Code IN (
SELECT t2.CountryCode FROM t2 WHERE Population > 5000000);
@@ -562,8 +562,8 @@ explain
select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t3));
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3
-1 PRIMARY t2 ALL NULL NULL NULL NULL 3 Using where
-1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 Using index
+1 PRIMARY t2 ALL NULL NULL NULL NULL 3 Using where; Using join buffer
+2 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY PRIMARY 4 func 1 Using index
drop table t0, t1, t2, t3;
create table t1 (a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -675,8 +675,8 @@ alter table t3 add primary key(id), add
The following must use loose index scan over t3, key a:
explain select count(a) from t2 where a in ( SELECT a FROM t3);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 index a a 5 NULL 30000 Using index; LooseScan
-1 PRIMARY t2 ref a a 5 test.t3.a 1 Using index
+1 PRIMARY t2 index a a 5 NULL 1000 Using index
+1 PRIMARY t3 index a a 5 NULL 30000 Using index; Materialize
select count(a) from t2 where a in ( SELECT a FROM t3);
count(a)
1000
=== modified file 'mysql-test/r/subselect_sj_jcl6.result'
--- a/mysql-test/r/subselect_sj_jcl6.result 2008-12-08 21:15:06 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result 2008-12-22 19:03:25 +0000
@@ -75,20 +75,20 @@ explAin extended
select * from t1 left join (t2 A, t2 B) on ( A.A= t1.A And B.A in (select pk from t10));
id select_type tABle type possiBle_keys key key_len ref rows filtered ExtrA
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
-1 PRIMARY A ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY B ALL NULL NULL NULL NULL 3 100.00
-1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.B.A 1 100.00 Using index
+1 PRIMARY A ALL NULL NULL NULL NULL 3 100.00 Using where; Using join Buffer
+1 PRIMARY B ALL NULL NULL NULL NULL 3 100.00 Using where; Using join Buffer
+2 DEPENDENT SUBQUERY t10 unique_suBquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
-Note 1003 select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`A`.`A` AS `A`,`test`.`A`.`B` AS `B`,`test`.`B`.`A` AS `A`,`test`.`B`.`B` AS `B` from `test`.`t1` left join (`test`.`t10` join `test`.`t2` `A` join `test`.`t2` `B`) on(((`test`.`A`.`A` = `test`.`t1`.`A`) And 1 And (`test`.`B`.`A` = `test`.`t10`.`pk`))) where 1
+Note 1003 select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`A`.`A` AS `A`,`test`.`A`.`B` AS `B`,`test`.`B`.`A` AS `A`,`test`.`B`.`B` AS `B` from `test`.`t1` left join (`test`.`t2` `A` join `test`.`t2` `B`) on(((`test`.`A`.`A` = `test`.`t1`.`A`) And <in_optimizer>(`test`.`B`.`A`,<exists>(<primAry_index_lookup>(<cAche>(`test`.`B`.`A`) in t10 on PRIMARY))))) where 1
t2 should be wrapped into OJ-nest, so we have "t1 LJ (t2 J t10)"
explAin extended
select * from t1 left join t2 on (t2.A= t1.A And t2.A in (select pk from t10));
id select_type tABle type possiBle_keys key key_len ref rows filtered ExtrA
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
-1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t2.A 1 100.00 Using index
+1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join Buffer
+2 DEPENDENT SUBQUERY t10 unique_suBquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
-Note 1003 select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`t2`.`A` AS `A`,`test`.`t2`.`B` AS `B` from `test`.`t1` left join (`test`.`t10` join `test`.`t2`) on(((`test`.`t2`.`A` = `test`.`t1`.`A`) And 1 And (`test`.`t2`.`A` = `test`.`t10`.`pk`))) where 1
+Note 1003 select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`t2`.`A` AS `A`,`test`.`t2`.`B` AS `B` from `test`.`t1` left join `test`.`t2` on(((`test`.`t2`.`A` = `test`.`t1`.`A`) And <in_optimizer>(`test`.`t2`.`A`,<exists>(<primAry_index_lookup>(<cAche>(`test`.`t2`.`A`) in t10 on PRIMARY))))) where 1
we shouldn't flatten if we're going to get a join of > MAX_TABLES.
explain select * from
t1 s00, t1 s01, t1 s02, t1 s03, t1 s04,t1 s05,t1 s06,t1 s07,t1 s08,t1 s09,
=== modified file 'sql/sql_join_cache.cc'
--- a/sql/sql_join_cache.cc 2008-12-13 20:01:27 +0000
+++ b/sql/sql_join_cache.cc 2008-12-22 19:03:25 +0000
@@ -1654,6 +1654,9 @@ enum_nested_loop_state JOIN_CACHE_BNL::j
info= &join_tab->read_record;
do
{
+ if (join_tab->keep_current_rowid)
+ join_tab->table->file->position(join_tab->table->record[0]);
+
if (join->thd->killed)
{
/* The user has aborted the execution of the query */
@@ -2152,6 +2155,8 @@ enum_nested_loop_state JOIN_CACHE_BKA::j
rc= NESTED_LOOP_KILLED;
goto finish;
}
+ if (join_tab->keep_current_rowid)
+ join_tab->table->file->position(join_tab->table->record[0]);
/*
If only the first match is needed and it has been already found
for the associated partial join record then the returned candidate
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2008-12-13 20:01:27 +0000
+++ b/sql/sql_select.cc 2008-12-22 19:03:25 +0000
@@ -1325,6 +1325,11 @@ int setup_semijoin_dups_elimination(JOIN
{
if (!tab->emb_sj_nest)
jump_to= tab;
+ else
+ {
+ tab->first_sj_inner_tab= tab;
+ tab->last_sj_inner_tab= tab + pos->n_sj_tables - 1;
+ }
}
j[-1].do_firstmatch= jump_to;
i += pos->n_sj_tables;
@@ -7801,7 +7806,9 @@ static void fix_semijoin_strategies_for_
table_map rem_tables= remaining_tables;
uint idx;
for (idx= first; idx <= tablenr; idx++)
+ {
rem_tables |= join->best_positions[idx].table->table->map;
+ }
/*
Re-run best_access_path to produce best access methods that do not use
join buffering
@@ -9335,6 +9342,10 @@ void set_join_cache_denial(JOIN_TAB *joi
if (join_tab->use_join_cache)
{
join_tab->use_join_cache= FALSE;
+ /*
+ It could be only sub_select(). It could not be sub_seject_sjm because we
+ don't do join buffering for the first table in sjm nest.
+ */
join_tab[-1].next_select= sub_select;
}
}
@@ -9810,27 +9821,34 @@ bool check_join_cache_usage(JOIN_TAB *ta
bool force_unlinked_cache= test(cache_level & 1);
uint i= tab - join->join_tab;
- if (cache_level == 0)
- return FALSE;
- if (i == join->const_tables)
+ if (cache_level == 0 || i == join->const_tables)
return FALSE;
+
if (options & SELECT_NO_JOIN_CACHE)
goto no_join_cache;
+ /*
+ psergey-todo: why the below when execution code seems to handle the
+ "range checked for each record" case?
+ */
if (tab->use_quick == 2)
goto no_join_cache;
+
+ /*
+ Non-linked join buffers can't guarantee one match
+ */
if (force_unlinked_cache &&
(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;
- if (!(i <= no_jbuf_after) || tab->loosescan_match_tab ||
- sj_is_materialize_strategy(join->best_positions[i].sj_strategy))
- goto no_join_cache;
+
/*
- Temporary and overly-restrictive:
+ Don't use join buffering if we're dictated not to by no_jbuf_after (this
+ ...)
*/
- if (tab->emb_sj_nest || join->best_positions[i].sj_strategy != SJ_OPT_NONE)
+ if (!(i <= no_jbuf_after) || tab->loosescan_match_tab ||
+ sj_is_materialize_strategy(join->best_positions[i].sj_strategy))
goto no_join_cache;
for (JOIN_TAB *first_inner= tab->first_inner; first_inner;
@@ -15899,6 +15917,7 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi
end_of_records);
}
+
/*
Fill the join buffer with partial records, retrieve all full matches for them
| Thread |
|---|
| • bzr commit into mysql-6.0-opt-subqueries branch (sergefp:2745) WL#4688 | Sergey Petrunia | 22 Dec |
