Hello Igor, Ok to push.
On Wed, Nov 03, 2010 at 12:26:19PM -0700, Igor Babaev wrote: > At file:///home/igor/maria/maria-5.3-mwl128/ > > ------------------------------------------------------------ > revno: 2845 > revision-id: [email protected] > parent: [email protected] > committer: Igor Babaev <[email protected]> > branch nick: maria-5.3-mwl128 > timestamp: Wed 2010-11-03 12:26:18 -0700 > message: > Fixed LP bug #664594 and other bugs leading to invalid execution > plans or wrong results due to the fact that JOIN_CACHE functions > ignored the possibility of interleaving materialized semijoin > tables with tables whose records were stored in join buffers. > This fixes would become mostly unnecessary if the new code of > mwl 90 was merged into 5.3 right now. > Yet the fix the code of optimize_wo_join_buffering was needed > in any case. > === modified file 'mysql-test/r/explain.result' > --- a/mysql-test/r/explain.result 2010-10-27 23:31:22 +0000 > +++ b/mysql-test/r/explain.result 2010-11-03 19:26:18 +0000 > @@ -195,16 +195,16 @@ > flush tables; > EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM > t2 AS INNR WHERE OUTR.dt IS NULL ); > id select_type table type possible_keys key key_len ref > rows Extra > -1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using > where > -1 PRIMARY INNR ALL NULL NULL NULL NULL 2 Using > where; FirstMatch(OUTR) > +1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using > where; Start temporary > +1 PRIMARY INNR ALL NULL NULL NULL NULL 2 Using > where; End temporary; Using join buffer (flat, BNL join) > flush tables; > SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS > INNR WHERE OUTR.dt IS NULL ); > dt > flush tables; > EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt > FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' ); > id select_type table type possible_keys key key_len ref > rows Extra > -1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using > where > -1 PRIMARY INNR ALL NULL NULL NULL NULL 2 Using > where; FirstMatch(OUTR) > +1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using > where; Start temporary > +1 PRIMARY INNR ALL NULL NULL NULL NULL 2 Using > where; End temporary; Using join buffer (flat, BNL join) > flush tables; > SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS > INNR WHERE OUTR.t < '2005-11-13 7:41:31' ); > dt > > === modified file 'mysql-test/r/group_by.result' > --- a/mysql-test/r/group_by.result 2010-10-27 23:31:22 +0000 > +++ b/mysql-test/r/group_by.result 2010-11-03 19:26:18 +0000 > @@ -1543,7 +1543,8 @@ > (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2)); > id select_type table type possible_keys key key_len ref > rows Extra > 1 PRIMARY t1 index PRIMARY,i2 PRIMARY 4 NULL 144 > Using index > -1 PRIMARY t1 ALL NULL NULL NULL NULL 144 Using > where; FirstMatch(t1) > +1 PRIMARY subselect2 eq_ref unique_key unique_key 4 > func 1 > +2 SUBQUERY t1 ALL NULL NULL NULL NULL 144 > CREATE TABLE t2 (a INT, b INT, KEY(a)); > INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4); > EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2; > > === modified file 'mysql-test/r/subselect.result' > --- a/mysql-test/r/subselect.result 2010-10-27 23:31:22 +0000 > +++ b/mysql-test/r/subselect.result 2010-11-03 19:26:18 +0000 > @@ -2831,9 +2831,10 @@ > 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 > -1 PRIMARY t2 ALL NULL NULL NULL NULL 9 100.00 > Using where; FirstMatch(t1) > +1 PRIMARY subselect2 eq_ref unique_key unique_key 10 > func 1 1.00 > +2 SUBQUERY t2 ALL NULL NULL NULL NULL 9 > 100.00 Using where > Warnings: > -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')) > +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` > from `test`.`t1` semi join (`test`.`t2`) where ((`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 > @@ -4203,8 +4204,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 index I1 I1 2 NULL 2 Using > where; Using index; LooseScan > -1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 > Using index condition > +1 PRIMARY t1 ALL I2 NULL NULL NULL 2 Using > where > +1 PRIMARY t1 ref I1 I1 2 test.t1.b 2 > Using where; Using index; FirstMatch(t1) > SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); > a b > CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10)); > @@ -4213,15 +4214,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 index I1 I1 4 NULL 2 Using > where; Using index; LooseScan > -1 PRIMARY t2 ref I2 I2 13 test.t2.a 2 > Using index condition > +1 PRIMARY t2 ALL I2 NULL NULL NULL 2 Using > where > +1 PRIMARY t2 ref I1 I1 4 test.t2.b 2 > Using where; Using index; FirstMatch(t2) > 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 index I1 I1 2 NULL 2 Using > where; Using index; LooseScan > -1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 > Using index condition > +1 PRIMARY t1 ALL I2 NULL NULL NULL 2 Using > where > +1 PRIMARY t1 ref I1 I1 2 test.t1.b 2 > Using where; Using index; FirstMatch(t1) > 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/r/subselect3.result' > --- a/mysql-test/r/subselect3.result 2010-10-27 23:31:22 +0000 > +++ b/mysql-test/r/subselect3.result 2010-11-03 19:26:18 +0000 > @@ -103,7 +103,7 @@ > 1 1 > show status like '%Handler_read_rnd_next'; > Variable_name Value > -Handler_read_rnd_next 5 > +Handler_read_rnd_next 11 > delete from t2; > insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0); > set optimizer_switch='subquery_cache=off'; > @@ -1112,7 +1112,8 @@ > 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>) > +1 PRIMARY subselect3 eq_ref unique_key unique_key 5 > func 1 > +3 SUBQUERY t1 ALL NULL NULL NULL NULL 20 > 2 DERIVED t0 ALL NULL NULL NULL NULL 11 > drop table t0, t1; > create table t0 (a int); > @@ -1124,16 +1125,18 @@ > 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 (flat, BNL join) > +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 > +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 > func 1 > +2 SUBQUERY t1 range kp1 kp1 5 NULL 48 > Using where; Using index > 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 where; 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 (flat, BNL join) > +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 > +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 > func 1 > +2 SUBQUERY t1 range kp1 kp1 5 NULL 48 > Using index condition; Using where; Using MRR > +2 SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c > 1 Using index > 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; > @@ -1261,12 +1264,14 @@ > 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 (flat, BNL join) > +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using > where > +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 > func 1 > +2 SUBQUERY t1 ref a a 10 const,test.t2.a > 8 Using index > 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 (flat, BNL join) > +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using > where > +1 PRIMARY subselect2 eq_ref unique_key unique_key 10 > func 1 > +2 SUBQUERY t1 ref a a 10 const,test.t2.a > 8 Using index > drop table t1,t2; > create table t1 (a int, b int); > insert into t1 select a,a from t0; > @@ -1295,7 +1300,8 @@ > 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) > +1 PRIMARY subselect2 eq_ref unique_key unique_key 3 > func 1 > +2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 > select * from t0 where a in (select a from t1); > a > 10.24 > @@ -1308,7 +1314,8 @@ > 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) > +1 PRIMARY subselect2 eq_ref unique_key unique_key 4 > func 1 > +2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 > select * from t0 where a in (select a from t1); > a > 2008-01-01 > > === modified file 'mysql-test/r/subselect3_jcl6.result' > --- a/mysql-test/r/subselect3_jcl6.result 2010-10-27 23:31:22 +0000 > +++ b/mysql-test/r/subselect3_jcl6.result 2010-11-03 19:26:18 +0000 > @@ -110,7 +110,7 @@ > 1 1 > show status like '%Handler_read_rnd_next'; > Variable_name Value > -Handler_read_rnd_next 5 > +Handler_read_rnd_next 11 > delete from t2; > insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0); > set optimizer_switch='subquery_cache=off'; > @@ -1031,7 +1031,7 @@ > 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 subselect2 ALL unique_key NULL NULL NULL > 8 Using temporary; Using filesort > -1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using > where; Using join buffer (incremental, BNL join) > +1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using > where; Using join buffer (flat, BNL join) > 1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using > where; Using join buffer (incremental, BNL join) > 2 SUBQUERY t11 ALL NULL NULL NULL NULL 8 > Using where > 2 SUBQUERY t12 ALL NULL NULL NULL NULL 8 > Using where; Using join buffer (flat, BNL join) > @@ -1039,7 +1039,6 @@ > 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); > @@ -1120,7 +1119,8 @@ > 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 (flat, BNL join) > +1 PRIMARY subselect3 eq_ref unique_key unique_key 5 > func 1 > +3 SUBQUERY t1 ALL NULL NULL NULL NULL 20 > 2 DERIVED t0 ALL NULL NULL NULL NULL 11 > drop table t0, t1; > create table t0 (a int); > @@ -1132,16 +1132,18 @@ > 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 (flat, BNL join) > +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 > +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 > func 1 > +2 SUBQUERY t1 range kp1 kp1 5 NULL 48 > Using where; Using index > 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 where; 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 (flat, BNL join) > +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 > +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 > func 1 > +2 SUBQUERY t1 range kp1 kp1 5 NULL 48 > Using index condition; Using where; Using MRR > +2 SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c > 1 Using index > 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; > @@ -1269,12 +1271,14 @@ > 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 (flat, BNL join) > +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using > where > +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 > func 1 > +2 SUBQUERY t1 ref a a 10 const,test.t2.a > 8 Using index > 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 (flat, BNL join) > +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using > where > +1 PRIMARY subselect2 eq_ref unique_key unique_key 10 > func 1 > +2 SUBQUERY t1 ref a a 10 const,test.t2.a > 8 Using index > drop table t1,t2; > create table t1 (a int, b int); > insert into t1 select a,a from t0; > @@ -1303,7 +1307,8 @@ > 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 (flat, BNL join) > +1 PRIMARY subselect2 eq_ref unique_key unique_key 3 > func 1 > +2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 > select * from t0 where a in (select a from t1); > a > 10.24 > @@ -1316,7 +1321,8 @@ > 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 (flat, BNL join) > +1 PRIMARY subselect2 eq_ref unique_key unique_key 4 > func 1 > +2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 > select * from t0 where a in (select a from t1); > a > 2008-01-01 > @@ -1404,7 +1410,7 @@ > ); > id select_type table type possible_keys key key_len ref > rows filtered Extra > 1 PRIMARY subselect2 ALL unique_key NULL NULL NULL > 2 1.00 > -1 PRIMARY a index PRIMARY PRIMARY 4 NULL 2 100.00 > Using where; Using index; Using join buffer (incremental, BNL join) > +1 PRIMARY a index PRIMARY PRIMARY 4 NULL 2 100.00 > Using where; Using index; Using join buffer (flat, BNL join) > 2 SUBQUERY cona ALL NULL NULL NULL NULL 2 > 100.00 Using where > 2 SUBQUERY c eq_ref PRIMARY PRIMARY 4 > test.cona.idContact 1 100.00 Using join buffer (flat, BKA join) > Warnings: > > === modified file 'mysql-test/r/subselect_no_mat.result' > --- a/mysql-test/r/subselect_no_mat.result 2010-10-27 23:31:22 +0000 > +++ b/mysql-test/r/subselect_no_mat.result 2010-11-03 19:26:18 +0000 > @@ -4207,8 +4207,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 index I1 I1 2 NULL 2 Using > where; Using index; LooseScan > -1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 > Using index condition > +1 PRIMARY t1 ALL I2 NULL NULL NULL 2 Using > where > +1 PRIMARY t1 ref I1 I1 2 test.t1.b 2 > Using where; Using index; FirstMatch(t1) > SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); > a b > CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10)); > @@ -4217,15 +4217,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 index I1 I1 4 NULL 2 Using > where; Using index; LooseScan > -1 PRIMARY t2 ref I2 I2 13 test.t2.a 2 > Using index condition > +1 PRIMARY t2 ALL I2 NULL NULL NULL 2 Using > where > +1 PRIMARY t2 ref I1 I1 4 test.t2.b 2 > Using where; Using index; FirstMatch(t2) > 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 index I1 I1 2 NULL 2 Using > where; Using index; LooseScan > -1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 > Using index condition > +1 PRIMARY t1 ALL I2 NULL NULL NULL 2 Using > where > +1 PRIMARY t1 ref I1 I1 2 test.t1.b 2 > Using where; Using index; FirstMatch(t1) > 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/r/subselect_sj.result' > --- a/mysql-test/r/subselect_sj.result 2010-10-27 23:31:22 +0000 > +++ b/mysql-test/r/subselect_sj.result 2010-11-03 19:26:18 +0000 > @@ -1062,8 +1062,10 @@ > WHERE t3.val LIKE 'a%' OR t3.val LIKE 'e%'); > id select_type table type possible_keys key key_len ref > rows Extra > 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 > -1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using > where; FirstMatch(t1) > -1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using > where; FirstMatch(t3) > +1 PRIMARY subselect3 eq_ref unique_key unique_key 14 > func 1 > +1 PRIMARY subselect2 eq_ref unique_key unique_key 14 > func 1 > +3 SUBQUERY t3 ALL NULL NULL NULL NULL 5 > Using where > +2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 > Using where > SELECT * > FROM t1 > WHERE t1.val IN (SELECT t2.val FROM t2 > > === modified file 'mysql-test/r/subselect_sj2.result' > --- a/mysql-test/r/subselect_sj2.result 2010-10-18 20:33:05 +0000 > +++ b/mysql-test/r/subselect_sj2.result 2010-11-03 19:26:18 +0000 > @@ -52,7 +52,8 @@ > 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 t3 ALL b NULL NULL NULL 10 > -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using > where; FirstMatch(t3) > +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 > func 1 > +2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 > select * from t3 where b in (select a from t1); > a b pk1 pk2 pk3 > 1 1 1 1 1 > > === modified file 'mysql-test/r/subselect_sj2_jcl6.result' > --- a/mysql-test/r/subselect_sj2_jcl6.result 2010-10-18 20:33:05 +0000 > +++ b/mysql-test/r/subselect_sj2_jcl6.result 2010-11-03 19:26:18 +0000 > @@ -59,7 +59,8 @@ > 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 t3 ALL b NULL NULL NULL 10 > -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using > where; FirstMatch(t3); Using join buffer (flat, BNL join) > +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 > func 1 > +2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 > select * from t3 where b in (select a from t1); > a b pk1 pk2 pk3 > 1 1 1 1 1 > > === modified file 'mysql-test/r/subselect_sj_jcl6.result' > --- a/mysql-test/r/subselect_sj_jcl6.result 2010-10-27 23:31:22 +0000 > +++ b/mysql-test/r/subselect_sj_jcl6.result 2010-11-03 19:26:18 +0000 > @@ -1069,8 +1069,10 @@ > WHERE t3.val LIKE 'a%' OR t3.val LIKE 'e%'); > id select_type table type possible_keys key key_len ref > rows Extra > 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 > -1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using > where; FirstMatch(t1); Using join buffer (flat, BNL join) > -1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using > where; FirstMatch(t3); Using join buffer (incremental, BNL join) > +1 PRIMARY subselect3 eq_ref unique_key unique_key 14 > func 1 > +1 PRIMARY subselect2 eq_ref unique_key unique_key 14 > func 1 > +3 SUBQUERY t3 ALL NULL NULL NULL NULL 5 > Using where > +2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 > Using where > SELECT * > FROM t1 > WHERE t1.val IN (SELECT t2.val FROM t2 > > === modified file 'mysql-test/r/type_datetime.result' > --- a/mysql-test/r/type_datetime.result 2010-06-26 10:05:41 +0000 > +++ b/mysql-test/r/type_datetime.result 2010-11-03 19:26:18 +0000 > @@ -537,8 +537,8 @@ > select * from t1 > where id in (select id from t1 as x1 where (t1.cur_date is null)); > id select_type table type possible_keys key key_len ref > rows filtered Extra > -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 > Using where > -1 PRIMARY x1 ALL NULL NULL NULL NULL 2 100.00 > Using where; FirstMatch(t1) > +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 > Using where; Start temporary > +1 PRIMARY x1 ALL NULL NULL NULL NULL 2 100.00 > Using where; End temporary; Using join buffer (flat, BNL join) > Warnings: > Note 1276 Field or reference 'test.t1.cur_date' of SELECT #2 was resolved > in SELECT #1 > Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`cur_date` AS > `cur_date` from `test`.`t1` semi join (`test`.`t1` `x1`) where > ((`test`.`x1`.`id` = `test`.`t1`.`id`) and (`test`.`t1`.`cur_date` = 0)) > @@ -549,8 +549,8 @@ > select * from t2 > where id in (select id from t2 as x1 where (t2.cur_date is null)); > id select_type table type possible_keys key key_len ref > rows filtered Extra > -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 > Using where > -1 PRIMARY x1 ALL NULL NULL NULL NULL 2 100.00 > Using where; FirstMatch(t2) > +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 > Using where; Start temporary > +1 PRIMARY x1 ALL NULL NULL NULL NULL 2 100.00 > Using where; End temporary; Using join buffer (flat, BNL join) > Warnings: > Note 1276 Field or reference 'test.t2.cur_date' of SELECT #2 was resolved > in SELECT #1 > Note 1003 select `test`.`t2`.`id` AS `id`,`test`.`t2`.`cur_date` AS > `cur_date` from `test`.`t2` semi join (`test`.`t2` `x1`) where > ((`test`.`x1`.`id` = `test`.`t2`.`id`) and (`test`.`t2`.`cur_date` = 0)) > > === modified file 'sql/sql_join_cache.cc' > --- a/sql/sql_join_cache.cc 2010-10-27 23:37:33 +0000 > +++ b/sql/sql_join_cache.cc 2010-11-03 19:26:18 +0000 > @@ -137,7 +137,44 @@ > *descr_ptr= copy_ptr; > return len; > } > - > + > +/* > + Get the next table whose records are stored in the join buffer of this > cache > + > + SYNOPSIS > + get_next_table() > + tab the table for which the next table is to be returned > + > + DESCRIPTION > + For a given table whose records are stored in this cache the function > + returns the next such table if there is any. > + The function takes into account that the tables whose records are > + are stored in the same cache now can interleave with tables from > + materialized semijoin subqueries. > + > + TODO > + This function should be modified/simplified after the new code for > + materialized semijoins is merged. > + > + RETURN > + The next join table whose records are stored in the buffer of this cache > + if such table exists, 0 - otherwise > +*/ > + > +JOIN_TAB *JOIN_CACHE::get_next_table(JOIN_TAB *tab) > +{ > + > + if (++tab == join_tab) > + return NULL; > + if (join_tab->first_sjm_sibling) > + return tab; > + uint i= tab-join->join_tab; > + while (sj_is_materialize_strategy(join->best_positions[i].sj_strategy) && > + i < join->tables) > + i+= join->best_positions[i].n_sj_tables; > + return join->join_tab+i < join_tab ? join->join_tab+i : NULL; > +} > + > > /* > Determine different counters of fields associated with a record in the > cache > @@ -159,7 +196,9 @@ > void JOIN_CACHE::calc_record_fields() > { > JOIN_TAB *tab = prev_cache ? prev_cache->join_tab : > - join->join_tab+join->const_tables; > + (join_tab->first_sjm_sibling ? > + join_tab->first_sjm_sibling : > + join->join_tab+join->const_tables); > tables= join_tab-tab; > > fields= 0; > @@ -169,7 +208,7 @@ > data_field_ptr_count= 0; > referenced_fields= 0; > > - for ( ; tab < join_tab ; tab++) > + for ( ; tab ; tab= get_next_table(tab)) > { > tab->calc_used_field_length(FALSE); > flag_fields+= test(tab->used_null_fields || tab->used_uneven_bit_fields); > @@ -222,7 +261,8 @@ > cache= this; > do > { > - for (tab= cache->join_tab-cache->tables; tab < cache->join_tab ; tab++) > + for (tab= cache->join_tab-cache->tables; tab ; > + tab= cache->get_next_table(tab)) > { > uint key_args; > bitmap_clear_all(&tab->table->tmp_set); > @@ -338,7 +378,7 @@ > ©); > > /* Create fields for all null bitmaps and null row flags that are needed */ > - for (tab= join_tab-tables; tab < join_tab; tab++) > + for (tab= join_tab-tables; tab; tab= get_next_table(tab)) > { > TABLE *table= tab->table; > > @@ -425,7 +465,8 @@ > while (ext_key_arg_cnt) > { > cache= cache->prev_cache; > - for (tab= cache->join_tab-cache->tables; tab < cache->join_tab ; tab++) > + for (tab= cache->join_tab-cache->tables; tab; > + tab= cache->get_next_table(tab)) > { > CACHE_FIELD *copy_end; > MY_BITMAP *key_read_set= &tab->table->tmp_set; > @@ -475,7 +516,7 @@ > > /* Now create local fields that are used to build ref for this key access > */ > copy= field_descr+flag_fields; > - for (tab= join_tab-tables; tab < join_tab ; tab++) > + for (tab= join_tab-tables; tab; tab= get_next_table(tab)) > { > length+= add_table_data_fields_to_join_cache(tab, &tab->table->tmp_set, > &data_field_count, ©, > @@ -531,7 +572,7 @@ > CACHE_FIELD *copy= field_descr+flag_fields+data_field_count; > CACHE_FIELD **copy_ptr= blob_ptr+data_field_ptr_count; > > - for (tab= join_tab-tables; tab < join_tab; tab++) > + for (tab= join_tab-tables; tab; tab= get_next_table(tab)) > { > MY_BITMAP *rem_field_set; > TABLE *table= tab->table; > @@ -1341,6 +1382,7 @@ > end_pos= pos= cp; > *is_full= last_record; > > + last_written_is_null_compl= 0; > if (!join_tab->first_unmatched && join_tab->on_precond) > { > join_tab->found= 0; > @@ -1351,8 +1393,6 @@ > last_written_is_null_compl= 1; > } > } > - else > - last_written_is_null_compl= 0; > > return (uint) (cp-init_pos); > } > > === modified file 'sql/sql_select.cc' > --- a/sql/sql_select.cc 2010-10-30 22:14:36 +0000 > +++ b/sql/sql_select.cc 2010-11-03 19:26:18 +0000 > @@ -7441,6 +7441,7 @@ > join join for which the check is performed > options options of the join > no_jbuf_after don't use join buffering after table with this > number > + prev_tab previous join table > icp_other_tables_ok OUT TRUE if condition pushdown supports > other tables presence > idx_cond_fact_out OUT TRUE if condition pushed to the index is > factored > @@ -7568,6 +7569,7 @@ > uint check_join_cache_usage(JOIN_TAB *tab, > JOIN *join, ulonglong options, > uint no_jbuf_after, > + JOIN_TAB *prev_tab, > bool *icp_other_tables_ok, > bool *idx_cond_fact_out) > { > @@ -7587,7 +7589,7 @@ > > *icp_other_tables_ok= TRUE; > *idx_cond_fact_out= TRUE; > - if (cache_level == 0 || i == join->const_tables) > + if (cache_level == 0 || i == join->const_tables || !prev_tab) > return 0; > > if (options & SELECT_NO_JOIN_CACHE) > @@ -7633,7 +7635,7 @@ > if (tab->first_sj_inner_tab && tab->first_sj_inner_tab != tab && > !tab->first_sj_inner_tab->use_join_cache) > goto no_join_cache; > - if (!tab[-1].use_join_cache) > + if (!prev_tab->use_join_cache) > { > /* > Check whether table tab and the previous one belong to the same nest of > @@ -7655,7 +7657,7 @@ > } > > if (!force_unlinked_cache) > - prev_cache= tab[-1].cache; > + prev_cache= prev_tab->cache; > > switch (tab->type) { > case JT_ALL: > @@ -7807,6 +7809,12 @@ > return TRUE; /* purecov: inspected */ > tab->sorted= TRUE; > } > + > + /* > + SJ-Materialization > + */ > + if (!(i >= first_sjm_table && i < last_sjm_table)) > + tab->first_sjm_sibling= NULL; > if (sj_is_materialize_strategy(join->best_positions[i].sj_strategy)) > { > /* This is a start of semi-join nest */ > @@ -7819,23 +7827,52 @@ > > if (setup_sj_materialization(tab)) > return TRUE; > + for (uint j= first_sjm_table; j != last_sjm_table; j++) > + join->join_tab[j].first_sjm_sibling= join->join_tab + > first_sjm_table; > } > table->status=STATUS_NO_RECORD; > pick_table_access_method (tab); > > + /* > + This loop currently can be executed only once as the function > + check_join_cache_usage does not change the value of tab->type. > + It won't be true for the future code. > + */ > + for ( ; ; ) > + { > + enum join_type tab_type= tab->type; > + switch (tab->type) { > + case JT_SYSTEM: > + case JT_CONST: > + case JT_EQ_REF: > + case JT_REF: > + case JT_REF_OR_NULL: > + case JT_ALL: > + if ((jcl= check_join_cache_usage(tab, join, options, > + no_jbuf_after, > + i == last_sjm_table ? > + join->join_tab+first_sjm_table : > + tab-1, > + &icp_other_tables_ok, > + &idx_cond_fact_out))) > + { > + tab->use_join_cache= TRUE; > + tab[-1].next_select=sub_select_cache; > + } > + break; > + default: > + ; > + } > + if (tab->type == tab_type) > + break; > + } > + > switch (tab->type) { > case JT_SYSTEM: // Only happens with left join > case JT_CONST: // Only happens with left join > /* Only happens with outer joins */ > tab->read_first_record= tab->type == JT_SYSTEM ? > join_read_system :join_read_const; > - if ((jcl= check_join_cache_usage(tab, join, options, > - no_jbuf_after, &icp_other_tables_ok, > - &idx_cond_fact_out))) > - { > - tab->use_join_cache= TRUE; > - tab[-1].next_select=sub_select_cache; > - } > if (table->covering_keys.is_set(tab->ref.key) && > !table->no_keyread) > { > @@ -7849,13 +7886,6 @@ > case JT_EQ_REF: > tab->read_record.unlock_row= join_read_key_unlock_row; > /* fall through */ > - if ((jcl= check_join_cache_usage(tab, join, options, > - no_jbuf_after, &icp_other_tables_ok, > - &idx_cond_fact_out))) > - { > - tab->use_join_cache= TRUE; > - tab[-1].next_select=sub_select_cache; > - } > if (table->covering_keys.is_set(tab->ref.key) && > !table->no_keyread) > { > @@ -7875,13 +7905,6 @@ > } > delete tab->quick; > tab->quick=0; > - if ((jcl= check_join_cache_usage(tab, join, options, > - no_jbuf_after, &icp_other_tables_ok, > - &idx_cond_fact_out))) > - { > - tab->use_join_cache= TRUE; > - tab[-1].next_select=sub_select_cache; > - } > if (table->covering_keys.is_set(tab->ref.key) && > !table->no_keyread) > table->enable_keyread(); > @@ -7896,12 +7919,6 @@ > Also don't use cache if this is the first table in semi-join > materialization nest. > */ > - if (check_join_cache_usage(tab, join, options, no_jbuf_after, > - &icp_other_tables_ok, &idx_cond_fact_out)) > - { > - tab->use_join_cache= TRUE; > - tab[-1].next_select=sub_select_cache; > - } > /* These init changes read_record */ > if (tab->use_quick == 2) > { > @@ -9563,6 +9580,11 @@ > Item_equal *upper= item_field->find_item_equal(upper_levels); > Item_field *item= item_field; > TABLE_LIST *field_sjm= embedding_sjm(item_field); > + if (!field_sjm) > + { > + current_sjm= NULL; > + current_sjm_head= NULL; > + } > > /* > Check if "item_field=head" equality is already guaranteed to be true > @@ -10629,7 +10651,7 @@ > { > /* Find the best access method that would not use join buffering */ > best_access_path(join, rs, reopt_remaining_tables, i, > - test(i < no_jbuf_before), rec_count, > + TRUE, rec_count, > &pos, &loose_scan_pos); > } > else > > === modified file 'sql/sql_select.h' > --- a/sql/sql_select.h 2010-10-30 22:14:36 +0000 > +++ b/sql/sql_select.h 2010-11-03 19:26:18 +0000 > @@ -306,6 +306,8 @@ > */ > uint sj_strategy; > > + struct st_join_table *first_sjm_sibling; > + > void cleanup(); > inline bool is_using_loose_index_scan() > { > @@ -1035,6 +1037,8 @@ > buff= 0; > } > > + JOIN_TAB *get_next_table(JOIN_TAB *tab); > + > friend class JOIN_CACHE_HASHED; > friend class JOIN_CACHE_BNL; > friend class JOIN_CACHE_BKA; > > _______________________________________________ > commits mailing list > [email protected] > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits -- BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
