olap.test
上传用户:romrleung
上传日期:2022-05-23
资源大小:18897k
文件大小:9k
- --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;
- #
- # 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;
- #
- # 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;
- #
- # 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;
- #
- # 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;
- #
- # Bug #11885: derived table 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);
- 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;
- #
- # 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;
- # End of 4.1 tests