1
drop table if exists t1,t2;
2
set @sav_dpi= @@div_precision_increment;
3
set div_precision_increment= 5;
4
show variables like 'div_precision_increment';
6
div_precision_increment 5
7
create table t1 (product varchar(32), country_id int not null, year int, profit int);
8
insert into t1 values ( 'Computer', 2,2000, 1200),
10
( 'Calculator', 1, 1999,50),
11
( 'Computer', 1, 1999,1500),
12
( 'Computer', 1, 2000,1500),
13
( 'TV', 1, 2000, 150),
14
( 'TV', 2, 2000, 100),
15
( 'TV', 2, 2000, 100),
16
( 'Calculator', 1, 2000,75),
17
( 'Calculator', 2, 2000,75),
18
( 'TV', 1, 1999, 100),
19
( 'Computer', 1, 1999,1200),
20
( 'Computer', 2, 2000,1500),
21
( 'Calculator', 2, 2000,75),
22
( 'Phone', 3, 2003,10)
24
create table t2 (country_id int primary key, country char(20) not null);
25
insert into t2 values (1, 'USA'),(2,'India'), (3,'Finland');
26
select product, sum(profit) from t1 group by product;
32
select product, sum(profit) from t1 group by product with rollup;
39
select product, sum(profit) from t1 group by 1 with rollup;
46
select product, sum(profit),avg(profit) from t1 group by product with rollup;
47
product sum(profit) avg(profit)
48
Calculator 275 68.75000
49
Computer 6900 1380.00000
53
select product, country_id , year, sum(profit) from t1 group by product, country_id, year;
54
product country_id year sum(profit)
65
select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup;
66
product country_id year sum(profit)
72
Calculator NULL NULL 275
78
Computer NULL NULL 6900
89
explain extended select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup;
90
id select_type table type possible_keys key key_len ref rows filtered Extra
91
1 SIMPLE t1 ALL NULL NULL NULL NULL 15 100.00 Using filesort
93
Note 1003 select `test`.`t1`.`product` AS `product`,`test`.`t1`.`country_id` AS `country_id`,`test`.`t1`.`year` AS `year`,sum(`test`.`t1`.`profit`) AS `sum(profit)` from `test`.`t1` group by `test`.`t1`.`product`,`test`.`t1`.`country_id`,`test`.`t1`.`year` with rollup
94
select product, country_id , sum(profit) from t1 group by product desc, country_id with rollup;
95
product country_id sum(profit)
108
select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup limit 5;
109
product country_id year sum(profit)
112
Calculator 1 NULL 125
113
Calculator 2 2000 150
114
Calculator 2 NULL 150
115
select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup limit 3,3;
116
product country_id year sum(profit)
117
Calculator 2 2000 150
118
Calculator 2 NULL 150
119
Calculator NULL NULL 275
120
select product, country_id, count(*), count(distinct year) from t1 group by product, country_id;
121
product country_id count(*) count(distinct year)
129
select product, country_id, count(*), count(distinct year) from t1 group by product, country_id with rollup;
130
product country_id count(*) count(distinct year)
143
select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup having country_id = 1;
144
product country_id year sum(profit)
147
Calculator 1 NULL 125
154
select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup having sum(profit) > 200;
155
product country_id year sum(profit)
156
Calculator NULL NULL 275
162
Computer NULL NULL 6900
167
select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup having sum(profit) > 7000;
168
product country_id year sum(profit)
170
select concat(product,':',country_id) as 'prod', concat(":",year,":") as 'year',1+1, sum(profit)/count(*) from t1 group by 1,2 with rollup;
171
prod year 1+1 sum(profit)/count(*)
172
Calculator:1 :1999: 2 50.00000
173
Calculator:1 :2000: 2 75.00000
174
Calculator:1 NULL 2 62.50000
175
Calculator:2 :2000: 2 75.00000
176
Calculator:2 NULL 2 75.00000
177
Computer:1 :1999: 2 1350.00000
178
Computer:1 :2000: 2 1500.00000
179
Computer:1 NULL 2 1400.00000
180
Computer:2 :2000: 2 1350.00000
181
Computer:2 NULL 2 1350.00000
182
Phone:3 :2003: 2 10.00000
183
Phone:3 NULL 2 10.00000
184
TV:1 :1999: 2 125.00000
185
TV:1 :2000: 2 150.00000
186
TV:1 NULL 2 133.33333
187
TV:2 :2000: 2 100.00000
188
TV:2 NULL 2 100.00000
189
NULL NULL 2 519.00000
190
select product, sum(profit)/count(*) from t1 group by product with rollup;
191
product sum(profit)/count(*)
197
select left(product,4) as prod, sum(profit)/count(*) from t1 group by prod with rollup;
198
prod sum(profit)/count(*)
204
select concat(product,':',country_id), 1+1, sum(profit)/count(*) from t1 group by concat(product,':',country_id) with rollup;
205
concat(product,':',country_id) 1+1 sum(profit)/count(*)
206
Calculator:1 2 62.50000
207
Calculator:2 2 75.00000
208
Computer:1 2 1400.00000
209
Computer:2 2 1350.00000
214
select product, country , year, sum(profit) from t1,t2 where t1.country_id=t2.country_id group by product, country, year with rollup;
215
product country year sum(profit)
216
Calculator India 2000 150
217
Calculator India NULL 150
218
Calculator USA 1999 50
219
Calculator USA 2000 75
220
Calculator USA NULL 125
221
Calculator NULL NULL 275
222
Computer India 2000 2700
223
Computer India NULL 2700
224
Computer USA 1999 2700
225
Computer USA 2000 1500
226
Computer USA NULL 4200
227
Computer NULL NULL 6900
228
Phone Finland 2003 10
229
Phone Finland NULL 10
238
select product, `sum` from (select product, sum(profit) as 'sum' from t1 group by product with rollup) as tmp where product is null;
241
select product from t1 where exists (select product, country_id , sum(profit) from t1 as t2 where t1.product=t2.product group by product, country_id with rollup having sum(profit) > 6000);
248
select product, country_id , year, sum(profit) from t1 group by product, country_id, year having country_id is NULL;
249
product country_id year sum(profit)
250
select concat(':',product,':'), sum(profit),avg(profit) from t1 group by product with rollup;
251
concat(':',product,':') sum(profit) avg(profit)
252
:Calculator: 275 68.75000
253
:Computer: 6900 1380.00000
257
select product, country_id , year, sum(profit) from t1 group by product, country_id, year with cube;
258
ERROR 42000: This version of MySQL doesn't yet support 'CUBE'
259
explain select product, country_id , year, sum(profit) from t1 group by product, country_id, year with cube;
260
ERROR 42000: This version of MySQL doesn't yet support 'CUBE'
261
select product, country_id , year, sum(profit) from t1 group by product, country_id, year with cube union all select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup;
262
ERROR 42000: This version of MySQL doesn't yet support 'CUBE'
264
CREATE TABLE t1 (i int);
265
INSERT INTO t1 VALUES(100);
266
CREATE TABLE t2 (i int);
267
INSERT INTO t2 VALUES (100),(200);
268
SELECT i, COUNT(*) FROM t1 GROUP BY i WITH ROLLUP;
272
SELECT t1.i, t2.i, COUNT(*) FROM t1,t2 GROUP BY t1.i,t2.i WITH ROLLUP;
279
CREATE TABLE user_day(
280
user_id INT NOT NULL,
282
UNIQUE INDEX user_date (user_id, date)
284
INSERT INTO user_day VALUES
290
COUNT(d.user_id) as sample,
291
COUNT(next_day.user_id) AS not_cancelled
293
LEFT JOIN user_day next_day
294
ON next_day.user_id=d.user_id AND
295
next_day.date= DATE_ADD( d.date, interval 1 day )
297
day sample not_cancelled
302
COUNT(d.user_id) as sample,
303
COUNT(next_day.user_id) AS not_cancelled
305
LEFT JOIN user_day next_day
306
ON next_day.user_id=d.user_id AND
307
next_day.date= DATE_ADD( d.date, interval 1 day )
310
day sample not_cancelled
315
CREATE TABLE t1 (a int, b int);
316
INSERT INTO t1 VALUES
319
(4,1), (4,1), (4,1), (4,1),
321
SELECT SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
327
SELECT DISTINCT SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
332
SELECT SUM(b), COUNT(DISTINCT b) FROM t1 GROUP BY a WITH ROLLUP;
333
SUM(b) COUNT(DISTINCT b)
338
SELECT DISTINCT SUM(b), COUNT(DISTINCT b) FROM t1 GROUP BY a WITH ROLLUP;
339
SUM(b) COUNT(DISTINCT b)
343
SELECT SUM(b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
349
SELECT DISTINCT SUM(b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
355
SELECT SUM(b), COUNT(DISTINCT b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
356
SUM(b) COUNT(DISTINCT b) COUNT(*)
361
SELECT DISTINCT SUM(b), COUNT(DISTINCT b), COUNT(*) FROM t1
362
GROUP BY a WITH ROLLUP;
363
SUM(b) COUNT(DISTINCT b) COUNT(*)
368
SELECT a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;
378
SELECT DISTINCT a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;
386
SELECT b, a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;
396
SELECT DISTINCT b,a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;
406
ALTER TABLE t1 ADD COLUMN c INT;
407
SELECT a,b,sum(c) FROM t1 GROUP BY a,b,c WITH ROLLUP;
421
SELECT distinct a,b,sum(c) FROM t1 GROUP BY a,b,c WITH ROLLUP;
432
CREATE TABLE t1 (a int, b int);
433
INSERT INTO t1 VALUES
436
(4,1), (4,1), (4,1), (4,1),
438
SELECT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1;
441
SELECT SQL_CALC_FOUND_ROWS a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1;
445
CREATE TABLE t1 (a int(11) NOT NULL);
446
INSERT INTO t1 VALUES (1),(2);
447
SELECT a, SUM(a) m FROM t1 GROUP BY a WITH ROLLUP;
452
SELECT * FROM ( SELECT a, SUM(a) m FROM t1 GROUP BY a WITH ROLLUP ) t2;
458
set div_precision_increment= @sav_dpi;
459
CREATE TABLE t1 (a int(11));
460
INSERT INTO t1 VALUES (1),(2);
461
SELECT a, SUM(a), SUM(a)+1 FROM (SELECT a FROM t1 UNION select 2) d
466
SELECT a, SUM(a), SUM(a)+1 FROM (SELECT a FROM t1 UNION select 2) d
467
GROUP BY a WITH ROLLUP;
472
SELECT a, SUM(a), SUM(a)+1 FROM (SELECT 1 a UNION select 2) d
477
SELECT a, SUM(a), SUM(a)+1 FROM (SELECT 1 a UNION select 2) d
478
GROUP BY a WITH ROLLUP;
483
SELECT a, SUM(a), SUM(a)+1, CONCAT(SUM(a),'x'), SUM(a)+SUM(a), SUM(a)
484
FROM (SELECT 1 a, 2 b UNION SELECT 2,3 UNION SELECT 5,6 ) d
485
GROUP BY a WITH ROLLUP;
486
a SUM(a) SUM(a)+1 CONCAT(SUM(a),'x') SUM(a)+SUM(a) SUM(a)
492
CREATE TABLE t1 (a int(11));
493
INSERT INTO t1 VALUES (1),(2);
494
SELECT a, a+1, SUM(a) FROM t1 GROUP BY a WITH ROLLUP;
499
SELECT a+1 FROM t1 GROUP BY a WITH ROLLUP;
504
SELECT a+SUM(a) FROM t1 GROUP BY a WITH ROLLUP;
509
SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING b > 2;
512
SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING a IS NULL;
515
SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING b IS NULL;
518
SELECT IFNULL(a, 'TEST') FROM t1 GROUP BY a WITH ROLLUP;
523
CREATE TABLE t2 (a int, b int);
524
INSERT INTO t2 VALUES
527
(4,1), (4,1), (4,1), (4,1),
529
SELECT a,b,SUM(b) FROM t2 GROUP BY a,b WITH ROLLUP;
539
SELECT a,b,SUM(b), a+b as c FROM t2
540
GROUP BY a,b WITH ROLLUP HAVING c IS NULL;
546
SELECT IFNULL(a, 'TEST'), COALESCE(b, 'TEST') FROM t2
547
GROUP BY a, b WITH ROLLUP;
548
IFNULL(a, 'TEST') COALESCE(b, 'TEST')
558
CREATE TABLE t1 (a INT(10) NOT NULL, b INT(10) NOT NULL);
559
INSERT INTO t1 VALUES (1, 1);
560
INSERT INTO t1 VALUES (1, 2);
561
SELECT a, b, a AS c, COUNT(*) AS count FROM t1 GROUP BY a, b, c WITH ROLLUP;
570
CREATE TABLE t1 (a int(11) NOT NULL);
571
INSERT INTO t1 VALUES (1),(2);
572
SELECT * FROM (SELECT a, a + 1, COUNT(*) FROM t1 GROUP BY a WITH ROLLUP) t;
577
SELECT * FROM (SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP) t;
583
create table t1 ( a varchar(9), b int );
584
insert into t1 values('a',1),(null,2);
585
select a, max(b) from t1 group by a with rollup;
590
select distinct a, max(b) from t1 group by a with rollup;
595
create table t1 (a varchar(22) not null , b int);
596
insert into t1 values ("2006-07-01 21:30", 1), ("2006-07-01 23:30", 10);
597
select left(a,10), a, sum(b) from t1 group by 1,2 with rollup;
599
2006-07-01 2006-07-01 21:30 1
600
2006-07-01 2006-07-01 23:30 10
603
select left(a,10) x, a, sum(b) from t1 group by x,a with rollup;
605
2006-07-01 2006-07-01 21:30 1
606
2006-07-01 2006-07-01 23:30 10
610
CREATE TABLE t1 (a int, b int);
612
VALUES (2,10),(3,30),(2,40),(1,10),(2,30),(1,20),(2,10);
613
SELECT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
619
SELECT DISTINCT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
625
SELECT a, b, COUNT(*) FROM t1 GROUP BY a,b WITH ROLLUP;
637
SELECT DISTINCT a, b, COUNT(*) FROM t1 GROUP BY a,b WITH ROLLUP;
649
SELECT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP;
656
SELECT DISTINCT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP;
663
SELECT DISTINCT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP;
671
CREATE TABLE t1(id int, type char(1));
672
INSERT INTO t1 VALUES
673
(1,"A"),(2,"C"),(3,"A"),(4,"A"),(5,"B"),
674
(6,"B"),(7,"A"),(8,"C"),(9,"A"),(10,"C");
675
CREATE VIEW v1 AS SELECT * FROM t1;
676
SELECT type FROM t1 GROUP BY type WITH ROLLUP;
682
SELECT type FROM v1 GROUP BY type WITH ROLLUP;
688
EXPLAIN SELECT type FROM v1 GROUP BY type WITH ROLLUP;
689
id select_type table type possible_keys key key_len ref rows Extra
690
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using filesort
693
CREATE TABLE t1 (a int(11) NOT NULL);
694
INSERT INTO t1 VALUES (1),(2);
696
SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
698
Field Type Null Key Default Extra
699
a bigint(11) YES NULL
700
LENGTH(a) bigint(10) YES NULL
701
COUNT(*) bigint(21) NO 0
709
CREATE TABLE t1 (a int, KEY (a));
710
INSERT INTO t1 VALUES (3), (1), (4), (1), (3), (1), (1);
711
SELECT * FROM (SELECT a, SUM(a) FROM t1 GROUP BY a WITH ROLLUP) as t;
719
# Bug#31095: Unexpected NULL constant caused server crash.
721
create table t1(a int);
722
insert into t1 values (1),(2),(3);
723
select count(a) from t1 group by null with rollup;
728
##############################################################
729
CREATE TABLE t1(a INT);
730
INSERT INTO t1 VALUES(0);
731
SELECT 1 FROM t1 GROUP BY (DATE(NULL)) WITH ROLLUP;
added
removed
mysql-test/r/olap.result
