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)
> {
> /*
>
>
>
> ------------------------------------------------------------------------
>
>
