diff options
| author | Monty <monty@mariadb.org> | 2023-03-07 11:25:16 +0200 |
|---|---|---|
| committer | Monty <monty@mariadb.org> | 2023-03-07 14:27:26 +0200 |
| commit | 7a277a335291b2f818260c34333774956ad667da (patch) | |
| tree | 2cc3df13bab5606f3e2753029df684e4be3722eb /mysql-test/main | |
| parent | c5fdb988b7419ec953f2365a1ecff2fe6ccb3200 (diff) | |
| download | mariadb-git-7a277a335291b2f818260c34333774956ad667da.tar.gz | |
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
Diffstat (limited to 'mysql-test/main')
22 files changed, 122 insertions, 82 deletions
diff --git a/mysql-test/main/analyze_stmt_privileges2.result b/mysql-test/main/analyze_stmt_privileges2.result index 0dec4722802..3f09b1722b6 100644 --- a/mysql-test/main/analyze_stmt_privileges2.result +++ b/mysql-test/main/analyze_stmt_privileges2.result @@ -381,7 +381,7 @@ id select_type table type possible_keys key key_len ref rows Extra ANALYZE SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 -1 PRIMARY t2 ALL NULL NULL NULL NULL 3 3.00 100.00 0.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 3.00 33.33 0.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) #------------------------------------------------------------------------ # I/R/U/D/S on the inner view # Expectation: Can run everything @@ -494,7 +494,7 @@ id select_type table type possible_keys key key_len ref rows Extra ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 NULL 33.33 NULL Using where; FirstMatch(t1); Using join buffer (flat, BNL join) #------------------------------------------------------------------------ # I/R/U/D/S on the outer view # Expectation: Can run everything @@ -599,7 +599,7 @@ id select_type table type possible_keys key key_len ref rows Extra ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 12 12.00 100.00 0.00 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 NULL 33.33 NULL Using where; FirstMatch(t1); Using join buffer (flat, BNL join) #======================================================================== # Test: Grant INSERT on the table @@ -1590,7 +1590,7 @@ id select_type table type possible_keys key key_len ref rows Extra ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 4.00 100.00 0.00 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 NULL 33.33 NULL Using where; FirstMatch(t1); Using join buffer (flat, BNL join) #------------------------------------------------------------------------ # I/R/U/D/S on the outer view # Expectation: Can run everything: SELECT access to the column `a` @@ -1705,7 +1705,7 @@ id select_type table type possible_keys key key_len ref rows Extra ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 NULL 33.33 NULL Using where; FirstMatch(t1); Using join buffer (flat, BNL join) #======================================================================== # Test: Grant SELECT, INSERT, UPDATE, DELETE on the table @@ -1935,7 +1935,7 @@ id select_type table type possible_keys key key_len ref rows Extra ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 4.00 100.00 0.00 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 NULL 33.33 NULL Using where; FirstMatch(t1); Using join buffer (flat, BNL join) #------------------------------------------------------------------------ # I/R/U/D/S on the outer view # Expectation: Can run everything @@ -2041,7 +2041,7 @@ id select_type table type possible_keys key key_len ref rows Extra ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 NULL 33.33 NULL Using where; FirstMatch(t1); Using join buffer (flat, BNL join) ######################################################################### # Inner view permission tests @@ -2688,7 +2688,7 @@ id select_type table type possible_keys key key_len ref rows Extra ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 14 14.00 100.00 0.00 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 NULL 33.33 NULL Using where; FirstMatch(t1); Using join buffer (flat, BNL join) #------------------------------------------------------------------------ # I/R/U/D/S on the outer view # Expectation: Can run everything @@ -2793,7 +2793,7 @@ id select_type table type possible_keys key key_len ref rows Extra ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 18 18.00 100.00 0.00 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 NULL 33.33 NULL Using where; FirstMatch(t1); Using join buffer (flat, BNL join) #======================================================================== # Test: Grant INSERT on the inner view @@ -3974,7 +3974,7 @@ id select_type table type possible_keys key key_len ref rows Extra ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 35 35.00 100.00 0.00 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 NULL 33.33 NULL Using where; FirstMatch(t1); Using join buffer (flat, BNL join) #------------------------------------------------------------------------ # I/R/U/D/S on the outer view # Expectation: Can run everything @@ -4079,7 +4079,7 @@ id select_type table type possible_keys key key_len ref rows Extra ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 39 39.00 100.00 0.00 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 NULL 33.33 NULL Using where; FirstMatch(t1); Using join buffer (flat, BNL join) ######################################################################### # Outer view permission tests @@ -4597,7 +4597,7 @@ id select_type table type possible_keys key key_len ref rows Extra ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 39 39.00 100.00 0.00 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 NULL 33.33 NULL Using where; FirstMatch(t1); Using join buffer (flat, BNL join) #======================================================================== # Test: Grant INSERT on the outer view @@ -5202,7 +5202,7 @@ id select_type table type possible_keys key key_len ref rows Extra ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 44 44.00 100.00 0.00 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 NULL 33.33 NULL Using where; FirstMatch(t1); Using join buffer (flat, BNL join) disconnect con1; connection default; DROP USER 'privtest'@localhost; diff --git a/mysql-test/main/derived.result b/mysql-test/main/derived.result index 62bcfaed57b..112a72a2bf6 100644 --- a/mysql-test/main/derived.result +++ b/mysql-test/main/derived.result @@ -648,7 +648,7 @@ SELECT f3 FROM t2 HAVING f3 >= 8 id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00 1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 4 const 1 100.00 -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(<subquery4>); Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 50.00 Using where; FirstMatch(<subquery4>); Using join buffer (flat, BNL join) 4 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 2 DERIVED t1 system NULL NULL NULL NULL 1 100.00 Warnings: diff --git a/mysql-test/main/explain_json.result b/mysql-test/main/explain_json.result index 806773d154c..df443003a86 100644 --- a/mysql-test/main/explain_json.result +++ b/mysql-test/main/explain_json.result @@ -900,7 +900,7 @@ EXPLAIN "loops": 10, "rows": 10, "cost": "COST_REPLACED", - "filtered": 100, + "filtered": 10, "first_match": "t2" }, "buffer_type": "flat", diff --git a/mysql-test/main/myisam_explain_non_select_all.result b/mysql-test/main/myisam_explain_non_select_all.result index 82d494be01c..c8c26d76a70 100644 --- a/mysql-test/main/myisam_explain_non_select_all.result +++ b/mysql-test/main/myisam_explain_non_select_all.result @@ -281,7 +281,7 @@ FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT b FROM t2 WHERE t1.a < 3); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 33.33 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`b` = `test`.`t1`.`a` and `test`.`t1`.`a` < 3 @@ -917,7 +917,7 @@ FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT a FROM t2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY t2 ALL NULL NULL NULL NULL 4 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 25.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`a` = `test`.`t1`.`a` # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution @@ -1003,7 +1003,7 @@ FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 -1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 5 20.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`a1` AS `a1` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`a2` > 2 and `test`.`t1`.`a1` = `test`.`t2`.`a2` # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution 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" } ] } diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result index 055ae1f20c4..a147bdffc0f 100644 --- a/mysql-test/main/selectivity_innodb.result +++ b/mysql-test/main/selectivity_innodb.result @@ -804,7 +804,7 @@ explain extended select * from t1 where a in ( select b from t2 ) AND ( a > 3 ); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 50.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`b` = `test`.`t1`.`a` and `test`.`t1`.`a` > 3 select * from t1 where a in ( select b from t2 ) AND ( a > 3 ); diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result index 77d80405b3b..2b3050c3ca2 100644 --- a/mysql-test/main/subselect.result +++ b/mysql-test/main/subselect.result @@ -4470,7 +4470,7 @@ EXPLAIN EXTENDED SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a)); 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 3 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 33.33 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1003 select 2 AS `2` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`a` = `test`.`t1`.`a` diff --git a/mysql-test/main/subselect3.inc b/mysql-test/main/subselect3.inc index b78d5ed444f..fb330dfa3bc 100644 --- a/mysql-test/main/subselect3.inc +++ b/mysql-test/main/subselect3.inc @@ -1082,9 +1082,9 @@ drop table t0, t1, t2; set @@optimizer_switch='materialization=off'; -# -# Primitive SJ-Materialization tests for DECIMAL and DATE -# +--echo # +--echo # Primitive SJ-Materialization tests for DECIMAL and DATE +--echo # create table t0 (a decimal(4,2)); insert into t0 values (10.24), (22.11); create table t1 as select * from t0; diff --git a/mysql-test/main/subselect3.result b/mysql-test/main/subselect3.result index e8e91ec2cc2..710aa00d5aa 100644 --- a/mysql-test/main/subselect3.result +++ b/mysql-test/main/subselect3.result @@ -1304,6 +1304,9 @@ set @@optimizer_search_depth=@save_optimizer_search_depth; set @@optimizer_switch=@save_optimizer_switch; drop table t0, t1, t2; set @@optimizer_switch='materialization=off'; +# +# Primitive SJ-Materialization tests for DECIMAL and DATE +# create table t0 (a decimal(4,2)); insert into t0 values (10.24), (22.11); create table t1 as select * from t0; diff --git a/mysql-test/main/subselect3_jcl6.result b/mysql-test/main/subselect3_jcl6.result index 4fc6f713088..2bbc11863df 100644 --- a/mysql-test/main/subselect3_jcl6.result +++ b/mysql-test/main/subselect3_jcl6.result @@ -1128,8 +1128,8 @@ set @tmp_optimizer_switch=@@optimizer_switch; set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain select * from (select a from t0) X where a in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 11 -1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using where; FirstMatch(<derived2>); Using join buffer (flat, BNL join) +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 11 Using where +1 PRIMARY t1 hash_ALL NULL #hash#$hj 5 X.a 20 Using where; FirstMatch(<derived2>); Using join buffer (flat, BNLH join) 2 DERIVED t0 ALL NULL NULL NULL NULL 11 drop table t0, t1; set optimizer_switch=@tmp_optimizer_switch; @@ -1307,14 +1307,17 @@ set @@optimizer_search_depth=@save_optimizer_search_depth; set @@optimizer_switch=@save_optimizer_switch; drop table t0, t1, t2; set @@optimizer_switch='materialization=off'; +# +# Primitive SJ-Materialization tests for DECIMAL and DATE +# create table t0 (a decimal(4,2)); insert into t0 values (10.24), (22.11); create table t1 as select * from t0; insert into t1 select * from t0; explain select * from t0 where a in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t0 ALL NULL NULL NULL NULL 2 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(t0); Using join buffer (flat, BNL join) +1 PRIMARY t0 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t1 hash_ALL NULL #hash#$hj 3 test.t0.a 4 Using where; FirstMatch(t0); Using join buffer (flat, BNLH join) select * from t0 where a in (select a from t1); a 10.24 @@ -1326,8 +1329,8 @@ create table t1 as select * from t0; insert into t1 select * from t0; explain select * from t0 where a in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t0 ALL NULL NULL NULL NULL 2 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(t0); Using join buffer (flat, BNL join) +1 PRIMARY t0 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t1 hash_ALL NULL #hash#$hj 4 test.t0.a 4 Using where; FirstMatch(t0); Using join buffer (flat, BNLH join) select * from t0 where a in (select a from t1); a 2008-01-01 diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result index e0d06298f88..29e7e11f0ba 100644 --- a/mysql-test/main/subselect4.result +++ b/mysql-test/main/subselect4.result @@ -3146,7 +3146,7 @@ where b in (select c from t3 group by (select a from t1 where a = 1) in (select d from t4)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(t2); Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 50.00 Using where; FirstMatch(t2); Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t2`.`b` AS `b` from `test`.`t2` semi join (`test`.`t3`) where `test`.`t3`.`c` = `test`.`t2`.`b` select b from t2 diff --git a/mysql-test/main/subselect_exists2in.result b/mysql-test/main/subselect_exists2in.result index f6a4365f522..051003a8df8 100644 --- a/mysql-test/main/subselect_exists2in.result +++ b/mysql-test/main/subselect_exists2in.result @@ -596,7 +596,7 @@ SELECT * FROM t1 AS alias WHERE EXISTS ( SELECT * FROM t1 WHERE a > alias.a AND a = alias.b ); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY alias ALL NULL NULL NULL NULL 2 100.00 Using where -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(alias); Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 50.00 Using where; FirstMatch(alias); Using join buffer (flat, BNL join) Warnings: Note 1276 Field or reference 'test.alias.a' of SELECT #2 was resolved in SELECT #1 Note 1276 Field or reference 'test.alias.b' of SELECT #2 was resolved in SELECT #1 diff --git a/mysql-test/main/subselect_extra.result b/mysql-test/main/subselect_extra.result index bcb9cd18c64..247b36dbf49 100644 --- a/mysql-test/main/subselect_extra.result +++ b/mysql-test/main/subselect_extra.result @@ -68,7 +68,7 @@ select * from t1 where id in (select id from t1 as x1 where (t1.cur_date is null)); 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 Using where -1 PRIMARY x1 ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY x1 ALL NULL NULL NULL NULL 2 50.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) Warnings: Note 1276 Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`cur_date` AS `cur_date` from `test`.`t1` semi join (`test`.`t1` `x1`) where `test`.`x1`.`id` = `test`.`t1`.`id` and `test`.`t1`.`cur_date` = 0 @@ -80,7 +80,7 @@ select * from t2 where id in (select id from t2 as x1 where (t2.cur_date is null)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where -1 PRIMARY x1 ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(t2); Using join buffer (flat, BNL join) +1 PRIMARY x1 ALL NULL NULL NULL NULL 2 50.00 Using where; FirstMatch(t2); Using join buffer (flat, BNL join) Warnings: Note 1276 Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t2`.`id` AS `id`,`test`.`t2`.`cur_date` AS `cur_date` from `test`.`t2` semi join (`test`.`t2` `x1`) where `test`.`x1`.`id` = `test`.`t2`.`id` and `test`.`t2`.`cur_date` = 0 diff --git a/mysql-test/main/subselect_firstmatch.result b/mysql-test/main/subselect_firstmatch.result new file mode 100644 index 00000000000..86a3dd1bada --- /dev/null +++ b/mysql-test/main/subselect_firstmatch.result @@ -0,0 +1,30 @@ +# +# Check that firstmatch works with HASH +# +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); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where +1 PRIMARY t2 hash_ALL NULL #hash#$hj 10 test.t1.a,test.t1.b 100 Using where; FirstMatch(t1); Using join buffer (flat, BNLH join) +select * from t1 where t1.a in (select t2.a from t2 where t1.b=t2.b); +a b +1 1 +2 2 +3 3 +4 4 +5 5 +6 6 +7 7 +8 8 +9 9 +10 10 +set @@join_cache_level=@save_join_cache_level; +drop table t1,t2; +# +# End of 11.0 tests +# diff --git a/mysql-test/main/subselect_firstmatch.test b/mysql-test/main/subselect_firstmatch.test new file mode 100644 index 00000000000..54da8bf0548 --- /dev/null +++ b/mysql-test/main/subselect_firstmatch.test @@ -0,0 +1,28 @@ +# +# 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 # diff --git a/mysql-test/main/subselect_mat.result b/mysql-test/main/subselect_mat.result index 038acfbb1cb..a8cad01c674 100644 --- a/mysql-test/main/subselect_mat.result +++ b/mysql-test/main/subselect_mat.result @@ -2452,7 +2452,7 @@ explain SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); 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 3 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY t2 hash_ALL NULL #hash#$hj 8 test.t1.id,test.t1.id 3 Using where; FirstMatch(t1); Using join buffer (flat, BNLH join) SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); 1 1 diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result index 12c72545f73..ca68e4d7e03 100644 --- a/mysql-test/main/subselect_no_mat.result +++ b/mysql-test/main/subselect_no_mat.result @@ -2445,7 +2445,7 @@ sum(a+b) explain extended select sum(a+b) from t1 up where exists (select * from t1 where t1.a=up.a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY up ALL NULL NULL NULL NULL 25 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 25 100.00 Using where; FirstMatch(up); Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 25 4.00 Using where; FirstMatch(up); Using join buffer (flat, BNL join) Warnings: Note 1276 Field or reference 'test.up.a' of SELECT #2 was resolved in SELECT #1 Note 1003 select sum(`test`.`up`.`a` + `test`.`up`.`b`) AS `sum(a+b)` from `test`.`t1` `up` semi join (`test`.`t1`) where `test`.`t1`.`a` = `test`.`up`.`a` @@ -4473,7 +4473,7 @@ EXPLAIN EXTENDED SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a)); 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 3 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 33.33 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1003 select 2 AS `2` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`a` = `test`.`t1`.`a` diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result index 8083057eddf..813ca78703a 100644 --- a/mysql-test/main/subselect_no_scache.result +++ b/mysql-test/main/subselect_no_scache.result @@ -4476,7 +4476,7 @@ EXPLAIN EXTENDED SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a)); 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 3 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 33.33 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1003 select 2 AS `2` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`a` = `test`.`t1`.`a` diff --git a/mysql-test/main/subselect_sj2_jcl6.result b/mysql-test/main/subselect_sj2_jcl6.result index f09e70e90ee..672b1654aa8 100644 --- a/mysql-test/main/subselect_sj2_jcl6.result +++ b/mysql-test/main/subselect_sj2_jcl6.result @@ -61,8 +61,8 @@ a b 19 14 explain select * from t2 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 ALL b NULL NULL NULL 20 -1 PRIMARY t1 ALL NULL NULL NULL NULL 7 Using where; FirstMatch(t2); Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL b NULL NULL NULL 20 Using where +1 PRIMARY t1 hash_ALL NULL #hash#$hj 5 test.t2.b 7 Using where; FirstMatch(t2); Using join buffer (flat, BNLH join) select * from t2 where b in (select a from t1); a b 1 1 @@ -88,8 +88,8 @@ test.t3 analyze status Engine-independent statistics collected test.t3 analyze status OK explain select * from t3 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 ALL b NULL NULL NULL 20 -1 PRIMARY t1 ALL NULL NULL NULL NULL 7 Using where; FirstMatch(t3); Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL b NULL NULL NULL 20 Using where +1 PRIMARY t1 hash_ALL NULL #hash#$hj 5 test.t3.b 7 Using where; FirstMatch(t3); Using join buffer (flat, BNLH join) select * from t3 where b in (select a from t1); a b pk1 pk2 pk3 1 1 1 1 1 @@ -165,8 +165,8 @@ explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ot ALL NULL NULL NULL NULL 32 -1 PRIMARY it ALL NULL NULL NULL NULL 22 Using where; FirstMatch(ot); Using join buffer (flat, BNL join) +1 PRIMARY ot ALL NULL NULL NULL NULL 32 Using where +1 PRIMARY it hash_ALL NULL #hash#$hj 5 test.ot.a 22 Using where; FirstMatch(ot); Using join buffer (flat, BNLH join) select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); @@ -197,8 +197,8 @@ explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) from t2 ot where a in (select a from t1 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ot ALL NULL NULL NULL NULL 22 -1 PRIMARY it ALL NULL NULL NULL NULL 32 Using where; FirstMatch(ot); Using join buffer (flat, BNL join) +1 PRIMARY ot ALL NULL NULL NULL NULL 22 Using where +1 PRIMARY it hash_ALL NULL #hash#$hj 5 test.ot.a 32 Using where; FirstMatch(ot); Using join buffer (flat, BNLH join) select a, mid(filler1, 1,10), length(filler1)=length(filler2) from t2 ot where a in (select a from t1 it); @@ -1492,8 +1492,8 @@ SET join_cache_level = 3; EXPLAIN SELECT * FROM t1 WHERE b IN (SELECT a FROM t2 GROUP BY a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 1 -1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 1 Using where +1 PRIMARY t2 hash_ALL NULL #hash#$hj 4 test.t1.b 1 Using where; FirstMatch(t1); Using join buffer (flat, BNLH join) SELECT * FROM t1 WHERE b IN (SELECT a FROM t2 GROUP BY a); a b v v diff --git a/mysql-test/main/subselect_sj2_mat.result b/mysql-test/main/subselect_sj2_mat.result index cf2b1c4840f..5bc7751eeaa 100644 --- a/mysql-test/main/subselect_sj2_mat.result +++ b/mysql-test/main/subselect_sj2_mat.result @@ -1717,7 +1717,7 @@ explain extended select * from t1 where (rand() < 0) and i in (select i from t2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 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`.`i` AS `i` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`i` = `test`.`t1`.`i` and rand() < 0 drop table t1,t2; diff --git a/mysql-test/main/subselect_sj_jcl6.result b/mysql-test/main/subselect_sj_jcl6.result index 8b1042142f8..5971fa30e89 100644 --- a/mysql-test/main/subselect_sj_jcl6.result +++ b/mysql-test/main/subselect_sj_jcl6.result @@ -775,7 +775,7 @@ select a from t1 where a in (select c from t2 where d >= some(select e from t3 where b=e)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 -1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 6 16.67 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) 3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where Warnings: Note 1276 Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1 @@ -985,7 +985,7 @@ FROM t1 WHERE `varchar_nokey` < 'n' XOR `pk` ) ; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 18 100.00 -1 PRIMARY t1 ref varchar_key varchar_key 3 test.t2.varchar_nokey 2 100.00 Using where; FirstMatch(t2); Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 PRIMARY t1 ALL varchar_key NULL NULL NULL 15 6.67 Using where; FirstMatch(t2); Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where `test`.`t1`.`varchar_key` = `test`.`t2`.`varchar_nokey` and `test`.`t1`.`varchar_nokey` = `test`.`t2`.`varchar_nokey` and (`test`.`t2`.`varchar_nokey` < 'n' xor `test`.`t1`.`pk`) SELECT varchar_nokey @@ -3409,13 +3409,13 @@ WHERE t2.a IN (SELECT b FROM t3 WHERE t3.d <= t1.a); 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 PRIMARY NULL NULL NULL 4 Using join buffer (flat, BNL join) -1 PRIMARY t3 ALL d NULL NULL NULL 5 Range checked for each record (index map: 0x2); FirstMatch(t2) +1 PRIMARY t3 hash_ALL d #hash#$hj 5 test.t2.a 5 Using where; FirstMatch(t2); Using join buffer (flat, BNLH join) SELECT * FROM t1, t2 WHERE t2.a IN (SELECT b FROM t3 WHERE t3.d <= t1.a); a a b +w 5 19:11:10 w 2 18:56:33 q 2 18:56:33 -w 5 19:11:10 SET SESSION optimizer_switch='mrr=on'; SET SESSION join_cache_level=6; EXPLAIN @@ -3424,13 +3424,13 @@ WHERE t2.a IN (SELECT b FROM t3 WHERE t3.d <= t1.a); 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 PRIMARY NULL NULL NULL 4 Using join buffer (flat, BNL join) -1 PRIMARY t3 ALL d NULL NULL NULL 5 Range checked for each record (index map: 0x2); FirstMatch(t2) +1 PRIMARY t3 hash_ALL d #hash#$hj 5 test.t2.a 5 Using where; FirstMatch(t2); Using join buffer (incremental, BNLH join) SELECT * FROM t1, t2 WHERE t2.a IN (SELECT b FROM t3 WHERE t3.d <= t1.a); a a b +w 5 19:11:10 w 2 18:56:33 q 2 18:56:33 -w 5 19:11:10 set optimizer_switch=@save_optimizer_switch; set join_cache_level=default; DROP TABLE t1,t2,t3; diff --git a/mysql-test/main/subselect_sj_mat.result b/mysql-test/main/subselect_sj_mat.result index ec052d3cc4c..d6582652729 100644 --- a/mysql-test/main/subselect_sj_mat.result +++ b/mysql-test/main/subselect_sj_mat.result @@ -2478,7 +2478,7 @@ explain SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); 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 3 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY t2 hash_ALL NULL #hash#$hj 8 test.t1.id,test.t1.id 3 Using where; FirstMatch(t1); Using join buffer (flat, BNLH join) SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); 1 1 |