diff options
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 |