← Back to branch summary

~ubuntu-branches/ubuntu/trusty/mysql-5.6/trusty

« back to all changes in this revision

Viewing changes to mysql-test/t/olap.test

  • Committer: Package Import Robot
  • Author(s): James Page
  • Date: 2014-02-12 11:54:27 UTC
  • Revision ID: package-import@ubuntu.com-20140212115427-oq6tfsqxl1wuwehi
Tags: upstream-5.6.15
Import upstream version 5.6.15

Show diffs side-by-side

added added

removed removed

Lines of Context:
mysql-test/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 1235
 
76
select product, country_id , year, sum(profit) from t1 group by product, country_id, year with cube;
 
77
--error 1235
 
78
explain select product, country_id , year, sum(profit) from t1 group by product, country_id, year with cube;
 
79
--error 1235
 
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,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
--error ER_WRONG_USAGE
 
146
SELECT DISTINCT SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
 
147
 
 
148
SELECT SUM(b), COUNT(DISTINCT b) FROM t1 GROUP BY a WITH ROLLUP;
 
149
--error ER_WRONG_USAGE
 
150
SELECT DISTINCT SUM(b), COUNT(DISTINCT b) FROM t1 GROUP BY a WITH ROLLUP;
 
151
 
 
152
SELECT SUM(b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
 
153
--error ER_WRONG_USAGE
 
154
SELECT DISTINCT SUM(b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
 
155
 
 
156
SELECT SUM(b), COUNT(DISTINCT b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
 
157
--error ER_WRONG_USAGE
 
158
SELECT DISTINCT SUM(b), COUNT(DISTINCT b), COUNT(*) FROM t1
 
159
  GROUP BY a WITH ROLLUP;
 
160
 
 
161
SELECT a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;
 
162
--error ER_WRONG_USAGE
 
163
SELECT DISTINCT a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;
 
164
 
 
165
SELECT b, a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;
 
166
--error ER_WRONG_USAGE
 
167
SELECT DISTINCT b,a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;
 
168
 
 
169
ALTER TABLE t1 ADD COLUMN c INT;
 
170
SELECT a,b,sum(c) FROM t1 GROUP BY a,b,c WITH ROLLUP;
 
171
--error ER_WRONG_USAGE
 
172
SELECT distinct a,b,sum(c) FROM t1 GROUP BY a,b,c WITH ROLLUP;
 
173
 
 
174
DROP TABLE t1;
 
175
 
 
176
#
 
177
# Tests for bugs #8617: SQL_CACL_FOUND_ROWS with rollup and limit 
 
178
#
 
179
 
 
180
CREATE TABLE t1 (a int, b int);
 
181
 
 
182
INSERT INTO t1 VALUES
 
183
  (1,4),
 
184
  (2,2), (2,2),
 
185
  (4,1), (4,1), (4,1), (4,1),
 
186
  (2,1), (2,1);
 
187
 
 
188
SELECT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1;
 
189
SELECT SQL_CALC_FOUND_ROWS a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1;
 
190
 
 
191
DROP TABLE t1;
 
192
 
 
193
#
 
194
# Tests for bug #9681: ROLLUP in subquery for derived table wiht 
 
195
#                      a group by field declared as NOT NULL
 
196
#
 
197
 
 
198
CREATE TABLE t1 (a int(11) NOT NULL);
 
199
INSERT INTO t1 VALUES (1),(2);
 
200
 
 
201
SELECT a, SUM(a) m FROM  t1 GROUP BY a WITH ROLLUP;
 
202
SELECT * FROM ( SELECT a, SUM(a) m FROM  t1 GROUP BY a WITH ROLLUP ) t2;
 
203
 
 
204
DROP TABLE t1;
 
205
set div_precision_increment= @sav_dpi;
 
206
 
 
207
#
 
208
# Tests for bug #7914: ROLLUP over expressions on temporary table
 
209
#
 
210
 
 
211
CREATE TABLE t1 (a int(11));
 
212
INSERT INTO t1 VALUES (1),(2);
 
213
 
 
214
SELECT a, SUM(a), SUM(a)+1 FROM (SELECT a FROM t1 UNION select 2) d 
 
215
  GROUP BY a;
 
216
SELECT a, SUM(a), SUM(a)+1 FROM (SELECT a FROM t1 UNION select 2) d 
 
217
  GROUP BY a WITH ROLLUP;
 
218
 
 
219
SELECT a, SUM(a), SUM(a)+1 FROM (SELECT 1 a UNION select 2) d 
 
220
  GROUP BY a;
 
221
SELECT a, SUM(a), SUM(a)+1 FROM (SELECT 1 a UNION select 2) d 
 
222
  GROUP BY a WITH ROLLUP;
 
223
 
 
224
SELECT a, SUM(a), SUM(a)+1, CONCAT(SUM(a),'x'), SUM(a)+SUM(a), SUM(a)
 
225
  FROM (SELECT 1 a, 2 b UNION SELECT 2,3 UNION SELECT 5,6 ) d
 
226
    GROUP BY a WITH ROLLUP;
 
227
 
 
228
DROP TABLE t1;
 
229
 
 
230
#
 
231
# Tests for bug #7894: ROLLUP over expressions on group by attributes
 
232
#
 
233
 
 
234
CREATE TABLE t1 (a int(11));
 
235
INSERT INTO t1 VALUES (1),(2);
 
236
 
 
237
SELECT a, a+1, SUM(a) FROM t1 GROUP BY a WITH ROLLUP;
 
238
SELECT a+1 FROM t1 GROUP BY a WITH ROLLUP;
 
239
SELECT a+SUM(a) FROM t1 GROUP BY a WITH ROLLUP;
 
240
SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING b > 2;
 
241
SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING a IS NULL;
 
242
SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING b IS NULL;
 
243
SELECT IFNULL(a, 'TEST') FROM t1 GROUP BY a WITH ROLLUP;
 
244
 
 
245
CREATE TABLE t2 (a int, b int);
 
246
INSERT INTO t2 VALUES
 
247
  (1,4),
 
248
  (2,2), (2,2),
 
249
  (4,1), (4,1), (4,1), (4,1),
 
250
  (2,1), (2,1);
 
251
 
 
252
SELECT a,b,SUM(b) FROM t2 GROUP BY a,b WITH ROLLUP; 
 
253
SELECT a,b,SUM(b), a+b as c FROM t2
 
254
  GROUP BY a,b WITH ROLLUP HAVING c IS NULL;
 
255
SELECT IFNULL(a, 'TEST'), COALESCE(b, 'TEST') FROM t2 
 
256
  GROUP BY a, b WITH ROLLUP; 
 
257
 
 
258
DROP TABLE t1,t2;
 
259
 
 
260
#
 
261
# Test for bug #11543: ROLLUP query with a repeated column in GROUP BY 
 
262
#
 
263
 
 
264
CREATE TABLE t1 (a INT(10) NOT NULL, b INT(10) NOT NULL);
 
265
INSERT INTO t1 VALUES (1, 1);
 
266
INSERT INTO t1 VALUES (1, 2);
 
267
 
 
268
SELECT a, b, a AS c, COUNT(*) AS count FROM t1 GROUP BY a, b, c WITH ROLLUP;
 
269
 
 
270
DROP TABLE t1;
 
271
 
 
272
# Bug #12885(1): derived table specified by a subquery with
 
273
#                ROLLUP over expressions on not nullable group by attributes 
 
274
#
 
275
 
 
276
CREATE TABLE t1 (a int(11) NOT NULL);
 
277
INSERT INTO t1 VALUES (1),(2);
 
278
 
 
279
SELECT * FROM (SELECT a, a + 1, COUNT(*) FROM t1 GROUP BY a WITH ROLLUP) t;
 
280
SELECT * FROM (SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP) t;
 
281
 
 
282
DROP TABLE t1;
 
283
 
 
284
#
 
285
# Bug #12887 Distinct is not always applied after rollup
 
286
#
 
287
create table t1 ( a varchar(9), b int );
 
288
insert into t1 values('a',1),(null,2);
 
289
select a, max(b) from t1 group by a with rollup;
 
290
--error ER_WRONG_USAGE
 
291
select distinct a, max(b) from t1 group by a with rollup;
 
292
drop table t1;
 
293
 
 
294
#
 
295
# Bug #20825: rollup puts non-equal values together
 
296
#
 
297
create table t1 (a varchar(22) not null , b int);
 
298
insert into t1 values ("2006-07-01 21:30", 1), ("2006-07-01 23:30", 10);
 
299
select left(a,10), a, sum(b) from t1 group by 1,2 with rollup;
 
300
select left(a,10) x, a, sum(b) from t1 group by x,a with rollup;
 
301
drop table t1;
 
302
 
 
303
#
 
304
# Bug #24856: ROLLUP by const item in a query with DISTINCT
 
305
#
 
306
 
 
307
CREATE TABLE t1 (a int, b int);
 
308
INSERT INTO t1 
 
309
  VALUES (2,10),(3,30),(2,40),(1,10),(2,30),(1,20),(2,10);
 
310
 
 
311
SELECT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
 
312
--error ER_WRONG_USAGE
 
313
SELECT DISTINCT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
 
314
SELECT a, b, COUNT(*) FROM t1 GROUP BY a,b WITH ROLLUP;
 
315
--error ER_WRONG_USAGE
 
316
SELECT DISTINCT a, b, COUNT(*) FROM t1 GROUP BY a,b WITH ROLLUP;
 
317
 
 
318
SELECT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP;
 
319
--error ER_WRONG_USAGE
 
320
SELECT DISTINCT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP;
 
321
--error ER_WRONG_USAGE
 
322
SELECT DISTINCT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP;
 
323
 
 
324
DROP TABLE t1;
 
325
 
 
326
# End of 4.1 tests
 
327
 
 
328
#
 
329
# Tests for bug #11639: ROLLUP over view executed through filesort
 
330
#
 
331
 
 
332
CREATE TABLE t1(id int, type char(1));
 
333
INSERT INTO t1 VALUES
 
334
  (1,"A"),(2,"C"),(3,"A"),(4,"A"),(5,"B"),
 
335
  (6,"B"),(7,"A"),(8,"C"),(9,"A"),(10,"C");
 
336
CREATE VIEW v1 AS SELECT * FROM t1;
 
337
 
 
338
SELECT type FROM t1 GROUP BY type WITH ROLLUP;
 
339
SELECT type FROM v1 GROUP BY type WITH ROLLUP;
 
340
EXPLAIN SELECT type FROM v1 GROUP BY type WITH ROLLUP;
 
341
 
 
342
DROP VIEW v1;
 
343
DROP TABLE t1;
 
344
 
 
345
#
 
346
# Bug #12885(2): view specified by a subquery with
 
347
#                ROLLUP over expressions on not nullable group by attributes 
 
348
#
 
349
 
 
350
CREATE TABLE t1 (a int(11) NOT NULL);
 
351
INSERT INTO t1 VALUES (1),(2);
 
352
 
 
353
CREATE VIEW v1 AS
 
354
  SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
 
355
 
 
356
DESC v1;
 
357
SELECT * FROM v1;
 
358
 
 
359
DROP VIEW v1;
 
360
DROP TABLE t1;
 
361
 
 
362
#
 
363
# Bug #26830: derived table with ROLLUP 
 
364
#
 
365
 
 
366
CREATE TABLE t1 (a int, KEY (a));
 
367
INSERT INTO t1 VALUES (3), (1), (4), (1), (3), (1), (1);
 
368
 
 
369
SELECT * FROM (SELECT a, SUM(a) FROM t1 GROUP BY a WITH ROLLUP) as t;
 
370
 
 
371
DROP TABLE t1;
 
372
 
 
373
--echo #
 
374
--echo # Bug#31095: Unexpected NULL constant caused server crash.
 
375
--echo #
 
376
create table t1(a int);
 
377
insert into t1 values (1),(2),(3);
 
378
select count(a) from t1 group by null with rollup;
 
379
drop table t1;
 
380
--echo ##############################################################
 
381
 
 
382
#
 
383
# Bug #32558: group by null-returning expression with rollup causes crash
 
384
#
 
385
CREATE TABLE t1(a INT);
 
386
INSERT INTO t1 VALUES(0);
 
387
SELECT 1 FROM t1 GROUP BY (DATE(NULL)) WITH ROLLUP;
 
388
DROP TABLE t1;
 
389
 
 
390
--echo #
 
391
--echo # Bug #48131: crash group by with rollup, distinct,
 
392
--echo #             filesort, with temporary tables
 
393
--echo #
 
394
 
 
395
CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY);
 
396
INSERT INTO t1 VALUES (1), (2);
 
397
CREATE TABLE t2 (b INT);
 
398
INSERT INTO t2 VALUES (100);
 
399
 
 
400
SELECT a, b FROM t1, t2 GROUP BY a, b WITH ROLLUP;
 
401
--error ER_WRONG_USAGE
 
402
SELECT DISTINCT b FROM t1, t2 GROUP BY a, b WITH ROLLUP;
 
403
 
 
404
DROP TABLE t1, t2;
 
405
 
 
406
--echo #
 
407
--echo # Bug #48475: DISTINCT is ignored with GROUP BY WITH ROLLUP
 
408
--echo #             and only const tables
 
409
 
 
410
CREATE TABLE t1 (a INT);
 
411
CREATE TABLE t2 (b INT);
 
412
INSERT INTO t1 VALUES (1);
 
413
INSERT INTO t2 VALUES (1);
 
414
 
 
415
SELECT b FROM t1, t2 GROUP BY a, b WITH ROLLUP;
 
416
--error ER_WRONG_USAGE
 
417
SELECT DISTINCT b FROM t1, t2 GROUP BY a, b WITH ROLLUP;
 
418
 
 
419
DROP TABLE t1, t2;
 
420
 
 
421
--echo End of 5.0 tests
 
422
 
 
423
--echo #
 
424
--echo # Bug#13011553 CRASH IN SORTCMP OR CACHED_ITEM_STR::CMP IF GROUP BY SUBQUERY WITH ROLLUP
 
425
--echo #
 
426
 
 
427
CREATE TABLE t1 (f1 DATETIME) ENGINE = MyISAM;
 
428
INSERT INTO  t1 VALUES ('2012-12-20 00:00:00'), (NULL);
 
429
 
 
430
SELECT f1 FROM t1 GROUP BY
 
431
(SELECT f1 FROM t1 HAVING f1 < '2012-12-21 00:00:00') WITH ROLLUP;
 
432
 
 
433
DROP TABLE t1;
 
434
 
 
435
CREATE TABLE t1 (f1 DATE) ENGINE = MyISAM;
 
436
INSERT INTO  t1 VALUES ('2012-12-20'), (NULL);
 
437
 
 
438
SELECT f1 FROM t1 GROUP BY
 
439
(SELECT f1 FROM t1 HAVING f1 < '2012-12-21') WITH ROLLUP;
 
440
 
 
441
DROP TABLE t1;
 
442
 
 
443
CREATE TABLE t1 (f1 TIME) ENGINE = MyISAM;
 
444
INSERT INTO  t1 VALUES ('11:11:11'), (NULL);
 
445
 
 
446
SELECT f1 FROM t1 GROUP BY
 
447
(SELECT f1 FROM t1 HAVING f1 < '12:12:12') WITH ROLLUP;
 
448
 
 
449
DROP TABLE t1;
 
450
 
 
451
--echo End of 5.5 tests