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: igor@askmonty.org-20101103192618-17ii8dyn1h2qzdy8 parent: igor@askmonty.org-20101102235032-vh451jmuugv1gsr2 committer: Igor Babaev <igor@askmonty.org> 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 commits@mariadb.org 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