1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
|
--disable_warnings
drop table if exists t1,t2;
--enable_warnings
create table t1 (product varchar(32), country_id int not null, year int, profit int);
insert into t1 values ( 'Computer', 2,2000, 1200),
( 'TV', 1, 1999, 150),
( 'Calculator', 1, 1999,50),
( 'Computer', 1, 1999,1500),
( 'Computer', 1, 2000,1500),
( 'TV', 1, 2000, 150),
( 'TV', 2, 2000, 100),
( 'TV', 2, 2000, 100),
( 'Calculator', 1, 2000,75),
( 'Calculator', 2, 2000,75),
( 'TV', 1, 1999, 100),
( 'Computer', 1, 1999,1200),
( 'Computer', 2, 2000,1500),
( 'Calculator', 2, 2000,75),
( 'Phone', 3, 2003,10)
;
create table t2 (country_id int primary key, country char(20) not null);
insert into t2 values (1, 'USA'),(2,'India'), (3,'Finland');
# First simple rollups, with just grand total
select product, sum(profit) from t1 group by product;
select product, sum(profit) from t1 group by product with rollup;
select product, sum(profit) from t1 group by 1 with rollup;
select product, sum(profit),avg(profit) from t1 group by product with rollup;
# Sub totals
select product, country_id , year, sum(profit) from t1 group by product, country_id, year;
select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup;
explain extended select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup;
select product, country_id , sum(profit) from t1 group by product desc, country_id with rollup;
# limit
select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup limit 5;
select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup limit 3,3;
select product, country_id, count(*), count(distinct year) from t1 group by product, country_id;
select product, country_id, count(*), count(distinct year) from t1 group by product, country_id with rollup;
# Test of having
select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup having country_id = 1;
select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup having sum(profit) > 200;
select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup having sum(profit) > 7000;
# Functions
select concat(product,':',country_id) as 'prod', concat(":",year,":") as 'year',1+1, sum(profit)/count(*) from t1 group by 1,2 with rollup;
select product, sum(profit)/count(*) from t1 group by product with rollup;
select left(product,4) as prod, sum(profit)/count(*) from t1 group by prod with rollup;
select concat(product,':',country_id), 1+1, sum(profit)/count(*) from t1 group by concat(product,':',country_id) with rollup;
# Joins
select product, country , year, sum(profit) from t1,t2 where t1.country_id=t2.country_id group by product, country, year with rollup;
# Derived tables and sub selects
select product, `sum` from (select product, sum(profit) as 'sum' from t1 group by product with rollup) as tmp where product is null;
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);
# The following doesn't return the expected answer, but this is a limitation
# in the implementation so we should just document it
select product, country_id , year, sum(profit) from t1 group by product, country_id, year having country_id is NULL;
select concat(':',product,':'), sum(profit),avg(profit) from t1 group by product with rollup;
# Error handling
# Cube is not yet implemented
--error 1235
select product, country_id , year, sum(profit) from t1 group by product, country_id, year with cube;
--error 1235
explain select product, country_id , year, sum(profit) from t1 group by product, country_id, year with cube;
--error 1235
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;
drop table t1,t2;
#
# Test bug with const tables
#
CREATE TABLE t1 (i int);
INSERT INTO t1 VALUES(100);
CREATE TABLE t2 (i int);
INSERT INTO t2 VALUES (100),(200);
SELECT i, COUNT(*) FROM t1 GROUP BY i WITH ROLLUP;
SELECT t1.i, t2.i, COUNT(*) FROM t1,t2 GROUP BY t1.i,t2.i WITH ROLLUP;
drop table t1,t2;
#bug #4767: ROLLUP with LEFT JOIN
CREATE TABLE user_day(
user_id INT NOT NULL,
date DATE NOT NULL,
UNIQUE INDEX user_date (user_id, date)
);
INSERT INTO user_day VALUES
(1, '2004-06-06' ),
(1, '2004-06-07' ),
(2, '2004-06-06' );
SELECT
d.date AS day,
COUNT(d.user_id) as sample,
COUNT(next_day.user_id) AS not_cancelled
FROM user_day d
LEFT JOIN user_day next_day
ON next_day.user_id=d.user_id AND
next_day.date= DATE_ADD( d.date, interval 1 day )
GROUP BY day;
SELECT
d.date AS day,
COUNT(d.user_id) as sample,
COUNT(next_day.user_id) AS not_cancelled
FROM user_day d
LEFT JOIN user_day next_day
ON next_day.user_id=d.user_id AND
next_day.date= DATE_ADD( d.date, interval 1 day )
GROUP BY day
WITH ROLLUP;
DROP TABLE user_day;
#
# Tests for bugs #8616, #8615: distinct sum with rollup
#
CREATE TABLE t1 (a int, b int);
INSERT INTO t1 VALUES
(1,4),
(2,2), (2,2),
(4,1), (4,1), (4,1), (4,1),
(2,1), (2,1);
SELECT SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
SELECT DISTINCT SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
SELECT SUM(b), COUNT(DISTINCT b) FROM t1 GROUP BY a WITH ROLLUP;
SELECT DISTINCT SUM(b), COUNT(DISTINCT b) FROM t1 GROUP BY a WITH ROLLUP;
SELECT SUM(b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
SELECT DISTINCT SUM(b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
SELECT SUM(b), COUNT(DISTINCT b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
SELECT DISTINCT SUM(b), COUNT(DISTINCT b), COUNT(*) FROM t1
GROUP BY a WITH ROLLUP;
SELECT a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;
SELECT DISTINCT a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;
DROP TABLE t1;
#
# Tests for bugs #8617: SQL_CACL_FOUND_ROWS with rollup and limit
#
CREATE TABLE t1 (a int, b int);
INSERT INTO t1 VALUES
(1,4),
(2,2), (2,2),
(4,1), (4,1), (4,1), (4,1),
(2,1), (2,1);
SELECT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1;
SELECT SQL_CALC_FOUND_ROWS a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1;
DROP TABLE t1;
|
