Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
-
None
Description
When the 'firstmatch' flag is off a semi-join may use join cache even in the cases with 'semijoin_with_cache is set to 'off'.
This this reproducible in MariaDB-5.3 with the following test case:
create table t1 (a int);
insert into t1 values (7), (1), (5), (3);
create table t2 (a int);
insert into t2 values (4), (1), (8), (3), (9), (2);
set @tmp_otimizer_swictch= @@optimizer_switch;
set optimizer_switch='semijoin=on';
set optimizer_switch='firstmatch=off';
set optimizer_switch='semijoin_with_cache=on';
explain
select * from t1 where t1.a in (select t2.a from t2);
select * from t1 where t1.a in (select t2.a from t2);
set optimizer_switch='semijoin_with_cache=off';
explain
select * from t1 where t1.a in (select t2.a from t2);
select * from t1 where t1.a in (select t2.a from t2);
set optimizer_switch= @tmp_otimizer_swictch;
drop table t1,t2;
One can easily see this problem:
MariaDB [test]> set optimizer_switch='firstmatch=off';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]>
MariaDB [test]> set optimizer_switch='semijoin_with_cache=on';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> explain
-> select * from t1 where t1.a in (select t2.a from t2);
----------------------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
----------------------------------------------------------------------------------------------------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 4 | Start temporary |
| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 6 | Using where; End temporary; Using join buffer (flat, BNL join) |
----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [test]> set optimizer_switch='semijoin_with_cache=off';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> explain
-> select * from t1 where t1.a in (select t2.a from t2);
----------------------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
----------------------------------------------------------------------------------------------------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 4 | Start temporary |
| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 6 | Using where; End temporary; Using join buffer (flat, BNL join) |
----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)