Allow firstmatch to use HASH joins

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

1 files changed, 3 insertions, 27 deletions

diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index cb08c4150d8..41e2afbd43b 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -3199,14 +3199,14 @@ set optimizer_trace='enabled=on';
 explain extended select * from t1 where a in (select p from t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	10.00	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`p` = `test`.`t1`.`a`
 insert into t2 select seq,seq from seq_10_to_100;
 explain extended select * from t1 where a in (select p from t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	101	100.00	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	101	0.99	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`p` = `test`.`t1`.`a`
 select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
@@ -3523,31 +3523,7 @@ explain extended select * from t1 where a in (select p from t2)	{
                 "semi_join_strategy": "FirstMatch",
                 "join_order": [
                   {
-                    "table": "t2",
-                    "best_access_path": {
-                      "table": "t2",
-                      "plan_details": {
-                        "record_count": 4
-                      },
-                      "considered_access_paths": [
-                        {
-                          "access_type": "scan",
-                          "rows": 101,
-                          "rows_after_filter": 101,
-                          "rows_out": 101,
-                          "cost": 0.10333002,
-                          "index_only": false,
-                          "chosen": true
-                        }
-                      ],
-                      "chosen_access_method": {
-                        "type": "scan",
-                        "rows_read": 101,
-                        "rows_out": 101,
-                        "cost": 0.10333002,
-                        "uses_join_buffering": false
-                      }
-                    }
+                    "table": "t2"
                   }
                 ]
               }