diff options
| author | igor@rurik.mysql.com <> | 2005-05-30 05:47:50 -0700 |
|---|---|---|
| committer | igor@rurik.mysql.com <> | 2005-05-30 05:47:50 -0700 |
| commit | f987085aa47f392914d195d5798356ae09c3d559 (patch) | |
| tree | 40f2b026e1cfce38d8289a5103df455b64796d75 /mysql-test/r/olap.result | |
| parent | 329d974df743975d4f898dbb8fc7a31fca818f6b (diff) | |
| parent | e8a6fa42783a484a6bfef802feb987513d0ff0db (diff) | |
| download | mariadb-git-f987085aa47f392914d195d5798356ae09c3d559.tar.gz | |
Manual post merge
Diffstat (limited to 'mysql-test/r/olap.result')
| -rw-r--r-- | mysql-test/r/olap.result | 76 |
1 files changed, 71 insertions, 5 deletions
diff --git a/mysql-test/r/olap.result b/mysql-test/r/olap.result index 9411edcdc00..78326511f00 100644 --- a/mysql-test/r/olap.result +++ b/mysql-test/r/olap.result @@ -249,11 +249,11 @@ select product, country_id , year, sum(profit) from t1 group by product, country product country_id year sum(profit) select concat(':',product,':'), sum(profit),avg(profit) from t1 group by product with rollup; concat(':',product,':') sum(profit) avg(profit) -:Calculator: 275 68.75000 -:Computer: 6900 1380.00000 -:Phone: 10 10.00000 -:TV: 600 120.00000 -:TV: 7785 519.00000 +:Calculator: 275 68.7500 +:Computer: 6900 1380.0000 +:Phone: 10 10.0000 +:TV: 600 120.0000 +:TV: 7785 519.0000 select product, country_id , year, sum(profit) from t1 group by product, country_id, year with cube; ERROR 42000: This version of MySQL doesn't yet support 'CUBE' explain select product, country_id , year, sum(profit) from t1 group by product, country_id, year with cube; @@ -489,3 +489,69 @@ a SUM(a) SUM(a)+1 CONCAT(SUM(a),'x') SUM(a)+SUM(a) SUM(a) 5 5 6 5x 10 5 NULL 8 9 8x 16 8 DROP TABLE t1; +CREATE TABLE t1 (a int(11)); +INSERT INTO t1 VALUES (1),(2); +SELECT a, a+1, SUM(a) FROM t1 GROUP BY a WITH ROLLUP; +a a+1 SUM(a) +1 2 1 +2 3 2 +NULL NULL 3 +SELECT a+1 FROM t1 GROUP BY a WITH ROLLUP; +a+1 +2 +3 +NULL +SELECT a+SUM(a) FROM t1 GROUP BY a WITH ROLLUP; +a+SUM(a) +2 +4 +NULL +SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING b > 2; +a b +2 3 +SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING a IS NULL; +a b +NULL NULL +SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING b IS NULL; +a b +NULL NULL +SELECT IFNULL(a, 'TEST') FROM t1 GROUP BY a WITH ROLLUP; +IFNULL(a, 'TEST') +1 +2 +TEST +CREATE TABLE t2 (a int, b int); +INSERT INTO t2 VALUES +(1,4), +(2,2), (2,2), +(4,1), (4,1), (4,1), (4,1), +(2,1), (2,1); +SELECT a,b,SUM(b) FROM t2 GROUP BY a,b WITH ROLLUP; +a b SUM(b) +1 4 4 +1 NULL 4 +2 1 2 +2 2 4 +2 NULL 6 +4 1 4 +4 NULL 4 +NULL NULL 14 +SELECT a,b,SUM(b), a+b as c FROM t2 +GROUP BY a,b WITH ROLLUP HAVING c IS NULL; +a b SUM(b) c +1 NULL 4 NULL +2 NULL 6 NULL +4 NULL 4 NULL +NULL NULL 14 NULL +SELECT IFNULL(a, 'TEST'), COALESCE(b, 'TEST') FROM t2 +GROUP BY a, b WITH ROLLUP; +IFNULL(a, 'TEST') COALESCE(b, 'TEST') +1 4 +1 TEST +2 1 +2 2 +2 TEST +4 1 +4 TEST +TEST TEST +DROP TABLE t1,t2; |