diff options
Diffstat (limited to 'mysql-test/r/group_by.result')
| -rw-r--r-- | mysql-test/r/group_by.result | 27 |
1 files changed, 11 insertions, 16 deletions
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index bb1b3bab2c3..faa9d039d7b 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -538,11 +538,11 @@ a b explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort -1 SIMPLE t2 ALL a NULL NULL NULL 4 Using where; Using join buffer +1 SIMPLE t2 ALL a NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary -1 SIMPLE t2 ALL a NULL NULL NULL 4 Using where; Using join buffer +1 SIMPLE t2 ALL a NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) drop table t1,t2; create table t1 (a int, b int); insert into t1 values (1, 4),(10, 40),(1, 4),(10, 43),(1, 4),(10, 41),(1, 4),(10, 43),(1, 4); @@ -874,7 +874,7 @@ explain SELECT straight_join sql_no_cache v1.a, v1.b, v1.real_b from t2, v1 where t2.b=v1.a GROUP BY t2.b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index b b 2 NULL 10 Using index +1 SIMPLE t2 index b b 2 NULL 10 Using where; Using index 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 1 test.t2.b 1 SELECT straight_join sql_no_cache v1.a, v1.b, v1.real_b from t2, v1 where t2.b=v1.a GROUP BY t2.b; @@ -1543,8 +1543,8 @@ id select_type table type possible_keys key key_len ref rows Extra 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 t1 index NULL PRIMARY 4 NULL 144 Using where; Using index +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 144 Using where 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; @@ -1556,13 +1556,8 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT 1 FROM t2 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 t2 index a a 5 NULL 4 Using index -1 PRIMARY t1 ALL NULL NULL NULL NULL 144 Using where; FirstMatch(t2) -SHOW VARIABLES LIKE 'old'; -Variable_name Value -old OFF -SET @@old = off; -ERROR HY000: Variable 'old' is a read only variable +1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 144 Using where DROP TABLE t1, t2; CREATE TABLE t1( a INT, @@ -1743,7 +1738,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 -Note 1003 select <expr_cache><`test`.`t1`.`a`>((select `test`.`t1`.`a`)) AS `aa`,count(distinct `test`.`t1`.`b`) AS `COUNT(DISTINCT b)` from `test`.`t1` group by (<expr_cache><`test`.`t1`.`a`>((select `test`.`t1`.`a`)) + 0) +Note 1003 select (select `test`.`t1`.`a`) AS `aa`,count(distinct `test`.`t1`.`b`) AS `COUNT(DISTINCT b)` from `test`.`t1` group by ((select `test`.`t1`.`a`) + 0) EXPLAIN EXTENDED SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) FROM t1 GROUP BY -aa; id select_type table type possible_keys key key_len ref rows filtered Extra @@ -1751,7 +1746,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 -Note 1003 select <expr_cache><`test`.`t1`.`a`>((select `test`.`t1`.`a`)) AS `aa`,count(distinct `test`.`t1`.`b`) AS `COUNT(DISTINCT b)` from `test`.`t1` group by -(<expr_cache><`test`.`t1`.`a`>((select `test`.`t1`.`a`))) +Note 1003 select (select `test`.`t1`.`a`) AS `aa`,count(distinct `test`.`t1`.`b`) AS `COUNT(DISTINCT b)` from `test`.`t1` group by -((select `test`.`t1`.`a`)) # should return only one record SELECT (SELECT tt.a FROM t1 tt LIMIT 1) aa, COUNT(DISTINCT b) FROM t1 GROUP BY aa; @@ -1899,8 +1894,8 @@ SELECT a, AVG(t1.b), FROM t1 GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL a 10 NULL 9 Using index -3 DEPENDENT SUBQUERY t12 ref a a 10 func,func 2 Using index condition -2 DEPENDENT SUBQUERY t11 ref a a 10 func,func 2 Using index condition +3 DEPENDENT SUBQUERY t12 ref a a 10 func,func 2 Using where +2 DEPENDENT SUBQUERY t11 ref a a 10 func,func 2 Using where SELECT a, AVG(t1.b), (SELECT t11.c FROM t1 t11 WHERE t11.a = t1.a AND t11.b = AVG(t1.b)) AS t11c, (SELECT t12.c FROM t1 t12 WHERE t12.a = t1.a AND t12.b = AVG(t1.b)) AS t12c |