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

it looks to me that the idea of this patch is better than the other one. 
However, I think that it could be useful to be able to set first_sj_inner_tab 
and last_sj_inner_tab for all semijoin inner tables. Keeping track of which 
strategies set the field and which do not is bound to be difficult and error-prone.

The comments assume this idea, but does not add code to decorate JOIN_TAB for 
the other sj strategies (ie dup weedout and loosescan). Neither is the proposed 
code tested, so if you agree to go down this path, there's bound to be changes...

Thanks,
Roy

Guilhem Bichot wrote:
> #At file:///home/mysql_src/bzrrepos_new/opt-back-53970-otherfix/ 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 second possible fix, which stores more in order to compute less.
> Test
>       results are identical to the ones in the first fix.
>      @ sql/sql_select.cc
>         Semijoin materializations now set JOIN_TAB::first_sj_inner_tab and
>         JOIN_TAB::last_sj_inner_tab.
>         In check_join_cache_usage(), we want to disable join buffering if the
>         table is in semijoin materialization (see the TODO in the function's
>         comment). The semijoin strategy is however stored only in the first
>         semijoin inner table, other inner ones have SJ_OPT_NONE (see end of 
>         fix_semijoin_strategies_for_picked_join_order()). So when 
>         check_join_cache_usage() was looking at a non-first inner table,
>         sj_is_materialize_strategy() said "no", in the end join buffering was
>         not disabled for that non-first inner table, leading to wrong results.
>         To find the strategy for the table, we need to access the first inner
>         table: we can now do so with tab->first_sj_inner_tab (now filled),
>         through get_sj_strategy().
>         All existing code which implicitely applied only to firstmatch inner
>         tables, still does so.
>      @ sql/sql_select.h
>         JOIN_TAB::first_sj_inner_tab and JOIN_TAB::last_sj_inner_tab were set
>         only for firstmatch. As the same information is useful for
>         check_join_cache_usage() to detect semijoin materialization, we now set
>         it for this strategy too. Thus, existing code which tested
>         first_sj_inner_tab (implicitely testing for firstmatch) should now test
>         first_sj_inner_tab_with_firstmatch() to not change behaviour.
> 
>     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
>       sql/sql_select.h
> === 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:44:29 +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:44:29 +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:44:29 +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:44:29 +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:44:29 +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:44:29 +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:44:29 +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:44:29 +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:44:29 +0000
> @@ -1287,9 +1287,20 @@ int setup_semijoin_dups_elimination(JOIN
>      switch (pos->sj_strategy) {
>        case SJ_OPT_MATERIALIZE_LOOKUP:
>        case SJ_OPT_MATERIALIZE_SCAN:
> -        /* Do nothing */
> +      {
> +        for (JOIN_TAB *j= tab; j != tab + pos->n_sj_tables; j++)
> +        {
> +          DBUG_ASSERT(j->emb_sj_nest != NULL);
> +          /*
> +            Remember the interval of semijoin inner tables for
> +            check_join_cache_usage().
> +          */
> +          j->first_sj_inner_tab= tab;
> +          j->last_sj_inner_tab= tab + pos->n_sj_tables - 1;
> +        }
>          i+= pos->n_sj_tables;
>          break;

I think it is a good idea to set first_sj_inner_tab and last_sj_inner_tab for 
all semijoin strategies. We must just remember that they do not always define a 
true interval of inner semijoin tables.
> +      }
>        case SJ_OPT_LOOSE_SCAN:
>        {
>          DBUG_ASSERT(tab->emb_sj_nest != NULL); // First table must be inner
> @@ -1410,7 +1421,11 @@ int setup_semijoin_dups_elimination(JOIN
>          DBUG_ASSERT(tab->emb_sj_nest != NULL); // First table must be inner
>          for (j= tab; j != tab + pos->n_sj_tables; j++)
>          {
> -          if (!tab->emb_sj_nest) /// @todo fix this (BUG#51457)
> +          /**
> +             @todo once this code has been fixed (BUG#51457) the loop may be
> +             unified with the one of SJ_OPT_MATERIALIZATION above.
> +          */
> +          if (!tab->emb_sj_nest)
>              jump_to= tab;
>            else
>            {
> @@ -5752,7 +5767,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++)
> @@ -9935,12 +9957,12 @@ void revise_cache_usage(JOIN_TAB *join_t
>        end_tab= first_inner;
>      }
>    }
> -  else if (join_tab->first_sj_inner_tab)
> +  else if (join_tab->first_sj_inner_tab_with_firstmatch())

   else if (join_tab->first_sj_inner_tab &&
            get_sj_strategy() == SJ_OPT_FIRST_MATCH)
>    {
>      first_inner= join_tab->first_sj_inner_tab;
>      for (tab= join_tab-1; tab >= first_inner; tab--)
>      {
> -      if (tab->first_sj_inner_tab == first_inner)
> +      if (tab->first_sj_inner_tab_with_firstmatch() == first_inner)

Revert to old
>          set_join_cache_denial(tab);
>      }
>    }
> @@ -10079,12 +10101,13 @@ uint check_join_cache_usage(JOIN_TAB *ta
>        !tab->is_single_inner_of_outer_join())
>      goto no_join_cache;
>  
> -  /*
> -    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))
> +  /* No join buffering if prevented by no_jbuf_after */
> +  if (!(i <= no_jbuf_after) || tab->loosescan_match_tab)
> +    goto no_join_cache;
> +
> +  /* Neither if semijoin Materialization */
> +  if ((tab->first_sj_inner_tab != NULL) &&
> +      sj_is_materialize_strategy(tab->get_sj_strategy()))
>      goto no_join_cache;
>  
>    for (JOIN_TAB *first_inner= tab->first_inner; first_inner;
> @@ -10093,7 +10116,8 @@ uint check_join_cache_usage(JOIN_TAB *ta
>      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
> &&
> +  if (tab->first_sj_inner_tab_with_firstmatch() &&
> +      tab->first_sj_inner_tab != tab &&

   if (!tab->is_first_inner_tab_of_semi_join())

>        !tab->first_sj_inner_tab->use_join_cache)
>      goto no_join_cache;
>    if (!tab[-1].use_join_cache)
> @@ -10112,7 +10136,7 @@ uint check_join_cache_usage(JOIN_TAB *ta
>            goto no_join_cache;
>        }
>      }
> -    else if (tab->first_sj_inner_tab &&
> +    else if (tab->first_sj_inner_tab_with_firstmatch() &&

I think this can be reverted because it is probably universal for all sj strategies.
>               tab->first_sj_inner_tab == tab[-1].first_sj_inner_tab)
>        goto no_join_cache; 
>    }       
> @@ -10122,7 +10146,8 @@ uint check_join_cache_usage(JOIN_TAB *ta
>  
>    switch (tab->type) {
>    case JT_ALL:
> -    if (cache_level <= 2 && (tab->first_inner ||
> tab->first_sj_inner_tab))
> +    if (cache_level <= 2 &&
> +        (tab->first_inner || tab->first_sj_inner_tab_with_firstmatch()))

I think that tab->first_sj_inner_tab_with_firstmatch() can be replaced with
  tab->get_sj_strategy() == SJ_FIRST_MATCH.

>        goto no_join_cache;
>      if ((options & SELECT_DESCRIBE) ||
>          ((tab->cache= new JOIN_CACHE_BNL(join, tab, prev_cache))) &&
> @@ -10806,6 +10831,22 @@ void JOIN_TAB::cleanup()
>  
>  
>  /**
> +  @returns semijoin strategy for this table.
> +  @note validity has same restrictions as JOIN_TAB::first_sj_inner_tab.
> +*/
> +uint JOIN_TAB::get_sj_strategy() const
> +{
> +  DBUG_ASSERT(first_sj_inner_tab != NULL);

Perhaps instead:
   if (!first_sj_inner_tab)
     return SJ_OPT_NONE;

> +  const int j= first_sj_inner_tab - join->join_tab;
> +  DBUG_ASSERT(j >= 0);
> +  uint s= join->best_positions[j].sj_strategy;
> +  DBUG_ASSERT(s == SJ_OPT_FIRST_MATCH ||
> +              sj_is_materialize_strategy(s));
This assert may go away.
> +  return s;
> +}
> +
> +
> +/**
>    Partially cleanup JOIN after it has executed: close index or rnd read
>    (table cursors), free quick selects.
>  
> 
> === modified file 'sql/sql_select.h'
> --- a/sql/sql_select.h	2010-05-19 14:44:18 +0000
> +++ b/sql/sql_select.h	2010-05-30 19:44:29 +0000
> @@ -149,6 +149,17 @@ typedef enum_nested_loop_state
>  Next_select_func setup_end_select_func(JOIN *join);
>  int rr_sequential(READ_RECORD *info);
>  
> +#define SJ_OPT_NONE 0
> +#define SJ_OPT_DUPS_WEEDOUT 1
> +#define SJ_OPT_LOOSE_SCAN   2
> +#define SJ_OPT_FIRST_MATCH  3
> +#define SJ_OPT_MATERIALIZE_LOOKUP  4
> +#define SJ_OPT_MATERIALIZE_SCAN  5
> +
> +inline bool sj_is_materialize_strategy(uint strategy)
> +{
> +  return strategy >= SJ_OPT_MATERIALIZE_LOOKUP;
> +}
>  
>  typedef struct st_join_table
>  {
> @@ -247,7 +258,11 @@ typedef struct st_join_table
>    */
>    TABLE_LIST    *emb_sj_nest;
>  
> -  /* FirstMatch variables (final QEP) */
> +  /**
> +    Boundaries of semijoin inner tables around this table. Valid only once
> +    final QEP has been chosen, and only if table does FirstMatch or
> +    Materializations.
Validity would change.
> +  */
>    struct st_join_table *first_sj_inner_tab;
>    struct st_join_table *last_sj_inner_tab;
>  
> @@ -312,9 +327,16 @@ typedef struct st_join_table
>      }
>      return test(used_rowid_fields);
>    }
> +  struct st_join_table *first_sj_inner_tab_with_firstmatch() const
> +  {
> +    if ((first_sj_inner_tab != NULL) &&
> +        (get_sj_strategy() == SJ_OPT_FIRST_MATCH))
> +      return first_sj_inner_tab;
> +    return NULL;
> +  }

Above function no longer needed.

>    bool is_inner_table_of_semi_join_with_first_match()
>    {
> -    return first_sj_inner_tab != NULL;
> +    return (first_sj_inner_tab_with_firstmatch() != NULL);
   return (first_sj_inner_tab && first_sj_inner_tab == this &&
           get_sj_strategy() == SJ_OPT_FIRST_MATCH)
>    }
>    bool is_inner_table_of_outer_join()
>    {
> @@ -322,7 +344,8 @@ typedef struct st_join_table
>    }
>    bool is_single_inner_of_semi_join_with_first_match()
>    {
> -    return first_sj_inner_tab == this && last_sj_inner_tab == this;         
>   
> +    return first_sj_inner_tab_with_firstmatch() == this &&
> +      last_sj_inner_tab == this;
>    }

   return (first_sj_inner_tab == this && last_sj_inner_tab == this &&
           get_sj_strategy() == SJ_OPT_FIRST_MATCH);
>    bool is_single_inner_of_outer_join()
>    {
> @@ -334,24 +357,27 @@ typedef struct st_join_table
>    }
>    bool use_match_flag()
>    {
> -    return is_first_inner_for_outer_join() || first_sj_inner_tab == this ; 
> +    return is_first_inner_for_outer_join() ||
> +      first_sj_inner_tab_with_firstmatch() == this ;

Replace first_sj_inner_tab_with_firstmatch() with
first_sj_inner_tab == this && get_sj_strategy() == OPT_SJ_FIRST_MATCH
>    }
>    bool check_only_first_match()
>    {
> -    return  last_sj_inner_tab == this ||
> -           (first_inner && first_inner->last_inner == this &&
> -            table->reginfo.not_exists_optimize);
> +    return ((last_sj_inner_tab == this) &&
> +            (get_sj_strategy() == SJ_OPT_FIRST_MATCH)) ||
> +      (first_inner && first_inner->last_inner == this &&
> +       table->reginfo.not_exists_optimize);

It is a bit problematic that JOIN_CACHE must have such intimate knowledge of the 
strategies that it is executing on behalf of (if outer join, use 
first_inner/first_outer, if semijoin, use sj_first_inner/sj_first_outer).

But that would be material for a separate refactoring...
>    }
>    bool is_last_inner_table()
>    {
>      return (first_inner && first_inner->last_inner == this) ||
> -           last_sj_inner_tab == this;
> +      ((last_sj_inner_tab == this) &&
> +       (get_sj_strategy() == SJ_OPT_FIRST_MATCH));

The strategy test should not be part of this function. But then an outside 
additional tes may be needed.

>    }
>    struct st_join_table *get_first_inner_table()
>    {
>      if (first_inner)
>        return first_inner;
> -    return first_sj_inner_tab; 
> +    return first_sj_inner_tab_with_firstmatch(); 

Revert...
>    }
>    void set_select_cond(COND *to, uint line)
>    {
> @@ -367,6 +393,7 @@ typedef struct st_join_table
>        select->cond= new_cond;
>      return tmp_select_cond;
>    }
> +  uint get_sj_strategy() const;
>  } JOIN_TAB;
>  
>  /* 
> @@ -1443,17 +1470,6 @@ public:
>    SJ_TMP_TABLE *next; 
>  };
>  
> -#define SJ_OPT_NONE 0
> -#define SJ_OPT_DUPS_WEEDOUT 1
> -#define SJ_OPT_LOOSE_SCAN   2
> -#define SJ_OPT_FIRST_MATCH  3
> -#define SJ_OPT_MATERIALIZE_LOOKUP  4
> -#define SJ_OPT_MATERIALIZE_SCAN  5
> -
> -inline bool sj_is_materialize_strategy(uint strategy)
> -{
> -  return strategy >= SJ_OPT_MATERIALIZE_LOOKUP;
> -}
>  
>  class JOIN :public Sql_alloc
>  {
> 
> 
> 
> ------------------------------------------------------------------------
> 
> 
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