← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/t/olap.test

  • Committer: Monty Taylor
  • Date: 2008-09-16 00:00:48 UTC
  • mto: This revision was merged to the branch mainline in revision 391.
  • Revision ID: monty@inaugust.com-20080916000048-3rvrv3gv9l0ad3gs
Fixed copyright headers in drizzled/

Show diffs side-by-side

added added

removed removed

Lines of Context:
tests/t/olap.test
1
 
--disable_warnings
2
 
drop table if exists t1,t2;
3
 
--enable_warnings
4
 
 
5
 
set @sav_dpi= @@div_precision_increment;
6
 
set div_precision_increment= 5;
7
 
show variables like 'div_precision_increment';
8
 
 
9
 
create table t1 (product varchar(32), country_id int not null, year int, profit int);
10
 
insert into t1  values ( 'Computer', 2,2000, 1200),
11
 
( 'TV', 1, 1999, 150),
12
 
( 'Calculator', 1, 1999,50),
13
 
( 'Computer', 1, 1999,1500),
14
 
( 'Computer', 1, 2000,1500),
15
 
( 'TV', 1, 2000, 150),
16
 
( 'TV', 2, 2000, 100),
17
 
( 'TV', 2, 2000, 100),
18
 
( 'Calculator', 1, 2000,75),
19
 
( 'Calculator', 2, 2000,75),
20
 
( 'TV', 1, 1999, 100),
21
 
( 'Computer', 1, 1999,1200),
22
 
( 'Computer', 2, 2000,1500),
23
 
( 'Calculator', 2, 2000,75),
24
 
( 'Phone', 3, 2003,10)
25
 
;
26
 
 
27
 
create table t2 (country_id int primary key, country char(20) not null);
28
 
insert into t2 values (1, 'USA'),(2,'India'), (3,'Finland');
29
 
 
30
 
# First simple rollups, with just grand total
31
 
select product, sum(profit) from t1 group by product;
32
 
select product, sum(profit) from t1 group by product with rollup;
33
 
select product, sum(profit) from t1 group by 1 with rollup;
34
 
select product, sum(profit),avg(profit) from t1 group by product with rollup;
35
 
 
36
 
# Sub totals
37
 
select product, country_id , year, sum(profit) from t1 group by product, country_id, year;
38
 
select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup;
39
 
explain extended select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup;
40
 
select product, country_id , sum(profit) from t1 group by product desc, country_id with rollup;
41
 
 
42
 
# limit
43
 
select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup limit 5;
44
 
select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup limit 3,3;
45
 
 
46
 
select product, country_id, count(*), count(distinct year) from t1 group by product, country_id;
47
 
select product, country_id, count(*), count(distinct year) from t1 group by product, country_id with rollup;
48
 
 
49
 
# Test of having
50
 
select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup having country_id = 1;
51
 
select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup having sum(profit) > 200;
52
 
select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup having sum(profit) > 7000;
53
 
 
54
 
# Functions
55
 
select concat(product,':',country_id) as 'prod', concat(":",year,":") as 'year',1+1, sum(profit)/count(*) from t1 group by 1,2 with rollup;
56
 
select product, sum(profit)/count(*) from t1 group by product with rollup;
57
 
select left(product,4) as prod, sum(profit)/count(*) from t1 group by prod with rollup;
58
 
select concat(product,':',country_id), 1+1, sum(profit)/count(*) from t1 group by concat(product,':',country_id) with rollup;
59
 
 
60
 
# Joins
61
 
select product, country , year, sum(profit) from t1,t2 where t1.country_id=t2.country_id group by product, country, year with rollup;
62
 
 
63
 
# Derived tables and sub selects
64
 
select product, `sum` from (select product, sum(profit) as 'sum' from t1 group by product with rollup) as tmp where product is null;
65
 
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);
66
 
 
67
 
# The following doesn't return the expected answer, but this is a limitation
68
 
# in the implementation so we should just document it
69
 
select product, country_id , year, sum(profit) from t1 group by product, country_id, year having country_id is NULL;
70
 
select concat(':',product,':'), sum(profit),avg(profit) from t1 group by product with rollup;
71
 
 
72
 
# Error handling
73
 
 
74
 
# Cube is not yet implemented
75
 
