olap.test 11.9 KB
Newer Older
1
--disable_warnings
2
drop table if exists t1,t2;
3 4
--enable_warnings

unknown's avatar
unknown committed
5 6 7 8
set @sav_dpi= @@div_precision_increment;
set div_precision_increment= 5;
show variables like 'div_precision_increment';

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
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;
39
explain extended select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup;
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
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
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 81 82
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;
83

84 85 86 87 88 89 90 91 92 93 94
#
# 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;
unknown's avatar
unknown committed
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

#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;

unknown's avatar
unknown committed
132
#
unknown's avatar
unknown committed
133
# Tests for bugs #8616, #8615: distinct sum with rollup
unknown's avatar
unknown committed
134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156
#

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;

unknown's avatar
unknown committed
157 158 159
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;

unknown's avatar
unknown committed
160 161 162 163 164 165 166
SELECT b, a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;
SELECT DISTINCT b,a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;

ALTER TABLE t1 ADD COLUMN c INT;
SELECT a,b,sum(c) FROM t1 GROUP BY a,b,c WITH ROLLUP;
SELECT distinct a,b,sum(c) FROM t1 GROUP BY a,b,c WITH ROLLUP;

unknown's avatar
unknown committed
167 168
DROP TABLE t1;

unknown's avatar
unknown committed
169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184
#
# 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;
unknown's avatar
unknown committed
185 186 187 188 189 190 191 192 193 194 195 196 197

#
# Tests for bug #9681: ROLLUP in subquery for derived table wiht 
#                      a group by field declared as NOT NULL
#

CREATE TABLE t1 (a int(11) NOT NULL);
INSERT INTO t1 VALUES (1),(2);

SELECT a, SUM(a) m FROM  t1 GROUP BY a WITH ROLLUP;
SELECT * FROM ( SELECT a, SUM(a) m FROM  t1 GROUP BY a WITH ROLLUP ) t2;

DROP TABLE t1;
unknown's avatar
unknown committed
198
set div_precision_increment= @sav_dpi;
unknown's avatar
unknown committed
199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222

#
# Tests for bug #7914: ROLLUP over expressions on temporary table
#

CREATE TABLE t1 (a int(11));
INSERT INTO t1 VALUES (1),(2);

SELECT a, SUM(a), SUM(a)+1 FROM (SELECT a FROM t1 UNION select 2) d 
  GROUP BY a;
SELECT a, SUM(a), SUM(a)+1 FROM (SELECT a FROM t1 UNION select 2) d 
  GROUP BY a WITH ROLLUP;

SELECT a, SUM(a), SUM(a)+1 FROM (SELECT 1 a UNION select 2) d 
  GROUP BY a;
SELECT a, SUM(a), SUM(a)+1 FROM (SELECT 1 a UNION select 2) d 
  GROUP BY a WITH ROLLUP;

SELECT a, SUM(a), SUM(a)+1, CONCAT(SUM(a),'x'), SUM(a)+SUM(a), SUM(a)
  FROM (SELECT 1 a, 2 b UNION SELECT 2,3 UNION SELECT 5,6 ) d
    GROUP BY a WITH ROLLUP;

DROP TABLE t1;

unknown's avatar
unknown committed
223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252
#
# Tests for bug #7894: ROLLUP over expressions on group by attributes
#

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;
SELECT a+1 FROM t1 GROUP BY a WITH ROLLUP;
SELECT a+SUM(a) FROM t1 GROUP BY a WITH ROLLUP;
SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING b > 2;
SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING a IS NULL;
SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING b IS NULL;
SELECT IFNULL(a, 'TEST') FROM t1 GROUP BY a WITH ROLLUP;

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; 
SELECT a,b,SUM(b), a+b as c FROM t2
  GROUP BY a,b WITH ROLLUP HAVING c IS NULL;
SELECT IFNULL(a, 'TEST'), COALESCE(b, 'TEST') FROM t2 
  GROUP BY a, b WITH ROLLUP; 

DROP TABLE t1,t2;

unknown's avatar
unknown committed
253 254 255 256 257 258 259 260 261 262 263
#
# Test for bug #11543: ROLLUP query with a repeated column in GROUP BY 
#

CREATE TABLE t1 (a INT(10) NOT NULL, b INT(10) NOT NULL);
INSERT INTO t1 VALUES (1, 1);
INSERT INTO t1 VALUES (1, 2);

SELECT a, b, a AS c, COUNT(*) AS count FROM t1 GROUP BY a, b, c WITH ROLLUP;

