mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01:00
7a277a3352
Firstmatch_picker::check_qep() has an optimization that allows firstmatch to be used together with join buffer under some conditions. In this case the cost was assumed to be same as what best_access_path() had calculated. However if HASH+join_buffer was used, then fix_semijoin_strategies_for_picked_join_order() would remove the join_buffer (which would cause a full join to be used) and the cost assumption by Firstmatch_picker::check_qep() would be wrong. Later check_join_cache_usage() sees that it's a full scan and decides it can use join buffering, (But not the hash join). Fixed by also allowing HASH joins with firstmatch. This removes the need to change disable and re-enable join buffer. Test case changes: - HASH join used with firstmatch (Using join buffer (flat, BNLH join)) - Filtered could change with firstmatch as the conversion with and without join_buffered lost the filtering information. - The not "re-enabling join buffer" is shown in main.optimizer_trace Original code by Sergei, optimized by Monty. Author: Sergei Petrunia <sergey@mariadb.com>, monty@mariadb.org
28 lines
696 B
Text
28 lines
696 B
Text
#
|
|
# Test for semijoins that don't need to be run for a lot of combinations
|
|
#
|
|
--source include/have_sequence.inc
|
|
|
|
--echo #
|
|
--echo # Check that firstmatch works with HASH
|
|
--echo #
|
|
|
|
create table t1 (a int, b int);
|
|
insert into t1 select seq, seq from seq_1_to_10;
|
|
|
|
create table t2 (a int, b int);
|
|
insert into t2 select A.seq,A.seq from seq_1_to_10 A, seq_1_to_10 B;
|
|
|
|
set @save_join_cache_level=@@join_cache_level;
|
|
set join_cache_level=6;
|
|
|
|
explain select * from t1 where t1.a in (select t2.a from t2 where t1.b=t2.b);
|
|
select * from t1 where t1.a in (select t2.a from t2 where t1.b=t2.b);
|
|
|
|
set @@join_cache_level=@save_join_cache_level;
|
|
|
|
drop table t1,t2;
|
|
|
|
--echo #
|
|
--echo # End of 11.0 tests
|
|
--echo #
|