summaryrefslogtreecommitdiff
path: root/mysql-test/main/subselect_no_mat.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main/subselect_no_mat.result')
-rw-r--r--mysql-test/main/subselect_no_mat.result62
1 files changed, 32 insertions, 30 deletions
diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result
index 8ca53e0f64d..367a468b517 100644
--- a/mysql-test/main/subselect_no_mat.result
+++ b/mysql-test/main/subselect_no_mat.result
@@ -929,10 +929,10 @@ a t1.a in (select t2.a from t2,t3 where t3.a=t2.a)
explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
-2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index
-2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
+2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00
+2 DEPENDENT SUBQUERY t2 index a a 5 NULL 3 75.00 Using where; Using index; Using join buffer (flat, BNL join)
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` join `test`.`t3` where `test`.`t3`.`a` = `test`.`t2`.`a` and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a` or `test`.`t2`.`a` is null) having `test`.`t2`.`a` is null))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` join `test`.`t3` where `test`.`t2`.`a` = `test`.`t3`.`a` and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a` or `test`.`t2`.`a` is null) having `test`.`t2`.`a` is null))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
drop table t1,t2,t3;
# check correct NULL Processing for normal IN/ALL/ANY
# and 2 ways of max/min optimization
@@ -1456,21 +1456,21 @@ a
4
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index
-1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 100.00 Using where
+1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00 Using where
+1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Using index
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t1`.`b` <> 30
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a` and `test`.`t1`.`b` <> 30
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
a
2
3
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index
-1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 100.00
-1 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 3 75.00 Using where; Using index; Using join buffer (flat, BNL join)
+1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00 Using where
+1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Using index
+1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Using index
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t3` join `test`.`t2` where `test`.`t3`.`a` = `test`.`t1`.`b` and `test`.`t1`.`a` = `test`.`t2`.`a`
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t3` join `test`.`t2` where `test`.`t3`.`a` = `test`.`t1`.`b` and `test`.`t2`.`a` = `test`.`t1`.`a`
drop table t1, t2, t3;
create table t1 (a int, b int, index a (a,b));
create table t2 (a int, index a (a));
@@ -1510,7 +1510,7 @@ a
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index
-1 PRIMARY t3 range a a 5 NULL 3 100.00 Using where; Using index
+1 PRIMARY t3 index a a 5 NULL 3 100.00 Using where; Using index
1 PRIMARY t1 ref a a 10 test.t2.a,test.t3.a 116 100.00 Using index; FirstMatch(t2)
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1` join `test`.`t3`) where `test`.`t1`.`b` = `test`.`t3`.`a` and `test`.`t1`.`a` = `test`.`t2`.`a`
@@ -1618,21 +1618,21 @@ a3 1
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
-2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
+2 DEPENDENT SUBQUERY t2 index s1 s1 6 NULL 2 100.00 Using where; Using index
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`s1` AS `s1`,!<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(`test`.`t2`.`s1` is null))))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1`
+Note 1003 /* select#1 */ select `test`.`t1`.`s1` AS `s1`,!<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(/* select#2 */ select `test`.`t2`.`s1` from `test`.`t2` where trigcond(<cache>(`test`.`t1`.`s1`) = `test`.`t2`.`s1` or `test`.`t2`.`s1` is null) having trigcond(`test`.`t2`.`s1` is null)))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1`
explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
-2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
+2 DEPENDENT SUBQUERY t2 index s1 s1 6 NULL 2 100.00 Using where; Using index
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`s1` AS `s1`,<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(`test`.`t2`.`s1` is null))))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1`
+Note 1003 /* select#1 */ select `test`.`t1`.`s1` AS `s1`,<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(/* select#2 */ select `test`.`t2`.`s1` from `test`.`t2` where trigcond(<cache>(`test`.`t1`.`s1`) = `test`.`t2`.`s1` or `test`.`t2`.`s1` is null) having trigcond(`test`.`t2`.`s1` is null)))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1`
explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
-2 SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Full scan on NULL key
+2 DEPENDENT SUBQUERY t2 index s1 s1 6 NULL 2 100.00 Using where; Using index
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`s1` AS `s1`,!<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(`test`.`t2`.`s1` is null))))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1`
+Note 1003 /* select#1 */ select `test`.`t1`.`s1` AS `s1`,!<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(/* select#2 */ select `test`.`t2`.`s1` from `test`.`t2` where trigcond(<cache>(`test`.`t1`.`s1`) = `test`.`t2`.`s1` or `test`.`t2`.`s1` is null) having trigcond(`test`.`t2`.`s1` is null)))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1`
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
@@ -2425,15 +2425,17 @@ a
3
DROP TABLE t1;
create table t1 (a int, b int);
-insert into t1 values (1,2),(3,4);
+insert into t1 values (1,2),(3,4),(5,6),(7,8);
select * from t1 up where exists (select * from t1 where t1.a=up.a);
a b
1 2
3 4
+5 6
+7 8
explain extended select * 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 2 100.00
-1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(up); Using join buffer (flat, BNL join)
+1 PRIMARY up ALL NULL NULL NULL NULL 4 100.00
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.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 `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up` semi join (`test`.`t1`) where `test`.`t1`.`a` = `test`.`up`.`a`
@@ -3105,7 +3107,7 @@ retailerID statusID changed
drop table t1;
create table t1(a int, primary key (a));
insert into t1 values (10);
-create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b));
+create table t2 (a int primary key, b varchar(32), c int, unique key cb(c, b));
insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989');
insert into t2(a, c, b) values (4,10,'360'), (5,10,'35998'), (6,10,'35999');
analyze table t1;
@@ -3118,7 +3120,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system PRIMARY NULL NULL NULL 1
1 PRIMARY r const PRIMARY PRIMARY 4 const 1
-2 SUBQUERY t2 range b b 40 NULL 3 Using where
+2 SUBQUERY t2 ref cb cb 5 const 1 Using where
SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
@@ -3130,7 +3132,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system PRIMARY NULL NULL NULL 1
1 PRIMARY r const PRIMARY PRIMARY 4 const 1
-2 SUBQUERY t2 range b b 40 NULL 3 Using index condition
+2 SUBQUERY t2 ref cb cb 5 const 1 Using index condition; Using where
SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
@@ -4228,8 +4230,8 @@ INSERT INTO t2 VALUES (7), (5), (1), (3);
SELECT id, st FROM t1
WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
id st
-3 FL
1 GA
+3 FL
7 FL
SELECT id, st FROM t1
WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
@@ -4441,7 +4443,7 @@ out_a MIN(b)
DROP TABLE t1;
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT);
-INSERT INTO t1 VALUES (1),(2);
+INSERT INTO t1 VALUES (1),(2),(3),(4);
INSERT INTO t2 VALUES (1),(2);
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
2
@@ -4450,7 +4452,7 @@ SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
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 2 100.00
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 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
@@ -4459,7 +4461,7 @@ EXPLAIN EXTENDED
SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION
(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 2 100.00 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 Using where
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 2 100.00 Using where
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
@@ -5687,9 +5689,9 @@ WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
FROM it2,it3);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY ot1 ALL NULL NULL NULL NULL 2
-1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where; Start temporary; Using join buffer (flat, BNL join)
-1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using join buffer (flat, BNL join)
-1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using join buffer (flat, BNL join)
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using where; FirstMatch(ot4)
DROP TABLE IF EXISTS ot1, ot4, it2, it3;
#
# Bug#729039: NULL keys used to evaluate subquery
View Lorry Controller Status