DROP TABLE t1;
264

unknown's avatar
unknown committed
265 266
# Bug #12885(1): derived table specified by a subquery with
#                ROLLUP over expressions on not nullable group by attributes 
unknown's avatar
unknown committed
267 268 269 270 271 272 273 274 275 276
#

CREATE TABLE t1 (a int(11) NOT NULL);
INSERT INTO t1 VALUES (1),(2);

SELECT * FROM (SELECT a, a + 1, COUNT(*) FROM t1 GROUP BY a WITH ROLLUP) t;
SELECT * FROM (SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP) t;

DROP TABLE t1;

277 278 279 280 281 282 283 284 285
#
# Bug #12887 Distinct is not always applied after rollup
#
create table t1 ( a varchar(9), b int );
insert into t1 values('a',1),(null,2);
select a, max(b) from t1 group by a with rollup;
select distinct a, max(b) from t1 group by a with rollup;
drop table t1;

286 287 288 289 290 291 292 293 294
#
# Bug #20825: rollup puts non-equal values together
#
create table t1 (a varchar(22) not null , b int);
insert into t1 values ("2006-07-01 21:30", 1), ("2006-07-01 23:30", 10);
select left(a,10), a, sum(b) from t1 group by 1,2 with rollup;
select left(a,10) x, a, sum(b) from t1 group by x,a with rollup;
drop table t1;

295
#
unknown's avatar
unknown committed
296
# Bug #24856: ROLLUP by const item in a query with DISTINCT
297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313
#

CREATE TABLE t1 (a int, b int);
INSERT INTO t1 
  VALUES (2,10),(3,30),(2,40),(1,10),(2,30),(1,20),(2,10);

SELECT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
SELECT DISTINCT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
SELECT a, b, COUNT(*) FROM t1 GROUP BY a,b WITH ROLLUP;
SELECT DISTINCT a, b, COUNT(*) FROM t1 GROUP BY a,b WITH ROLLUP;

SELECT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP;
SELECT DISTINCT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP;
SELECT DISTINCT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP;

DROP TABLE t1;

314
# End of 4.1 tests
unknown's avatar
unknown committed
315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332

#
# Tests for bug #11639: ROLLUP over view executed through filesort
#

CREATE TABLE t1(id int, type char(1));
INSERT INTO t1 VALUES
  (1,"A"),(2,"C"),(3,"A"),(4,"A"),(5,"B"),
  (6,"B"),(7,"A"),(8,"C"),(9,"A"),(10,"C");
CREATE VIEW v1 AS SELECT * FROM t1;

SELECT type FROM t1 GROUP BY type WITH ROLLUP;
SELECT type FROM v1 GROUP BY type WITH ROLLUP;
EXPLAIN SELECT type FROM v1 GROUP BY type WITH ROLLUP;

DROP VIEW v1;
DROP TABLE t1;

unknown's avatar
unknown committed
333 334 335 336 337 338 339
#
# Bug #12885(2): view specified by a subquery with
#                ROLLUP over expressions on not nullable group by attributes 
#

CREATE TABLE t1 (a int(11) NOT NULL);
INSERT INTO t1 VALUES (1),(2);
unknown's avatar
unknown committed
340

unknown's avatar
unknown committed
341 342 343 344 345 346 347 348
CREATE VIEW v1 AS
  SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;

DESC v1;
SELECT * FROM v1;

DROP VIEW v1;
DROP TABLE t1;
349 350 351 352 353 354 355 356 357 358 359 360

#
# Bug #26830: derived table with ROLLUP 
#

CREATE TABLE t1 (a int, KEY (a));
INSERT INTO t1 VALUES (3), (1), (4), (1), (3), (1), (1);

SELECT * FROM (SELECT a, SUM(a) FROM t1 GROUP BY a WITH ROLLUP) as t;

DROP TABLE t1;

361 362 363 364 365 366 367 368 369
--echo #
--echo # Bug#31095: Unexpected NULL constant caused server crash.
--echo #
create table t1(a int);
insert into t1 values (1),(2),(3);
select count(a) from t1 group by null with rollup;
drop table t1;
--echo ##############################################################

370 371 372 373 374 375 376 377 378
#
# Bug #32558: group by null-returning expression with rollup causes crash
#
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES(0);
SELECT 1 FROM t1 GROUP BY (DATE(NULL)) WITH ROLLUP;
DROP TABLE t1;

--echo End of 5.0 tests