diff options
Diffstat (limited to 'mysql-test/r/derived.result')
| -rw-r--r-- | mysql-test/r/derived.result | 14 |
1 files changed, 7 insertions, 7 deletions
diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index ebbc08aa958..78ce4a1d746 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -191,15 +191,15 @@ 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 9 Using where; Using join buffer (flat, BNL join) +1 PRIMARY <derived2> hash_ALL NULL #hash#$hj 7 test.m2.matintnum 9 Using where; Using join buffer (flat, BNLH join) 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 +2 DERIVED m1 hash_ALL PRIMARY #hash#PRIMARY 3 test.mp.mat_id 9 Using join buffer (flat, BNLH join) 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 9 Using where; Using join buffer (flat, BNL join) +1 PRIMARY <derived2> hash_ALL NULL #hash#$hj 7 test.m2.matintnum 9 Using where; Using join buffer (flat, BNLH join) 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 +2 DERIVED m1 hash_ALL PRIMARY #hash#PRIMARY 3 test.mp.mat_id 9 Using join buffer (flat, BNLH join) drop table t1,t2; SELECT a.x FROM (SELECT 1 AS x) AS a HAVING a.x = 1; x @@ -236,7 +236,7 @@ count(*) explain select count(*) from t1 INNER JOIN (SELECT A.E1, A.E2, A.E3 FROM t1 AS A WHERE A.E3 = (SELECT MAX(B.E3) FROM t1 AS B WHERE A.E2 = B.E2)) AS THEMAX ON t1.E1 = THEMAX.E2 AND t1.E1 = t1.E2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 -1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 THEMAX.E2 1 Using where +1 PRIMARY t1 hash_ALL PRIMARY #hash#PRIMARY 4 THEMAX.E2 2 Using where; Using join buffer (flat, BNLH join) 2 DERIVED A ALL NULL NULL NULL NULL 2 Using where 3 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 2 Using where drop table t1; @@ -317,8 +317,8 @@ 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 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 (flat, BNL join) +2 DERIVED x ALL NULL NULL NULL NULL 17 Using where; Using temporary; Using filesort +2 DERIVED y hash_ALL NULL #hash#$hj 2 test.x.name 17 Using where; Using join buffer (flat, BNLH join) drop table t1; create table t2 (a int, b int, primary key (a)); insert into t2 values (1,7),(2,7); |