diff options
Diffstat (limited to 'mysql-test/main/olap.test')
| -rw-r--r-- | mysql-test/main/olap.test | 449 |
1 files changed, 449 insertions, 0 deletions
diff --git a/mysql-test/main/olap.test b/mysql-test/main/olap.test new file mode 100644 index 00000000000..3da08581a87 --- /dev/null +++ b/mysql-test/main/olap.test @@ -0,0 +1,449 @@ +--disable_warnings +drop table if exists t1,t2; +--enable_warnings + +set @sav_dpi= @@div_precision_increment; +set div_precision_increment= 5; +show variables like 'div_precision_increment'; + +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; + +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; + +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; +set div_precision_increment= @sav_dpi; + +# +# 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 #12885(1): 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; + +# +# 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; + +# +# Bug #24856: ROLLUP by const item in a query with DISTINCT +# + +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; + +# End of 4.1 tests + +# +# 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; + +# +# 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); + +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; + +# +# 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; + +--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 ############################################################## + +# +# 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 # +--echo # Bug #48131: crash group by with rollup, distinct, +--echo # filesort, with temporary tables +--echo # + +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY); +INSERT INTO t1 VALUES (1), (2); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (100); + +SELECT a, b FROM t1, t2 GROUP BY a, b WITH ROLLUP; +SELECT DISTINCT b FROM t1, t2 GROUP BY a, b WITH ROLLUP; + +DROP TABLE t1, t2; + +--echo # +--echo # Bug #48475: DISTINCT is ignored with GROUP BY WITH ROLLUP +--echo # and only const tables + +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (b INT); +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (1); + +SELECT DISTINCT b FROM t1, t2 GROUP BY a, b WITH ROLLUP; + +DROP TABLE t1, t2; + +--echo End of 5.0 tests + +--echo # +--echo # Start of 10.3 tests +--echo # + +--echo # +--echo # MDEV-12886 Different default for INT and BIGINT column in a VIEW for a SELECT with ROLLUP +--echo # + +CREATE TABLE t1 (a int(11) NOT NULL); +INSERT INTO t1 VALUES (1),(2); +CREATE OR REPLACE VIEW v1 AS SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP; +DESCRIBE v1; +DROP VIEW v1; +DROP TABLE t1; + +CREATE TABLE t1 (a bigint(11) NOT NULL); +INSERT INTO t1 VALUES (1),(2); +CREATE OR REPLACE VIEW v1 AS SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP; +DESCRIBE v1; +DROP VIEW v1; +DROP TABLE t1; + +--echo # +--echo # MDEV-9410 VIEW over a ROLLUP query reports too large columns +--echo # + +CREATE TABLE t1 (a int(10) NOT NULL, b int(20) NOT NULL); +INSERT INTO t1 VALUES (1,1),(2,2); + +CREATE VIEW v1 AS SELECT a,b FROM t1; +DESC v1; +DROP VIEW v1; + +CREATE VIEW v1 AS SELECT a, b FROM t1 GROUP BY a,b; +DESC v1; +DROP VIEW v1; + +CREATE VIEW v1 AS SELECT a, b FROM t1 GROUP BY a,b WITH ROLLUP; +DESC v1; +DROP VIEW v1; + +DROP TABLE t1; |