summaryrefslogtreecommitdiff
path: root/mysql-test/r
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r')
-rw-r--r--mysql-test/r/explain.result8
-rw-r--r--mysql-test/r/group_by.result3
-rw-r--r--mysql-test/r/subselect.result17
-rw-r--r--mysql-test/r/subselect3.result33
-rw-r--r--mysql-test/r/subselect3_jcl6.result38
-rw-r--r--mysql-test/r/subselect_no_mat.result12
-rw-r--r--mysql-test/r/subselect_sj.result6
-rw-r--r--mysql-test/r/subselect_sj2.result3
-rw-r--r--mysql-test/r/subselect_sj2_jcl6.result3
-rw-r--r--mysql-test/r/subselect_sj_jcl6.result6
-rw-r--r--mysql-test/r/type_datetime.result8
11 files changed, 79 insertions, 58 deletions
diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result
index e0993c9cfee..2d0904691ca 100644
--- a/mysql-test/r/explain.result
+++ b/mysql-test/r/explain.result
@@ -195,16 +195,16 @@ insert into t2 values ('2001-01-01 1:1:1'), ('2001-01-01 1:1:1');
flush tables;
EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL );
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where
-1 PRIMARY INNR ALL NULL NULL NULL NULL 2 Using where; FirstMatch(OUTR)
+1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where; Start temporary
+1 PRIMARY INNR ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (flat, BNL join)
flush tables;
SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL );
dt
flush tables;
EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' );
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where
-1 PRIMARY INNR ALL NULL NULL NULL NULL 2 Using where; FirstMatch(OUTR)
+1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where; Start temporary
+1 PRIMARY INNR ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (flat, BNL join)
flush tables;
SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' );
dt
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index 495abc66f72..7f04ce089fd 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -1543,7 +1543,8 @@ EXPLAIN SELECT 1 FROM t1 WHERE a IN
(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 index PRIMARY,i2 PRIMARY 4 NULL 144 Using index
-1 PRIMARY t1 ALL NULL NULL NULL NULL 144 Using where; FirstMatch(t1)
+1 PRIMARY subselect2 eq_ref unique_key unique_key 4 func 1
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 144
CREATE TABLE t2 (a INT, b INT, KEY(a));
INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4);
EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 017bae87d41..3091418675b 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -2831,9 +2831,10 @@ Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cac
explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
-1 PRIMARY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; FirstMatch(t1)
+1 PRIMARY subselect2 eq_ref unique_key unique_key 10 func 1 1.00
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`two` = `test`.`t1`.`two`) and (`test`.`t2`.`one` = `test`.`t1`.`one`) and (`test`.`t2`.`flag` = 'N'))
+Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`flag` = 'N'))
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
@@ -4203,8 +4204,8 @@ CREATE INDEX I1 ON t1 (a);
CREATE INDEX I2 ON t1 (b);
EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index I1 I1 2 NULL 2 Using where; Using index; LooseScan
-1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition
+1 PRIMARY t1 ALL I2 NULL NULL NULL 2 Using where
+1 PRIMARY t1 ref I1 I1 2 test.t1.b 2 Using where; Using index; FirstMatch(t1)
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
a b
CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));
@@ -4213,15 +4214,15 @@ CREATE INDEX I1 ON t2 (a);
CREATE INDEX I2 ON t2 (b);
EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 index I1 I1 4 NULL 2 Using where; Using index; LooseScan
-1 PRIMARY t2 ref I2 I2 13 test.t2.a 2 Using index condition
+1 PRIMARY t2 ALL I2 NULL NULL NULL 2 Using where
+1 PRIMARY t2 ref I1 I1 4 test.t2.b 2 Using where; Using index; FirstMatch(t2)
SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
a b
EXPLAIN
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index I1 I1 2 NULL 2 Using where; Using index; LooseScan
-1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition
+1 PRIMARY t1 ALL I2 NULL NULL NULL 2 Using where
+1 PRIMARY t1 ref I1 I1 2 test.t1.b 2 Using where; Using index; FirstMatch(t1)
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
a b
DROP TABLE t1,t2;
diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index b21c1a1d4b4..78a75776573 100644
--- a/mysql-test/r/subselect3.result
+++ b/mysql-test/r/subselect3.result
@@ -103,7 +103,7 @@ oref a
1 1
show status like '%Handler_read_rnd_next';
Variable_name Value
-Handler_read_rnd_next 5
+Handler_read_rnd_next 11
delete from t2;
insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0);
set optimizer_switch='subquery_cache=off';
@@ -1112,7 +1112,8 @@ set @@optimizer_switch=@save_optimizer_switch;
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>)
+1 PRIMARY subselect3 eq_ref unique_key unique_key 5 func 1
+3 SUBQUERY t1 ALL NULL NULL NULL NULL 20
2 DERIVED t0 ALL NULL NULL NULL NULL 11
drop table t0, t1;
create table t0 (a int);
@@ -1124,16 +1125,18 @@ create table t3 (a int);
insert into t3 select A.a + 10*B.a from t0 A, t0 B;
explain select * from t3 where a in (select kp1 from t1 where kp1<20);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; LooseScan
-1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100
+1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1
+2 SUBQUERY t1 range kp1 kp1 5 NULL 48 Using where; Using index
create table t4 (pk int primary key);
insert into t4 select a from t3;
explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20
and t4.pk=t1.c);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using index condition; Using where; Using MRR; LooseScan
-1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t1)
-1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100
+1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1
+2 SUBQUERY t1 range kp1 kp1 5 NULL 48 Using index condition; Using where; Using MRR
+2 SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index
drop table t1, t3, t4;
create table t1 (a int) as select * from t0 where a < 5;
set @save_max_heap_table_size=@@max_heap_table_size;
@@ -1261,12 +1264,14 @@ insert into t1 select A.a, B.a, 'filler' from t0 A, t0 B;
create table t2 as select * from t1;
explain select * from t2 where a in (select b from t1 where a=3);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 range a a 5 NULL 8 Using where; Using index; LooseScan
-1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where
+1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1
+2 SUBQUERY t1 ref a a 10 const,test.t2.a 8 Using index
explain select * from t2 where (b,a) in (select a,b from t1 where a=3);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 range a a 5 NULL 8 Using where; Using index; LooseScan
-1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where
+1 PRIMARY subselect2 eq_ref unique_key unique_key 10 func 1
+2 SUBQUERY t1 ref a a 10 const,test.t2.a 8 Using index
drop table t1,t2;
create table t1 (a int, b int);
insert into t1 select a,a from t0;
@@ -1295,7 +1300,8 @@ 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)
+1 PRIMARY subselect2 eq_ref unique_key unique_key 3 func 1
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 4
select * from t0 where a in (select a from t1);
a
10.24
@@ -1308,7 +1314,8 @@ 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)
+1 PRIMARY subselect2 eq_ref unique_key unique_key 4 func 1
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 4
select * from t0 where a in (select a from t1);
a
2008-01-01
diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result
index b037af128fb..f866f1c2aa9 100644
--- a/mysql-test/r/subselect3_jcl6.result
+++ b/mysql-test/r/subselect3_jcl6.result
@@ -110,7 +110,7 @@ oref a
1 1
show status like '%Handler_read_rnd_next';
Variable_name Value
-Handler_read_rnd_next 5
+Handler_read_rnd_next 11
delete from t2;
insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0);
set optimizer_switch='subquery_cache=off';
@@ -1031,7 +1031,7 @@ explain select t21.* from t21,t22 where t21.a = t22.a and
t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 8 Using temporary; Using filesort
-1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (incremental, BNL join)
+1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (flat, BNL join)
1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (incremental, BNL join)
2 SUBQUERY t11 ALL NULL NULL NULL NULL 8 Using where
2 SUBQUERY t12 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join)
@@ -1039,7 +1039,6 @@ select t21.* from t21,t22 where t21.a = t22.a and
t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
a b c
256 67 NULL
-256 67 NULL
drop table t1, t11, t12, t21, t22;
create table t1(a int);
insert into t1 values (0),(1);
@@ -1120,7 +1119,8 @@ set @@optimizer_switch=@save_optimizer_switch;
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 subselect3 eq_ref unique_key unique_key 5 func 1
+3 SUBQUERY t1 ALL NULL NULL NULL NULL 20
2 DERIVED t0 ALL NULL NULL NULL NULL 11
drop table t0, t1;
create table t0 (a int);
@@ -1132,16 +1132,18 @@ create table t3 (a int);
insert into t3 select A.a + 10*B.a from t0 A, t0 B;
explain select * from t3 where a in (select kp1 from t1 where kp1<20);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; LooseScan
-1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100
+1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1
+2 SUBQUERY t1 range kp1 kp1 5 NULL 48 Using where; Using index
create table t4 (pk int primary key);
insert into t4 select a from t3;
explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20
and t4.pk=t1.c);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using index condition; Using where; Using MRR; LooseScan
-1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t1)
-1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100
+1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1
+2 SUBQUERY t1 range kp1 kp1 5 NULL 48 Using index condition; Using where; Using MRR
+2 SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index
drop table t1, t3, t4;
create table t1 (a int) as select * from t0 where a < 5;
set @save_max_heap_table_size=@@max_heap_table_size;
@@ -1269,12 +1271,14 @@ insert into t1 select A.a, B.a, 'filler' from t0 A, t0 B;
create table t2 as select * from t1;
explain select * from t2 where a in (select b from t1 where a=3);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 range a a 5 NULL 8 Using where; Using index; LooseScan
-1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where
+1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1
+2 SUBQUERY t1 ref a a 10 const,test.t2.a 8 Using index
explain select * from t2 where (b,a) in (select a,b from t1 where a=3);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 range a a 5 NULL 8 Using where; Using index; LooseScan
-1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where
+1 PRIMARY subselect2 eq_ref unique_key unique_key 10 func 1
+2 SUBQUERY t1 ref a a 10 const,test.t2.a 8 Using index
drop table t1,t2;
create table t1 (a int, b int);
insert into t1 select a,a from t0;
@@ -1303,7 +1307,8 @@ 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 subselect2 eq_ref unique_key unique_key 3 func 1
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 4
select * from t0 where a in (select a from t1);
a
10.24
@@ -1316,7 +1321,8 @@ 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 subselect2 eq_ref unique_key unique_key 4 func 1
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 4
select * from t0 where a in (select a from t1);
a
2008-01-01
@@ -1404,7 +1410,7 @@ WHERE cona.postalStripped='T2H3B2'
);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 2 1.00
-1 PRIMARY a index PRIMARY PRIMARY 4 NULL 2 100.00 Using where; Using index; Using join buffer (incremental, BNL join)
+1 PRIMARY a index PRIMARY PRIMARY 4 NULL 2 100.00 Using where; Using index; Using join buffer (flat, BNL join)
2 SUBQUERY cona ALL NULL NULL NULL NULL 2 100.00 Using where
2 SUBQUERY c eq_ref PRIMARY PRIMARY 4 test.cona.idContact 1 100.00 Using join buffer (flat, BKA join)
Warnings:
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index cf31257f8af..ff9b5b1de61 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -4207,8 +4207,8 @@ CREATE INDEX I1 ON t1 (a);
CREATE INDEX I2 ON t1 (b);
EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index I1 I1 2 NULL 2 Using where; Using index; LooseScan
-1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition
+1 PRIMARY t1 ALL I2 NULL NULL NULL 2 Using where
+1 PRIMARY t1 ref I1 I1 2 test.t1.b 2 Using where; Using index; FirstMatch(t1)
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
a b
CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));
@@ -4217,15 +4217,15 @@ CREATE INDEX I1 ON t2 (a);
CREATE INDEX I2 ON t2 (b);
EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 index I1 I1 4 NULL 2 Using where; Using index; LooseScan
-1 PRIMARY t2 ref I2 I2 13 test.t2.a 2 Using index condition
+1 PRIMARY t2 ALL I2 NULL NULL NULL 2 Using where
+1 PRIMARY t2 ref I1 I1 4 test.t2.b 2 Using where; Using index; FirstMatch(t2)
SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
a b
EXPLAIN
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index I1 I1 2 NULL 2 Using where; Using index; LooseScan
-1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition
+1 PRIMARY t1 ALL I2 NULL NULL NULL 2 Using where
+1 PRIMARY t1 ref I1 I1 2 test.t1.b 2 Using where; Using index; FirstMatch(t1)
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
a b
DROP TABLE t1,t2;
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index 0cdad381691..330e519617b 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -1062,8 +1062,10 @@ AND t1.val IN (SELECT t3.val FROM t3
WHERE t3.val LIKE 'a%' OR t3.val LIKE 'e%');
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 5
-1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using where; FirstMatch(t1)
-1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; FirstMatch(t3)
+1 PRIMARY subselect3 eq_ref unique_key unique_key 14 func 1
+1 PRIMARY subselect2 eq_ref unique_key unique_key 14 func 1
+3 SUBQUERY t3 ALL NULL NULL NULL NULL 5 Using where
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where
SELECT *
FROM t1
WHERE t1.val IN (SELECT t2.val FROM t2
diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result
index e47d82de8a3..9a7b6748d29 100644
--- a/mysql-test/r/subselect_sj2.result
+++ b/mysql-test/r/subselect_sj2.result
@@ -52,7 +52,8 @@ insert into t3 select a,a, a,a,a from t0;
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 10
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; FirstMatch(t3)
+1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 3
select * from t3 where b in (select a from t1);
a b pk1 pk2 pk3
1 1 1 1 1
diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result
index 1f7a9d4b9a0..ab97d59302c 100644
--- a/mysql-test/r/subselect_sj2_jcl6.result
+++ b/mysql-test/r/subselect_sj2_jcl6.result
@@ -59,7 +59,8 @@ insert into t3 select a,a, a,a,a from t0;
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 10
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; FirstMatch(t3); Using join buffer (flat, BNL join)
+1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 3
select * from t3 where b in (select a from t1);
a b pk1 pk2 pk3
1 1 1 1 1
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index 607bf1e0134..180efef57b7 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -1069,8 +1069,10 @@ AND t1.val IN (SELECT t3.val FROM t3
WHERE t3.val LIKE 'a%' OR t3.val LIKE 'e%');
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 5
-1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
-1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; FirstMatch(t3); Using join buffer (incremental, BNL join)
+1 PRIMARY subselect3 eq_ref unique_key unique_key 14 func 1
+1 PRIMARY subselect2 eq_ref unique_key unique_key 14 func 1
+3 SUBQUERY t3 ALL NULL NULL NULL NULL 5 Using where
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where
SELECT *
FROM t1
WHERE t1.val IN (SELECT t2.val FROM t2
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index dfcddfebde0..76edaea45a1 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -537,8 +537,8 @@ explain extended
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)
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Start temporary
+1 PRIMARY x1 ALL NULL NULL NULL NULL 2 100.00 Using where; End temporary; 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))
@@ -549,8 +549,8 @@ explain extended
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)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Start temporary
+1 PRIMARY x1 ALL NULL NULL NULL NULL 2 100.00 Using where; End temporary; 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))
View Lorry Controller Status