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

Sergey Petrunya psergey at askmonty.org
Wed Nov 3 16:31:09 EET 2010


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 at askmonty.org-20101103192618-17ii8dyn1h2qzdy8
> 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 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 @@
>  	                                  &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 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 at 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


More information about the commits mailing list