[Commits] Rev 2845: Fixed LP bug #664594 and other bugs leading to invalid execution in file:///home/igor/maria/maria-5.3-mwl128/

Igor Babaev igor at askmonty.org
Wed Nov 3 18:20:03 EET 2010


At file:///home/igor/maria/maria-5.3-mwl128/

------------------------------------------------------------
revno: 2845
revision-id: igor at askmonty.org-20101103162002-ym6w7ghfgh5q8u0q
parent: igor at askmonty.org-20101102235032-vh451jmuugv1gsr2
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.3-mwl128
timestamp: Wed 2010-11-03 09:20:02 -0700
message:
  Fixed LP bug #664594 and other bugs leading to invalid execution
  plans or wrong results due to the fact that JOIN_CACHE function
  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.
-------------- next part --------------
=== 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 16:20:02 +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 16:20:02 +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 16:20:02 +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 16:20:02 +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 16:20:02 +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 16:20:02 +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 16:20:02 +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 16:20:02 +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 16:20:02 +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 16:20:02 +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 16:20:02 +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 16:20:02 +0000
@@ -137,7 +137,44 @@
   *descr_ptr= copy_ptr;
   return len;
 }
-    
+
+/* 
+  Get the next table whose records are stored in the join buffer if 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 @@
 	                                  &copy);
 
   /* 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, &copy,
@@ -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 16:20:02 +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)
 {
@@ -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,47 @@
 
       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);
 
+    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 +7881,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 +7900,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 +7914,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 +9575,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 +10646,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 16:20:02 +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;



More information about the commits mailing list