summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/main/analyze_stmt_privileges2.result26
-rw-r--r--mysql-test/main/derived.result2
-rw-r--r--mysql-test/main/explain_json.result2
-rw-r--r--mysql-test/main/myisam_explain_non_select_all.result6
-rw-r--r--mysql-test/main/opt_trace.result30
-rw-r--r--mysql-test/main/selectivity_innodb.result2
-rw-r--r--mysql-test/main/subselect.result2
-rw-r--r--mysql-test/main/subselect3.inc6
-rw-r--r--mysql-test/main/subselect3.result3
-rw-r--r--mysql-test/main/subselect3_jcl6.result15
-rw-r--r--mysql-test/main/subselect4.result2
-rw-r--r--mysql-test/main/subselect_exists2in.result2
-rw-r--r--mysql-test/main/subselect_extra.result4
-rw-r--r--mysql-test/main/subselect_firstmatch.result30
-rw-r--r--mysql-test/main/subselect_firstmatch.test28
-rw-r--r--mysql-test/main/subselect_mat.result2
-rw-r--r--mysql-test/main/subselect_no_mat.result4
-rw-r--r--mysql-test/main/subselect_no_scache.result2
-rw-r--r--mysql-test/main/subselect_sj2_jcl6.result20
-rw-r--r--mysql-test/main/subselect_sj2_mat.result2
-rw-r--r--mysql-test/main/subselect_sj_jcl6.result12
-rw-r--r--mysql-test/main/subselect_sj_mat.result2
-rw-r--r--mysql-test/suite/optimizer_unfixed_bugs/r/bug45221.result2
23 files changed, 123 insertions, 83 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
diff --git a/mysql-test/suite/optimizer_unfixed_bugs/r/bug45221.result b/mysql-test/suite/optimizer_unfixed_bugs/r/bug45221.result
index 17edbc19e0e..79100ca2b48 100644
--- a/mysql-test/suite/optimizer_unfixed_bugs/r/bug45221.result
+++ b/mysql-test/suite/optimizer_unfixed_bugs/r/bug45221.result
@@ -29,10 +29,10 @@ SELECT `int_key`
FROM t2
WHERE `date_nokey` < `datetime_nokey` XOR OUTR .`date_nokey` ) ;
pk
+9
2
5
6
-9
SELECT `pk`
FROM t1
WHERE `pk` IN (