--error ER_PARSE_ERROR
76
 
select product, country_id , year, sum(profit) from t1 group by product, country_id, year with cube;
77
 
--error ER_PARSE_ERROR
78
 
explain select product, country_id , year, sum(profit) from t1 group by product, country_id, year with cube;
79
 
--error ER_PARSE_ERROR
80
 
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;
81
 
 
82
 
drop table t1,t2;
83
 
 
84
 
#
85
 
# Test bug with const tables
86
 
#
87
 
 
88
 
CREATE TABLE t1 (i int);
89
 
INSERT INTO t1 VALUES(100);
90
 
CREATE TABLE t2 (i int);
91
 
INSERT INTO t2 VALUES (100),(200);
92
 
SELECT i, COUNT(*) FROM t1 GROUP BY i WITH ROLLUP;
93
 
SELECT t1.i, t2.i, COUNT(*) FROM t1 CROSS JOIN t2 GROUP BY t1.i,t2.i WITH ROLLUP;
94
 
drop table t1,t2;
95
 
 
96
 
#bug #4767: ROLLUP with LEFT JOIN 
97
 
 
98
 
CREATE TABLE user_day(
99
 
  user_id INT NOT NULL,
100
 
  date DATE NOT NULL,
101
 
  UNIQUE INDEX user_date (user_id, date)
102
 
);
103
 
 
104
 
INSERT INTO user_day VALUES
105
 
  (1, '2004-06-06' ),
106
 
  (1, '2004-06-07' ),
107
 
  (2, '2004-06-06' );
108
 
 
109
 
SELECT
110
 
       d.date AS day,
111
 
       COUNT(d.user_id) as sample,
112
 
       COUNT(next_day.user_id) AS not_cancelled
113
 
  FROM user_day d
114
 
       LEFT JOIN user_day next_day 
115
 
       ON next_day.user_id=d.user_id AND 
116
 
          next_day.date= DATE_ADD( d.date, interval 1 day )
117
 
  GROUP BY day;
118
 
 
119
 
SELECT
120
 
       d.date AS day,
121
 
       COUNT(d.user_id) as sample,
122
 
       COUNT(next_day.user_id) AS not_cancelled
123
 
  FROM user_day d
124
 
       LEFT JOIN user_day next_day 
125
 
       ON next_day.user_id=d.user_id AND 
126
 
          next_day.date= DATE_ADD( d.date, interval 1 day )
127
 
  GROUP BY day
128
 
    WITH ROLLUP;
129
 
 
130
 
DROP TABLE user_day;
131
 
 
132
 
#
133
 
# Tests for bugs #8616, #8615: distinct sum with rollup
134
 
#
135
 
 
136
 
CREATE TABLE t1 (a int, b int);
137
 
 
138
 
INSERT INTO t1 VALUES
139
 
  (1,4),
140
 
  (2,2), (2,2),
141
 
  (4,1), (4,1), (4,1), (4,1),
142
 
  (2,1), (2,1);
143
 
 
144
 
SELECT SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
145
 
SELECT DISTINCT SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
146
 
 
147
 
SELECT SUM(b), COUNT(DISTINCT b) FROM t1 GROUP BY a WITH ROLLUP;
148
 
SELECT DISTINCT SUM(b), COUNT(DISTINCT b) FROM t1 GROUP BY a WITH ROLLUP;
149
 
 
150
 
