[MDEV-3233] LP:889750 - Semi-join neglects the semijoin_with_cache flag when firstmatch=off Created: 2011-11-13  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Igor Babaev Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug889750.xml    

 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)

 Comments   
Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 889750

Generated at Thu Feb 08 06:47:13 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.