summaryrefslogtreecommitdiff
path: root/mysql-test/r/subselect4.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r/subselect4.result')
-rw-r--r--mysql-test/r/subselect4.result55
1 files changed, 29 insertions, 26 deletions
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index 617d2e9e592..1e4b6c9df0a 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -226,10 +226,10 @@ NULL
EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) AS RESULT FROM t1 ;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00
-2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
Warnings:
Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1
-Note 1003 select <expr_cache><>((select 1 from `test`.`t2` where 0)) AS `RESULT` from dual
+Note 1003 select (select 1 from `test`.`t2` where 0) AS `RESULT` from dual
first equivalent variant
SELECT (SELECT 1 FROM t2 WHERE d = IFNULL(c,NULL)) AS RESULT FROM t1 GROUP BY c ;
RESULT
@@ -237,10 +237,10 @@ NULL
EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = IFNULL(c,NULL)) AS RESULT FROM t1 GROUP BY c;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00
-2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
Warnings:
Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1
-Note 1003 select <expr_cache><>((select 1 from `test`.`t2` where 0)) AS `RESULT` from dual group by NULL
+Note 1003 select (select 1 from `test`.`t2` where 0) AS `RESULT` from dual group by NULL
second equivalent variant
SELECT (SELECT 1 FROM t2 WHERE d = c) AS RESULT FROM t1 GROUP BY c ;
RESULT
@@ -248,10 +248,10 @@ NULL
EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) AS RESULT FROM t1 GROUP BY c ;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00
-2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
Warnings:
Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1
-Note 1003 select <expr_cache><>((select 1 from `test`.`t2` where 0)) AS `RESULT` from dual group by NULL
+Note 1003 select (select 1 from `test`.`t2` where 0) AS `RESULT` from dual group by NULL
DROP TABLE t1,t2;
#
# BUG#45928 "Differing query results depending on MRR and
@@ -562,7 +562,7 @@ WHERE f3 = (
SELECT t1.f3 FROM t1
WHERE ( t1.f10 ) IN ( SELECT f11 FROM t2 GROUP BY f11 ));
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ref f3 f3 5 const 0 Using where
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2
2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1
3 MATERIALIZED t2 ALL NULL NULL NULL NULL 2
@@ -577,7 +577,7 @@ WHERE f3 = (
SELECT f3 FROM t1
WHERE ( f10, f10 ) IN ( SELECT f11, f11 FROM t2 GROUP BY f11 ));
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ref f3 f3 5 const 0 Using where
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2
2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 8 func,func 1
3 MATERIALIZED t2 ALL NULL NULL NULL NULL 2
@@ -1220,6 +1220,13 @@ id select_type table type possible_keys key key_len ref rows Extra
2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
3 SUBQUERY SQ1_t1 index NULL f4 5 NULL 2 Using index; Using temporary
3 SUBQUERY SQ1_t3 index f4 f4 5 NULL 2 Using where; Using index; Using join buffer (flat, BNL join)
+SELECT * FROM t1 WHERE
+(SELECT f2 FROM t2
+WHERE f4 <= ALL
+(SELECT max(SQ1_t1.f4)
+FROM t3 AS SQ1_t1 JOIN t3 AS SQ1_t3 ON SQ1_t3.f4
+GROUP BY SQ1_t1.f4));
+ERROR 21000: Subquery returns more than 1 row
drop table t1, t2, t3;
#
# BUG#52317: Assertion failing in Field_varstring::store()
@@ -1250,10 +1257,9 @@ FROM t2 JOIN t1 ON t1.f3
WHERE ('v') IN (SELECT f4 FROM t2)
GROUP BY f9;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 system NULL NULL NULL NULL 1
-1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 2
SELECT COUNT(t2.f3),
(SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9
FROM t2 JOIN t1 ON t1.f3
@@ -1267,17 +1273,16 @@ FROM t2 JOIN t1 ON t1.f3
WHERE ('v') IN (SELECT f4 FROM t2)
ORDER BY f9;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 system NULL NULL NULL NULL 1
-1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 2
SELECT COUNT(t2.f3),
(SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9
FROM t2 JOIN t1 ON t1.f3
WHERE ('v') IN (SELECT f4 FROM t2)
ORDER BY f9;
COUNT(t2.f3) f9
-0 2
+0 NULL
EXPLAIN
SELECT COUNT(t2.f3),
(SELECT t2.f1 FROM t1 limit 1) AS f9
@@ -1285,10 +1290,9 @@ FROM t2 JOIN t1
WHERE ('v') IN (SELECT f4 FROM t2)
GROUP BY f9;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 system NULL NULL NULL NULL 1
-1 PRIMARY t1 ALL NULL NULL NULL NULL 2
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 2
SELECT COUNT(t2.f3),
(SELECT t2.f1 FROM t1 limit 1) AS f9
FROM t2 JOIN t1
@@ -1302,10 +1306,9 @@ FROM t2 JOIN t1
WHERE ('v') IN (SELECT f4 FROM t2)
ORDER BY f9;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 system NULL NULL NULL NULL 1
-1 PRIMARY t1 ALL NULL NULL NULL NULL 2
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 2
SELECT COUNT(t2.f3),
(SELECT t2.f1 FROM t1 limit 1) AS f9
FROM t2 JOIN t1
@@ -1325,7 +1328,7 @@ EXPLAIN
SELECT (SELECT f1 FROM t1) AS field1 FROM t2 GROUP BY field1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 2
-2 SUBQUERY t1 system NULL NULL NULL NULL 0 const row not found
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
SELECT (SELECT f1 FROM t1) AS field1 FROM t2 GROUP BY field1;
field1
NULL
@@ -1333,7 +1336,7 @@ EXPLAIN
SELECT (SELECT f1 FROM t1) AS field1 FROM t2 ORDER BY field1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 2
-2 SUBQUERY t1 system NULL NULL NULL NULL 0 const row not found
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
SELECT (SELECT f1 FROM t1) AS field1 FROM t2 ORDER BY field1;
field1
NULL
@@ -1631,7 +1634,7 @@ SET SESSION optimizer_switch='in_to_exists=on,materialization=off';
EXPLAIN
SELECT * FROM t1 WHERE ( 6 ) NOT IN ( SELECT t2.f3 FROM t2 JOIN t3 ON t3.f10 = t2.f10);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 2
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
2 SUBQUERY t3 system NULL NULL NULL NULL 1
2 SUBQUERY t2 ref_or_null f10 f10 10 const,const 2 Using where; Using index
SELECT * FROM t1 WHERE ( 6 ) NOT IN ( SELECT t2.f3 FROM t2 JOIN t3 ON t3.f10 = t2.f10);
@@ -1852,8 +1855,8 @@ GROUP BY 1, 2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 system NULL NULL NULL NULL 1 Using temporary; Using filesort
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
-2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
-3 MATERIALIZED t1 system NULL NULL NULL NULL 0 const row not found
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
+3 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL no matching row in const table
PREPARE st1 FROM "
SELECT t2.f1, (SELECT f2 FROM t1 WHERE (7) IN (SELECT f1 FROM t1))
FROM t2 JOIN t3 ON t3.f4 = t2.f4
View Lorry Controller Status