SELECT SUM(b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
151
 
SELECT DISTINCT SUM(b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
152
 
 
153
 
SELECT SUM(b), COUNT(DISTINCT b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
154
 
SELECT DISTINCT SUM(b), COUNT(DISTINCT b), COUNT(*) FROM t1
155
 
  GROUP BY a WITH ROLLUP;
156
 
 
157
 
SELECT a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;
158
 
SELECT DISTINCT a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;
159
 
 
160
 
SELECT b, a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;
161
 
SELECT DISTINCT b,a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;
162
 
 
163
 
ALTER TABLE t1 ADD COLUMN c INT;
164
 
SELECT a,b,sum(c) FROM t1 GROUP BY a,b,c WITH ROLLUP;
165
 
SELECT distinct a,b,sum(c) FROM t1 GROUP BY a,b,c WITH ROLLUP;
166
 
 
167
 
DROP TABLE t1;
168
 
 
169
 
#
170
 
# Tests for bugs #8617: SQL_CACL_FOUND_ROWS with rollup and limit 
171
 
#
172
 
 
173
 
CREATE TABLE t1 (a int, b int);
174
 
 
175
 
INSERT INTO t1 VALUES
176
 
  (1,4),
177
 
  (2,2), (2,2),
178
 
  (4,1), (4,1), (4,1), (4,1),
179
 
  (2,1), (2,1);
180
 
 
181
 
SELECT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1;
182
 
SELECT SQL_CALC_FOUND_ROWS a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1;
183
 
 
184
 
DROP TABLE t1;
185
 
 
186
 
#
187
 
# Tests for bug #9681: ROLLUP in subquery for derived table wiht 
188
 
#                      a group by field declared as NOT NULL
189
 
#
190
 
 
191
 
CREATE TABLE t1 (a int NOT NULL);
192
 
INSERT INTO t1 VALUES (1),(2);
193
 
 
194
 
SELECT a, SUM(a) m FROM  t1 GROUP BY a WITH ROLLUP;
195
 
SELECT * FROM ( SELECT a, SUM(a) m FROM  t1 GROUP BY a WITH ROLLUP ) t2;
196
 
 
197
 
DROP TABLE t1;
198
 
set div_precision_increment= @sav_dpi;
199
 
 
200
 
#
201
 
# Tests for bug #7914: ROLLUP over expressions on temporary table
202
 
#
203
 
 
204
 
CREATE TABLE t1 (a int);
205
 
INSERT INTO t1 VALUES (1),(2);
206
 
 
207
 
SELECT a, SUM(a), SUM(a)+1 FROM (SELECT a FROM t1 UNION select 2) d 
208
 
  GROUP BY a;
209
 
SELECT a, SUM(a), SUM(a)+1 FROM (SELECT a FROM t1 UNION select 2) d 
210
 
  GROUP BY a WITH ROLLUP;
211
 
 
212
 
SELECT a, SUM(a), SUM(a)+1 FROM (SELECT 1 a UNION select 2) d 
213
 
  GROUP BY a;
214
 
SELECT a, SUM(a), SUM(a)+1 FROM (SELECT 1 a UNION select 2) d 
215
 
  GROUP BY a WITH ROLLUP;
216
 
 
217
 
SELECT a, SUM(a), SUM(a)+1, CONCAT(SUM(a),'x'), SUM(a)+SUM(a), SUM(a)
218
 
  FROM (SELECT 1 a, 2 b UNION SELECT 2,3 UNION SELECT 5,6 ) d
219
 
    GROUP BY a WITH ROLLUP;
220
 
 
221
 
DROP TABLE t1;
222
 
 
223
 
#
224
 
# Tests for bug #7894: ROLLUP over expressions on group by attributes
225
 
#
226
 
 
227
 
CREATE TABLE t1 (a int);
228
 
INSERT INTO t1 VALUES (1),(2);
229
 
 
230
 
SELECT a, a+1, SUM(a) FROM t1 GROUP BY a WITH ROLLUP;
231
 
SELECT a+1 FROM t1 GROUP BY a WITH ROLLUP;
232
 
SELECT a+SUM(a) FROM t1 GROUP BY a WITH ROLLUP;
233
 
SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING b > 2;
234
 
SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING a IS NULL;
235
 
SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING b IS NULL;
236
 
SELECT IFNULL(a, 'TEST') FROM t1 GROUP BY a WITH ROLLUP;
237
 
 
238
 
CREATE TABLE t2 (a int, b int);
239
 
INSERT INTO t2 VALUES
240
 
  (1,4),
241
 
  (2,2), (2,2),
242
 
  (4,1), (4,1), (4,1), (4,1),
243
 
  (2,1), (2,1);
244
 
 
245
 
SELECT a,b,SUM(b) FROM t2 GROUP BY a,b WITH ROLLUP; 
246
 
SELECT a,b,SUM(b), a+b as c FROM t2
247
 
  GROUP BY a,b WITH ROLLUP HAVING c IS NULL;
248
 
SELECT IFNULL(a, 'TEST'), COALESCE(b, 'TEST') FROM t2 
249
 
  GROUP BY a, b WITH ROLLUP; 
250
 
 
251
 
DROP TABLE t1,t2;
252
 
 
253
 
#
254
 
# Test for bug #11543: ROLLUP query with a repeated column in GROUP BY 
255
 
#
256
 
 
257
 
CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL);
258
 
INSERT INTO t1 VALUES (1, 1);
259
 
INSERT INTO t1 VALUES (1, 2);
260
 
 
261
 
SELECT a, b, a AS c, COUNT(*) AS count FROM t1 GROUP BY a, b, c WITH ROLLUP;
262
 
 
263
 
DROP TABLE t1;
264
 
 
265
 
# Bug #12885(1): derived table specified by a subquery with
266
 
#                ROLLUP over expressions on not nullable group by attributes 
267
 
#
268
 
 
269
 
CREATE TABLE t1 (a int NOT NULL);
270
 
INSERT INTO t1 VALUES (1),(2);
271
 
 
272
 
SELECT * FROM (SELECT a, a + 1, COUNT(*) FROM t1 GROUP BY a WITH ROLLUP) t;
273
 
SELECT * FROM (SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP) t;
274
 
 
275
 
DROP TABLE t1;
276
 
 
277
 
#
278
 
# Bug #12887 Distinct is not always applied after rollup
279
 
#
280
 
create table t1 ( a varchar(9), b int );
281
 
insert into t1 values('a',1),(null,2);
282
 
select a, max(b) from t1 group by a with rollup;
283
 
select distinct a, max(b) from t1 group by a with rollup;
284
 
drop table t1;
285
 
 
286
 
#
287
 
# Bug #20825: rollup puts non-equal values together
288
 
#
289
 
create table t1 (a varchar(22) not null , b int);
290
 
insert into t1 values ("2006-07-01 21:30", 1), ("2006-07-01 23:30", 10);
291
 
select left(a,10), a, sum(b) from t1 group by 1,2 with rollup;
292
 
select left(a,10) x, a, sum(b) from t1 group by x,a with rollup;
293
 
drop table t1;
294
 
 
295
 
#
296
 
# Bug #24856: ROLLUP by const item in a query with DISTINCT
297
 
#
298
 
 
299
 
CREATE TABLE t1 (a int, b int);
300
 
INSERT INTO t1 
301
 
  VALUES (2,10),(3,30),(2,40),(1,10),(2,30),(1,20),(2,10);
302
 
 
303
 
SELECT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
304
 
SELECT DISTINCT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
305
 
SELECT a, b, COUNT(*) FROM t1 GROUP BY a,b WITH ROLLUP;
306
 
SELECT DISTINCT a, b, COUNT(*) FROM t1 GROUP BY a,b WITH ROLLUP;
307
 
 
308
 
SELECT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP;
309
 
SELECT DISTINCT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP;
310
 
SELECT DISTINCT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP;
311
 
 
312
 
DROP TABLE t1;
313
 
 
314
 
# End of 4.1 tests
315
 
 
316
 
#
317
 
# Bug #26830: derived table with ROLLUP 
318
 
#
319
 
 
320
 
CREATE TABLE t1 (a int, KEY (a));
321
 
INSERT INTO t1 VALUES (3), (1), (4), (1), (3), (1), (1);
322
 
 
323
 
SELECT * FROM (SELECT a, SUM(a) FROM t1 GROUP BY a WITH ROLLUP) as t;
324
 
 
325
 
DROP TABLE t1;
326
 
 
327
 
--echo #
328
 
--echo # Bug#31095: Unexpected NULL constant caused server crash.
329
 
--echo #
330
 
create table t1(a int);
331
 
insert into t1 values (1),(2),(3);
332
 
select count(a) from t1 group by null with rollup;
333
 
drop table t1;
334
 
--echo ##############################################################
335
 
 
336
 
#
337
 
# Bug #32558: group by null-returning expression with rollup causes crash
338
 
#
339
 
CREATE TABLE t1(a INT);
340
 
INSERT INTO t1 VALUES(0);
341
 
SELECT 1 FROM t1 GROUP BY (DATE(NULL)) WITH ROLLUP;
342
 
DROP TABLE t1;
343
 
 
344
 
--echo End of 5.0 tests