diff options
Diffstat (limited to 'mysql-test/r/group_by.result')
| -rw-r--r-- | mysql-test/r/group_by.result | 102 |
1 files changed, 102 insertions, 0 deletions
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 663ef6cced4..d583eb02aeb 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -933,6 +933,108 @@ b sum(1) 18 6 19 6 DROP TABLE t1; +CREATE TABLE t1 (a INT PRIMARY KEY, b INT); +INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3); +SET SQL_MODE = 'ONLY_FULL_GROUP_BY'; +SELECT MAX(a)-MIN(a) FROM t1 GROUP BY b; +MAX(a)-MIN(a) +1 +1 +1 +SELECT CEILING(MIN(a)) FROM t1 GROUP BY b; +CEILING(MIN(a)) +1 +3 +5 +SELECT CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END FROM t1 +GROUP BY b; +CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END +Positive +Positive +Positive +SELECT a + 1 FROM t1 GROUP BY a; +a + 1 +2 +3 +4 +5 +6 +7 +SELECT a + b FROM t1 GROUP BY b; +ERROR 42000: 'test.t1.a' isn't in GROUP BY +SELECT (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1) +FROM t1 AS t1_outer; +(SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1) +1 +2 +3 +4 +5 +6 +SELECT 1 FROM t1 as t1_outer GROUP BY a +HAVING (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1); +1 +1 +1 +1 +1 +1 +1 +SELECT (SELECT t1_outer.a FROM t1 AS t1_inner LIMIT 1) +FROM t1 AS t1_outer GROUP BY t1_outer.b; +ERROR 42000: 'test.t1_outer.a' isn't in GROUP BY +SELECT 1 FROM t1 as t1_outer GROUP BY a +HAVING (SELECT t1_outer.b FROM t1 AS t1_inner LIMIT 1); +ERROR 42S22: Unknown column 'test.t1_outer.b' in 'field list' +SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner LIMIT 1) +FROM t1 AS t1_outer GROUP BY t1_outer.b; +(SELECT SUM(t1_inner.a) FROM t1 AS t1_inner LIMIT 1) +21 +21 +21 +SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1) +FROM t1 AS t1_outer; +(SELECT SUM(t1_inner.a) FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1) +3 +3 +3 +3 +3 +3 +SELECT (SELECT SUM(t1_outer.a) FROM t1 AS t1_inner LIMIT 1) +FROM t1 AS t1_outer GROUP BY t1_outer.b; +ERROR 42000: 'test.t1_outer.a' isn't in GROUP BY +SELECT 1 FROM t1 as t1_outer +WHERE (SELECT t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1); +1 +1 +1 +1 +1 +1 +1 +SELECT b FROM t1 GROUP BY b HAVING CEILING(b) > 0; +b +1 +2 +3 +SELECT 1 FROM t1 GROUP BY b HAVING b = 2 OR b = 3 OR SUM(a) > 12; +1 +1 +1 +SELECT 1 FROM t1 GROUP BY b HAVING ROW (b,b) = ROW (1,1); +1 +1 +SELECT 1 FROM t1 GROUP BY b HAVING a = 2; +ERROR 42S22: Unknown column 'a' in 'having clause' +SELECT 1 FROM t1 GROUP BY SUM(b); +ERROR HY000: Invalid use of group function +SELECT b FROM t1 AS t1_outer GROUP BY a HAVING t1_outer.a IN +(SELECT SUM(t1_inner.b)+t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.a +HAVING SUM(t1_inner.b)+t1_outer.b > 5); +ERROR 42000: 'test.t1_outer.b' isn't in GROUP BY +DROP TABLE t1; +SET SQL_MODE = ''; CREATE TABLE t1 (a INT, b INT, KEY(a)); INSERT INTO t1 VALUES (1, 1), (2, 2), (3,3), (4,4); EXPLAIN SELECT a, SUM(b) FROM t1 GROUP BY a LIMIT 2; |