List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:June 2 2010 8:52am
Subject:Re: bzr commit into mysql-next-mr-bugfixing branch (guilhem:3183)
Bug#50358 Bug#51018
View as plain text  
Hi Guilhem,

review for this proposed bug fix # 1.

I think the fix solves the problem in a straightforward manner, although ideally 
I think that your other proposal is a better long-term solution.

Guilhem Bichot wrote:
> #At file:///home/mysql_src/bzrrepos_new/opt-back-53970/ based on
> revid:epotemkin@stripped
> 
>  3183 Guilhem Bichot	2010-05-30
>       Fix for BUG#50358 "semijoin execution of subquery with outerjoin yields wrong
> result"
>       and BUG#51018 "Batched key access gives wrong results for SELECT with semijoin
> and NULL":
>       semijoin materialization was not fully disabled when doing join buffering.
>       This is the first possible fix, it has the advantage of changing little code,
>       and the disadvantage of adding some loops which in total scale like 0.5*(N^2)
>       where N is the number of tables in the join (exception: no added cost if
>       there are no semijoins in the query). Another fix with a different approach
>       will be submitted next.
>      @ mysql-test/r/subselect3.result
>         those plans accidentally had join buffering enabled for the non-first
>         inner tables in a semijoin materialization nest
>      @ mysql-test/r/subselect3_jcl6.result
>         those plans accidentally had join buffering enabled for the non-first
>         inner tables in a semijoin materialization nest.
>         The test where one 256,67,NULL line disappears, is exactly the test for
> BUG#51018.
>      @ mysql-test/r/subselect4.result
>         those plans accidentally had join buffering enabled for the non-first
>         inner tables in a semijoin materialization nest
>      @ mysql-test/r/subselect_sj.result
>         those plans accidentally had join buffering enabled for the non-first
>         inner tables in a semijoin materialization nest
>      @ mysql-test/r/subselect_sj2.result
>         result for test
>      @ mysql-test/r/subselect_sj2_jcl6.result
>         before this fix, the result of SELECT would be 2 and 2 which is wrong
>      @ mysql-test/r/subselect_sj_jcl6.result
>         those plans accidentally had join buffering enabled for the non-first
>         inner tables in a semijoin materialization nest
>      @ mysql-test/t/subselect_sj2.test
>         test for BUG#51018
>      @ sql/sql_select.cc
>         In check_join_cache_usage(), a TODO comment reflects that we don't support
> join buffering
>         in semijoin inner tables handled with materialization. This was already
> enforced with test
>         sj_is_materialize_strategy(join->best_positions[i].sj_strategy); but, as
> the strategy
>         is SJ_OPT_NONE for all non-first inner tables, this test let non-first inner
> tables
>         do materialization. That led to wrong results. The fix is to properly
> enforce
>         the limitation: scan previous JOIN_TABs until finding a first inner table
>         with materialization strategy. We do this search only if there are semijoin
> nests.
>         Another existing limitation, for FirstMatch, is groupped under the same
> if(),
>         as an optimization.
> 
>     modified:
>       mysql-test/r/subselect3.result
>       mysql-test/r/subselect3_jcl6.result
>       mysql-test/r/subselect4.result
>       mysql-test/r/subselect_sj.result
>       mysql-test/r/subselect_sj2.result
>       mysql-test/r/subselect_sj2_jcl6.result
>       mysql-test/r/subselect_sj_jcl6.result
>       mysql-test/t/subselect_sj2.test
>       sql/sql_select.cc
> === modified file 'mysql-test/r/subselect3.result'
> --- a/mysql-test/r/subselect3.result	2010-05-19 14:44:18 +0000
> +++ b/mysql-test/r/subselect3.result	2010-05-30 19:41:21 +0000
> @@ -1059,7 +1059,7 @@ explain select t21.* from t21,t22 where 
>  t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and
> t11.c is null) and t22.c is null order by t21.a;
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	PRIMARY	t11	ALL	NULL	NULL	NULL	NULL	8	Using where; Using temporary; Using
> filesort; Start materialize; Scan
> -1	PRIMARY	t12	ALL	NULL	NULL	NULL	NULL	8	Using where; End materialize; Using join
> buffer
> +1	PRIMARY	t12	ALL	NULL	NULL	NULL	NULL	8	Using where; End materialize
>  1	PRIMARY	t22	ALL	NULL	NULL	NULL	NULL	26	Using where; Using join buffer
>  1	PRIMARY	t21	ALL	NULL	NULL	NULL	NULL	26	Using where; Using join buffer
>  select t21.* from t21,t22 where t21.a = t22.a and 
> @@ -1416,8 +1416,8 @@ explain select * from t1 where (a,b,c) i
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
>  1	PRIMARY	X	ALL	NULL	NULL	NULL	NULL	6	Using where; Start materialize
> -1	PRIMARY	Y	ALL	NULL	NULL	NULL	NULL	6	Using join buffer
> -1	PRIMARY	Z	ALL	NULL	NULL	NULL	NULL	6	End materialize; Using join buffer
> +1	PRIMARY	Y	ALL	NULL	NULL	NULL	NULL	6	
> +1	PRIMARY	Z	ALL	NULL	NULL	NULL	NULL	6	End materialize
>  drop table t0,t1,t2;
>  
>  BUG#40118 Crash when running Batched Key Access and requiring one match for each
> key
> 
> === modified file 'mysql-test/r/subselect3_jcl6.result'
> --- a/mysql-test/r/subselect3_jcl6.result	2010-05-19 14:44:18 +0000
> +++ b/mysql-test/r/subselect3_jcl6.result	2010-05-30 19:41:21 +0000
> @@ -1063,14 +1063,13 @@ explain select t21.* from t21,t22 where 
>  t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and
> t11.c is null) and t22.c is null order by t21.a;
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	PRIMARY	t11	ALL	NULL	NULL	NULL	NULL	8	Using where; Using temporary; Using
> filesort; Start materialize; Scan
> -1	PRIMARY	t12	ALL	NULL	NULL	NULL	NULL	8	Using where; End materialize; Using join
> buffer
> +1	PRIMARY	t12	ALL	NULL	NULL	NULL	NULL	8	Using where; End materialize
>  1	PRIMARY	t22	ALL	NULL	NULL	NULL	NULL	26	Using where; Using join buffer
>  1	PRIMARY	t21	ALL	NULL	NULL	NULL	NULL	26	Using where; Using join buffer
>  select t21.* from t21,t22 where t21.a = t22.a and 
>  t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and
> t11.c is null) and t22.c is null order by t21.a;
>  a	b	c
>  256	67	NULL
> -256	67	NULL
>  drop table t1, t11, t12, t21, t22;
>  create table t1(a int);
>  insert into t1 values (0),(1);
> @@ -1421,8 +1420,8 @@ explain select * from t1 where (a,b,c) i
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
>  1	PRIMARY	X	ALL	NULL	NULL	NULL	NULL	6	Using where; Start materialize
> -1	PRIMARY	Y	ALL	NULL	NULL	NULL	NULL	6	Using join buffer
> -1	PRIMARY	Z	ALL	NULL	NULL	NULL	NULL	6	End materialize; Using join buffer
> +1	PRIMARY	Y	ALL	NULL	NULL	NULL	NULL	6	
> +1	PRIMARY	Z	ALL	NULL	NULL	NULL	NULL	6	End materialize
>  drop table t0,t1,t2;
>  
>  BUG#40118 Crash when running Batched Key Access and requiring one match for each
> key
> @@ -1478,7 +1477,7 @@ WHERE cona.postalStripped='T2H3B2'
>  	);
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
>  1	PRIMARY	cona	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Start materialize;
> Scan
> -1	PRIMARY	c	eq_ref	PRIMARY	PRIMARY	4	test.cona.idContact	1	100.00	End materialize;
> Using join buffer
> +1	PRIMARY	c	eq_ref	PRIMARY	PRIMARY	4	test.cona.idContact	1	100.00	End materialize
>  1	PRIMARY	a	index	PRIMARY	PRIMARY	4	NULL	2	100.00	Using where; Using index; Using
> join buffer
>  Warnings:
>  Note	1003	select `test`.`a`.`idIndividual` AS `idIndividual` from `test`.`t1` `a`
> semi join (`test`.`t3` `cona` join `test`.`t2` `c`) where ((`test`.`c`.`idContact` =
> `test`.`cona`.`idContact`) and (`test`.`a`.`idIndividual` = `test`.`c`.`idObj`) and
> (`test`.`cona`.`postalStripped` = 'T2H3B2'))
> 
> === modified file 'mysql-test/r/subselect4.result'
> --- a/mysql-test/r/subselect4.result	2010-05-19 14:44:18 +0000
> +++ b/mysql-test/r/subselect4.result	2010-05-30 19:41:21 +0000
> @@ -261,7 +261,7 @@ WHERE PTYPE = 'Design'));
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	
>  1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start materialize
> -1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; End materialize; Using join
> buffer
> +1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; End materialize
>  PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
>  FROM t1
>  WHERE EMPNUM IN
> @@ -275,12 +275,12 @@ EXECUTE stmt;
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	
>  1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start materialize
> -1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; End materialize; Using join
> buffer
> +1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; End materialize
>  EXECUTE stmt;
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	SIMPLE	t1	ALL	t1_IDX	NULL	NULL	NULL	5	
>  1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start materialize
> -1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; End materialize; Using join
> buffer
> +1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; End materialize
>  DEALLOCATE PREPARE stmt;
>  DROP INDEX t1_IDX ON t1;
>  CREATE INDEX t1_IDX ON t1(EMPNUM);
> @@ -296,7 +296,7 @@ WHERE PTYPE = 'Design'));
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	
>  1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start materialize
> -1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; End materialize; Using join
> buffer
> +1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; End materialize
>  PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
>  FROM t1
>  WHERE EMPNUM IN
> @@ -310,12 +310,12 @@ EXECUTE stmt;
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	
>  1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start materialize
> -1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; End materialize; Using join
> buffer
> +1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; End materialize
>  EXECUTE stmt;
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	SIMPLE	t1	ALL	t1_IDX	NULL	NULL	NULL	5	
>  1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start materialize
> -1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; End materialize; Using join
> buffer
> +1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; End materialize
>  DEALLOCATE PREPARE stmt;
>  DROP INDEX t1_IDX ON t1;
>  EXPLAIN SELECT EMPNAME
> @@ -330,7 +330,7 @@ WHERE PTYPE = 'Design'));
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	
>  1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start materialize
> -1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; End materialize; Using join
> buffer
> +1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; End materialize
>  PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
>  FROM t1
>  WHERE EMPNUM IN
> @@ -344,12 +344,12 @@ EXECUTE stmt;
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	
>  1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start materialize
> -1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; End materialize; Using join
> buffer
> +1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; End materialize
>  EXECUTE stmt;
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
>  1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start materialize
> -1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; End materialize; Using join
> buffer
> +1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; End materialize
>  DEALLOCATE PREPARE stmt;
>  SET SESSION optimizer_switch = @old_optimizer_switch;
>  SET SESSION optimizer_join_cache_level = @old_optimizer_join_cache_level;
> @@ -415,7 +415,7 @@ FROM t3));
>  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	
>  1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Start materialize
> -1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	End materialize; Using join buffer
> +1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	End materialize
>  Warnings:
>  Note	1003	select `test`.`t1`.`id` AS `id` from `test`.`t1` semi join (`test`.`t3`
> join `test`.`t2`) where 1
>  delete from t2;
> @@ -453,8 +453,8 @@ FROM t4)));
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	9	
>  1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Start materialize
> -1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	9	Using join buffer
> -1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	9	End materialize; Using join buffer
> +1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	9	
> +1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	9	End materialize
>  SELECT *
>  FROM t1
>  WHERE 1 IN(SELECT 1
> 
> === modified file 'mysql-test/r/subselect_sj.result'
> --- a/mysql-test/r/subselect_sj.result	2010-05-18 11:01:08 +0000
> +++ b/mysql-test/r/subselect_sj.result	2010-05-30 19:41:21 +0000
> @@ -1131,7 +1131,7 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
>  1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
> -1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	Using where; End materialize; Using join
> buffer
> +1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	Using where; End materialize
>  SELECT * FROM t1 WHERE (t1.i) IN 
>  (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
>  i
> @@ -1161,7 +1161,7 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN 
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
>  1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
> -1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	Using where; End materialize; Using join
> buffer
> +1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	Using where; End materialize
>  SELECT * FROM t1 WHERE (11) IN 
>  (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
>  i
> @@ -1722,7 +1722,7 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
>  (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize; Scan
> -1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	End materialize; Using join buffer
> +1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	End materialize
>  1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer
>  SELECT * FROM t1 WHERE (t1.i) IN 
>  (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
> @@ -1752,7 +1752,7 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN 
>  (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
> -1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	Using where; End materialize; Using join
> buffer
> +1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	Using where; End materialize
>  1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using join buffer
>  SELECT * FROM t1 WHERE (11) IN 
>  (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
> @@ -1787,7 +1787,7 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
>  (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize; Scan
> -1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	End materialize; Using join buffer
> +1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	End materialize
>  1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer
>  SELECT * FROM t1 WHERE (t1.i) IN 
>  (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
> @@ -1860,7 +1860,7 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
>  1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
> -1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	End materialize; Using join buffer
> +1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	End materialize
>  SELECT * FROM t1 WHERE (t1.i) IN 
>  (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
>  i
> @@ -1898,7 +1898,7 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN 
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
>  1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
> -1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize; Using join
> buffer
> +1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
>  SELECT * FROM t1 WHERE (11) IN 
>  (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
>  i
> 
> === modified file 'mysql-test/r/subselect_sj2.result'
> --- a/mysql-test/r/subselect_sj2.result	2010-05-19 14:44:18 +0000
> +++ b/mysql-test/r/subselect_sj2.result	2010-05-30 19:41:21 +0000
> @@ -717,3 +717,26 @@ DROP PROCEDURE p1;
>  DROP PROCEDURE p2;
>  DROP PROCEDURE p3;
>  DROP PROCEDURE p4;
> +#
> +# Bug#53058 - semijoin execution of subquery with outerjoin yields wrong result
> +#
> +CREATE TABLE t1 (i INTEGER);
> +CREATE TABLE t2 (i INTEGER);
> +CREATE TABLE t3 (i INTEGER);
> +INSERT INTO t1 VALUES (1), (2);
> +INSERT INTO t2 VALUES (6);
> +INSERT INTO t3 VALUES (1), (2);
> +explain extended SELECT * FROM t1 WHERE (t1.i) IN 
> +(SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i);
> +id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
> +1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Start materialize; Scan
> +1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; End materialize
> +1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer
> +Warnings:
> +Note	1003	select `test`.`t1`.`i` AS `i` from `test`.`t1` semi join (`test`.`t3` left
> join `test`.`t2` on((`test`.`t2`.`i` = `test`.`t3`.`i`))) where (`test`.`t1`.`i` =
> `test`.`t3`.`i`)
> +SELECT * FROM t1 WHERE (t1.i) IN 
> +(SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i);
> +i
> +1
> +2
> +drop table t1,t2,t3;
> 
> === modified file 'mysql-test/r/subselect_sj2_jcl6.result'
> --- a/mysql-test/r/subselect_sj2_jcl6.result	2010-05-19 14:44:18 +0000
> +++ b/mysql-test/r/subselect_sj2_jcl6.result	2010-05-30 19:41:21 +0000
> @@ -720,6 +720,29 @@ DROP PROCEDURE p1;
>  DROP PROCEDURE p2;
>  DROP PROCEDURE p3;
>  DROP PROCEDURE p4;
> +#
> +# Bug#53058 - semijoin execution of subquery with outerjoin yields wrong result
> +#
> +CREATE TABLE t1 (i INTEGER);
> +CREATE TABLE t2 (i INTEGER);
> +CREATE TABLE t3 (i INTEGER);
> +INSERT INTO t1 VALUES (1), (2);
> +INSERT INTO t2 VALUES (6);
> +INSERT INTO t3 VALUES (1), (2);
> +explain extended SELECT * FROM t1 WHERE (t1.i) IN 
> +(SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i);
> +id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
> +1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Start materialize; Scan
> +1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; End materialize
> +1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer
> +Warnings:
> +Note	1003	select `test`.`t1`.`i` AS `i` from `test`.`t1` semi join (`test`.`t3` left
> join `test`.`t2` on((`test`.`t2`.`i` = `test`.`t3`.`i`))) where (`test`.`t1`.`i` =
> `test`.`t3`.`i`)
> +SELECT * FROM t1 WHERE (t1.i) IN 
> +(SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i);
> +i
> +1
> +2
> +drop table t1,t2,t3;
>  set optimizer_join_cache_level=default;
>  show variables like 'optimizer_join_cache_level';
>  Variable_name	Value
> 
> === modified file 'mysql-test/r/subselect_sj_jcl6.result'
> --- a/mysql-test/r/subselect_sj_jcl6.result	2010-05-18 11:01:08 +0000
> +++ b/mysql-test/r/subselect_sj_jcl6.result	2010-05-30 19:41:21 +0000
> @@ -1135,7 +1135,7 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
>  1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
> -1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	Using where; End materialize; Using join
> buffer
> +1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	Using where; End materialize
>  SELECT * FROM t1 WHERE (t1.i) IN 
>  (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
>  i
> @@ -1165,7 +1165,7 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN 
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
>  1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
> -1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	Using where; End materialize; Using join
> buffer
> +1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	Using where; End materialize
>  SELECT * FROM t1 WHERE (11) IN 
>  (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
>  i
> @@ -1642,12 +1642,12 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
>  (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Start materialize; Scan
> -1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; End materialize; Using join
> buffer
> +1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; End materialize
>  1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer
>  SELECT * FROM t1 WHERE (t1.i) IN 
>  (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
>  i
> -1
> +2
>  1
>  EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN 
>  (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
> @@ -1726,7 +1726,7 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
>  (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize; Scan
> -1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	End materialize; Using join buffer
> +1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	End materialize
>  1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer
>  SELECT * FROM t1 WHERE (t1.i) IN 
>  (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
> @@ -1756,7 +1756,7 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN 
>  (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
> -1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	Using where; End materialize; Using join
> buffer
> +1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	0	Using where; End materialize
>  1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using join buffer
>  SELECT * FROM t1 WHERE (11) IN 
>  (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
> @@ -1771,7 +1771,7 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
>  (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize; Scan
> -1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where; End materialize; Using join
> buffer
> +1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where; End materialize
>  1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer
>  SELECT * FROM t1 WHERE (t1.i) IN 
>  (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
> @@ -1791,7 +1791,7 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
>  (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize; Scan
> -1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	End materialize; Using join buffer
> +1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	End materialize
>  1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer
>  SELECT * FROM t1 WHERE (t1.i) IN 
>  (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
> @@ -1842,7 +1842,7 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
>  1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
> -1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize; Using join
> buffer
> +1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
>  SELECT * FROM t1 WHERE (t1.i) IN 
>  (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
>  i
> @@ -1853,7 +1853,7 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
>  1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Start materialize
> -1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize; Using join
> buffer
> +1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
>  SELECT * FROM t1 WHERE (t1.i) IN 
>  (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
>  i
> @@ -1864,7 +1864,7 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
>  1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
> -1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	End materialize; Using join buffer
> +1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	End materialize
>  SELECT * FROM t1 WHERE (t1.i) IN 
>  (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
>  i
> @@ -1875,7 +1875,7 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN 
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
>  1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
> -1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize; Using join
> buffer
> +1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
>  SELECT * FROM t1 WHERE (11) IN 
>  (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
>  i
> @@ -1893,7 +1893,7 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN 
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
>  1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Start materialize
> -1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize; Using join
> buffer
> +1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
>  SELECT * FROM t1 WHERE (11) IN 
>  (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
>  i
> @@ -1902,7 +1902,7 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN 
>  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
>  1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
>  1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
> -1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize; Using join
> buffer
> +1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
>  SELECT * FROM t1 WHERE (11) IN 
>  (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
>  i
> 
> === modified file 'mysql-test/t/subselect_sj2.test'
> --- a/mysql-test/t/subselect_sj2.test	2010-05-11 07:38:36 +0000
> +++ b/mysql-test/t/subselect_sj2.test	2010-05-30 19:41:21 +0000
> @@ -904,3 +904,18 @@ DROP PROCEDURE p1;
>  DROP PROCEDURE p2;
>  DROP PROCEDURE p3;
>  DROP PROCEDURE p4;
> +
> +--echo #
> +--echo # Bug#53058 - semijoin execution of subquery with outerjoin yields wrong
> result
> +--echo #
> +CREATE TABLE t1 (i INTEGER);
> +CREATE TABLE t2 (i INTEGER);
> +CREATE TABLE t3 (i INTEGER);
> +INSERT INTO t1 VALUES (1), (2);
> +INSERT INTO t2 VALUES (6);
> +INSERT INTO t3 VALUES (1), (2);
> +explain extended SELECT * FROM t1 WHERE (t1.i) IN 
> +(SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i);
> +SELECT * FROM t1 WHERE (t1.i) IN 
> +(SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i);
> +drop table t1,t2,t3;
> 
> === modified file 'sql/sql_select.cc'
> --- a/sql/sql_select.cc	2010-05-24 07:22:22 +0000
> +++ b/sql/sql_select.cc	2010-05-30 19:41:21 +0000
> @@ -5752,7 +5752,14 @@ update_ref_and_keys(THD *thd, DYNAMIC_AR
>        /* Mark that we can optimize LEFT JOIN */
>        if (field->val->type() == Item::NULL_ITEM &&
>  	  !field->field->real_maybe_null())
> +      {
> +        /*
> +          Example:
> +          SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.a IS NULL;
> +          this just wants rows of t1 where t1.a does not exist in t2.
> +        */
>  	field->field->table->reginfo.not_exists_optimize=1;
> +      }
>      }
>    }
>    for (i=0 ; i < tables ; i++)
> @@ -10065,13 +10072,6 @@ uint check_join_cache_usage(JOIN_TAB *ta
>      goto no_join_cache;
>    
>    /*
> -    Use join cache with FirstMatch semi-join strategy only when semi-join
> -    contains only one table.
> -  */
> -  if (tab->is_inner_table_of_semi_join_with_first_match() &&
> -      !tab->is_single_inner_of_semi_join_with_first_match())
> -    goto no_join_cache;
> -  /*
>      Non-linked join buffers can't guarantee one match
>    */
>    if (force_unlinked_cache &&
> @@ -10083,19 +10083,35 @@ uint check_join_cache_usage(JOIN_TAB *ta
>      Don't use join buffering if we're dictated not to by no_jbuf_after (this
>      ...)
>    */
> -  if (!(i <= no_jbuf_after) || tab->loosescan_match_tab || 
> -      sj_is_materialize_strategy(join->best_positions[i].sj_strategy))
> +  if (!(i <= no_jbuf_after) || tab->loosescan_match_tab)
>      goto no_join_cache;
>  
> +  if (!join->select_lex->sj_nests.is_empty())

It is perhaps better to check whether the table is a semijoin inner table, e.g.
      if (tab->emb_sj_nest)

> +  {
> +    /* No join cache if FirstMatch with more than one inner table */
> +    if (tab->is_inner_table_of_semi_join_with_first_match() &&
> +        !tab->is_single_inner_of_semi_join_with_first_match())
> +      goto no_join_cache;

You do not need to wrap the above test inside another if(). But this is mostly a 
matter of taste, so no change required.

> +    /* No join cache if semijoin Materialization */
> +    for (int j= (int)i ; j >= (int)join->const_tables ; j--)
> +    {
> +      const POSITION *p= &(join->best_positions[j]);
> +      if (sj_is_materialize_strategy(p->sj_strategy) &&
> +          i < (j + p->n_sj_tables))
> +        goto no_join_cache;
> +    }

The second part of the above if() test should be unnecessary, but the condition 
might be asserted.

> +    /* Join cache for all semijoin's inner tables, or for none */
> +    if (tab->first_sj_inner_tab && tab->first_sj_inner_tab != tab
> &&
> +        !tab->first_sj_inner_tab->use_join_cache)
> +      goto no_join_cache;

Same comment here about wrapping.
> +  }
> +
>    for (JOIN_TAB *first_inner= tab->first_inner; first_inner;
>         first_inner= first_inner->first_upper)
>    {
>      if (first_inner != tab && !first_inner->use_join_cache)
>        goto no_join_cache;
>    }
> -  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)
>    {
>      /* 
> 
> 
> 
> ------------------------------------------------------------------------
> 
> 
Thread
bzr commit into mysql-next-mr-bugfixing branch (guilhem:3183) Bug#50358Bug#51018Guilhem Bichot30 May
Re: bzr commit into mysql-next-mr-bugfixing branch (guilhem:3183)Bug#50358 Bug#51018Roy Lyseng2 Jun
Re: bzr commit into mysql-next-mr-bugfixing branch (guilhem:3183)Bug#50358 Bug#51018Roy Lyseng2 Jun
  • Re: bzr commit into mysql-next-mr-bugfixing branch (guilhem:3183)Bug#50358 Bug#51018Guilhem Bichot4 Jun
    • Re: bzr commit into mysql-next-mr-bugfixing branch (guilhem:3183)Bug#50358 Bug#51018Roy Lyseng7 Jun
Re: bzr commit into mysql-next-mr-bugfixing branch (guilhem:3183)Bug#50358 Bug#51018Roy Lyseng7 Jun
  • Re: bzr commit into mysql-next-mr-bugfixing branch (guilhem:3183)Bug#50358 Bug#51018Guilhem Bichot7 Jun
    • Re: bzr commit into mysql-next-mr-bugfixing branch (guilhem:3183)Bug#50358 Bug#51018Roy Lyseng7 Jun