release/src/router/mysql/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 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(11) 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(11));
 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(11));
 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(10) NOT NULL, b INT(10) 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(11) 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 # Tests for bug #11639: ROLLUP over view executed through filesort
 318 #
 319
 320 CREATE TABLE t1(id int, type char(1));
 321 INSERT INTO t1 VALUES
 322   (1,"A"),(2,"C"),(3,"A"),(4,"A"),(5,"B"),
 323   (6,"B"),(7,"A"),(8,"C"),(9,"A"),(10,"C");
 324 CREATE VIEW v1 AS SELECT * FROM t1;
 325
 326 SELECT type FROM t1 GROUP BY type WITH ROLLUP;
 327 SELECT type FROM v1 GROUP BY type WITH ROLLUP;
 328 EXPLAIN SELECT type FROM v1 GROUP BY type WITH ROLLUP;
 329
 330 DROP VIEW v1;
 331 DROP TABLE t1;
 332
 333 #
 334 # Bug #12885(2): view specified by a subquery with
 335 #                ROLLUP over expressions on not nullable group by attributes
 336 #
 337
 338 CREATE TABLE t1 (a int(11) NOT NULL);
 339 INSERT INTO t1 VALUES (1),(2);
 340
 341 CREATE VIEW v1 AS
 342   SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
 343
 344 DESC v1;
 345 SELECT * FROM v1;
 346
 347 DROP VIEW v1;
 348 DROP TABLE t1;
 349
 350 #
 351 # Bug #26830: derived table with ROLLUP
 352 #
 353
 354 CREATE TABLE t1 (a int, KEY (a));
 355 INSERT INTO t1 VALUES (3), (1), (4), (1), (3), (1), (1);
 356
 357 SELECT * FROM (SELECT a, SUM(a) FROM t1 GROUP BY a WITH ROLLUP) as t;
 358
 359 DROP TABLE t1;
 360
 361 --echo #
 362 --echo # Bug#31095: Unexpected NULL constant caused server crash.
 363 --echo #
 364 create table t1(a int);
 365 insert into t1 values (1),(2),(3);
 366 select count(a) from t1 group by null with rollup;
 367 drop table t1;
 368 --echo ##############################################################
 369
 370 #
 371 # Bug #32558: group by null-returning expression with rollup causes crash
 372 #
 373 CREATE TABLE t1(a INT);
 374 INSERT INTO t1 VALUES(0);
 375 SELECT 1 FROM t1 GROUP BY (DATE(NULL)) WITH ROLLUP;
 376 DROP TABLE t1;
 377
 378 --echo #
 379 --echo # Bug #48131: crash group by with rollup, distinct,
 380 --echo #             filesort, with temporary tables
 381 --echo #
 382
 383 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY);
 384 INSERT INTO t1 VALUES (1), (2);
 385 CREATE TABLE t2 (b INT);
 386 INSERT INTO t2 VALUES (100);
 387
 388 SELECT a, b FROM t1, t2 GROUP BY a, b WITH ROLLUP;
 389 SELECT DISTINCT b FROM t1, t2 GROUP BY a, b WITH ROLLUP;
 390
 391 DROP TABLE t1, t2;
 392
 393 --echo #
 394 --echo # Bug #48475: DISTINCT is ignored with GROUP BY WITH ROLLUP
 395 --echo #             and only const tables
 396
 397 CREATE TABLE t1 (a INT);
 398 CREATE TABLE t2 (b INT);
 399 INSERT INTO t1 VALUES (1);
 400 INSERT INTO t2 VALUES (1);
 401
 402 SELECT DISTINCT b FROM t1, t2 GROUP BY a, b WITH ROLLUP;
 403
 404 DROP TABLE t1, t2;
 405
 406 --echo End of 5.0 tests