summaryrefslogtreecommitdiff
path: root/mysql-test/r/derived.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r/derived.result')
-rw-r--r--mysql-test/r/derived.result32
1 files changed, 21 insertions, 11 deletions
diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result
index a86eabc3192..575fdbe04b4 100644
--- a/mysql-test/r/derived.result
+++ b/mysql-test/r/derived.result
@@ -58,7 +58,7 @@ a b a b
explain select * from t1 as x1, (select * from t1) as x2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY x1 ALL NULL NULL NULL NULL 4
-1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 Using join buffer
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join)
2 DERIVED t1 ALL NULL NULL NULL NULL 4
drop table if exists t2,t3;
select * from (select 1) as a;
@@ -91,7 +91,7 @@ a b
2 b
explain select * from (select * from t1 union select * from t1) a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 8
2 DERIVED t1 ALL NULL NULL NULL NULL 4
3 UNION t1 ALL NULL NULL NULL NULL 4
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
@@ -113,7 +113,7 @@ a b
3 c
explain select * from (select t1.*, t2.a as t2a from t1,t2 where t1.a=t2.a) t1;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <derived2> system NULL NULL NULL NULL 1
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4
2 DERIVED t2 system NULL NULL NULL NULL 1
2 DERIVED t1 ALL NULL NULL NULL NULL 4 Using where
drop table t1, t2;
@@ -142,7 +142,8 @@ a t
20 20
explain select count(*) from t1 as tt1, (select * from t1) as tt2;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+1 PRIMARY tt1 index NULL a 4 NULL 10000 Using index
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 10000 Using join buffer (flat, BNL join)
2 DERIVED t1 ALL NULL NULL NULL NULL 10000
drop table t1;
SELECT * FROM (SELECT (SELECT * FROM (SELECT 1 as a) as a )) as b;
@@ -189,13 +190,13 @@ pla_id test
explain SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY m2 ALL NULL NULL NULL NULL 9
-1 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 Using where; Using join buffer
+1 PRIMARY <derived2> ALL NULL $hj 7 test.m2.matintnum 9 Using where
2 DERIVED mp ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
2 DERIVED m1 eq_ref PRIMARY PRIMARY 3 test.mp.mat_id 1
explain SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY m2 ALL NULL NULL NULL NULL 9
-1 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 Using where; Using join buffer
+1 PRIMARY <derived2> ALL NULL $hj 7 test.m2.matintnum 9 Using where
2 DERIVED mp ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
2 DERIVED m1 eq_ref PRIMARY PRIMARY 3 test.mp.mat_id 1
drop table t1,t2;
@@ -245,8 +246,8 @@ a a
2 2
explain select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2
-1 PRIMARY <derived4> ALL NULL NULL NULL NULL 2 Using join buffer
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4
+1 PRIMARY <derived4> ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join)
4 DERIVED t1 ALL NULL NULL NULL NULL 2
5 UNION t1 ALL NULL NULL NULL NULL 2
NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL
@@ -311,9 +312,9 @@ a 7.0000
b 3.5000
explain SELECT s.name, AVG(s.val) AS median FROM (SELECT x.name, x.val FROM t1 x, t1 y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val <= x.val) >= COUNT(*)/2 AND SUM(y.val >= x.val) >= COUNT(*)/2) AS s GROUP BY s.name;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 Using temporary; Using filesort
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 289 Using temporary; Using filesort
2 DERIVED x ALL NULL NULL NULL NULL 17 Using temporary; Using filesort
-2 DERIVED y ALL NULL NULL NULL NULL 17 Using where; Using join buffer
+2 DERIVED y ALL NULL NULL NULL NULL 17 Using where; Using join buffer (flat, BNL join)
drop table t1;
create table t2 (a int, b int, primary key (a));
insert into t2 values (1,7),(2,7);
@@ -322,7 +323,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index
explain select a from (select a from t2 where a>1) tt;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <derived2> system NULL NULL NULL NULL 1
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2
2 DERIVED t2 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index
drop table t2;
CREATE TABLE `t1` ( `itemid` int(11) NOT NULL default '0', `grpid` varchar(15) NOT NULL default '', `vendor` int(11) NOT NULL default '0', `date_` date NOT NULL default '0000-00-00', `price` decimal(12,2) NOT NULL default '0.00', PRIMARY KEY (`itemid`,`grpid`,`vendor`,`date_`), KEY `itemid` (`itemid`,`vendor`), KEY `itemid_2` (`itemid`,`date_`));
@@ -424,6 +425,15 @@ CREATE algorithm=temptable VIEW v1 AS
SELECT 1 FROM t1 LEFT JOIN t1 t3 ON 1 > (SELECT 1 FROM t1);
CREATE algorithm=temptable VIEW v2 AS SELECT 1 FROM t2;
EXPLAIN SELECT 1 FROM t1 JOIN v1 ON 1 > (SELECT 1 FROM v2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2
+1 PRIMARY <derived3> ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join)
+3 DERIVED t1 ALL NULL NULL NULL NULL 2
+3 DERIVED t3 ALL NULL NULL NULL NULL 2 Using where
+4 SUBQUERY t1 ALL NULL NULL NULL NULL 2
+2 SUBQUERY <derived5> ALL NULL NULL NULL NULL 2
+5 DERIVED t2 index NULL b 5 NULL 2 Using index
+SELECT 1 FROM t1 JOIN v1 ON 1 > (SELECT 1 FROM v2);
ERROR 21000: Subquery returns more than 1 row
DROP TABLE t1, t2;
DROP VIEW v1, v2;
View Lorry Controller Status