diff options
Diffstat (limited to 'mysql-test/t/olap.test')
| -rw-r--r-- | mysql-test/t/olap.test | 449 |
1 files changed, 0 insertions, 449 deletions
diff --git a/mysql-test/t/olap.test b/mysql-test/t/olap.test deleted file mode 100644 index 3da08581a87..00000000000 --- a/mysql-test/t/olap.test +++ /dev/null @@ -1,449 +0,0 @@ ---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; |