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; #check after fix MDEV-29601 --disable_service_connection select concat(product,':',country_id), 1+1, sum(profit)/count(*) from t1 group by concat(product,':',country_id) with rollup; --enable_service_connection # 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; #enable view protocol after fix MDEV-28535 --disable_view_protocol 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; --enable_view_protocol 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 # #enable view protocol after fix MDEV-28536 --disable_view_protocol 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; --enable_view_protocol # 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 # End of 10.0 tests --echo # --echo # --echo # MDEV-16190 Server crashes in Item_null_result::field_type on SELECT with time field, ROLLUP and HAVING --echo # CREATE TABLE t1 (t TIME) ENGINE=MyISAM; INSERT INTO t1 VALUES ('12:12:12'); SELECT t, COUNT(*) FROM t1 GROUP BY t WITH ROLLUP HAVING t > '00:00:00'; DROP TABLE t1; CREATE TABLE t1 (t TIME) ENGINE=MyISAM; INSERT INTO t1 VALUES ('12:12:12'),('12:12:13'); SELECT t, COUNT(*) FROM t1 GROUP BY t WITH ROLLUP HAVING t > '00:00:00'; DROP TABLE t1; --echo # --echo # MDEV-17830 Server crashes in Item_null_result::field_type upon SELECT with CHARSET(date) and ROLLUP --echo # --echo # Note, different MariaDB versions can return different results --echo # in the two rows (such as "latin1" vs "binary"). This is wrong. --echo # Both lines should return equal values. --echo # The point in this test is to make sure it does not crash. --echo # As this is a minor issue, bad result will be fixed --echo # in a later version, presumably in 10.4. CREATE TABLE t (d DATE) ENGINE=MyISAM; INSERT INTO t VALUES ('2018-12-12'); SELECT CHARSET(d) AS f FROM t GROUP BY d WITH ROLLUP; DROP TABLE t; --echo # --echo # MDEV-14041 Server crashes in String::length on queries with functions and ROLLUP --echo # --disable_ps2_protocol #enable view protocol after fix MDEV-28538 --disable_view_protocol CREATE TABLE t1 (i INT); INSERT INTO t1 VALUES (1),(2); SELECT GET_LOCK( 'foo', 0 ); SELECT HEX( RELEASE_LOCK( 'foo' ) ) AS f FROM t1 GROUP BY f WITH ROLLUP; DROP TABLE t1; --enable_view_protocol --enable_ps2_protocol CREATE TABLE t1 (i INT); INSERT INTO t1 VALUES (1),(2); SELECT i FROM t1 GROUP BY i WITH ROLLUP UNION ALL SELECT ELT( FOUND_ROWS(), 1 ) f FROM t1 GROUP BY f WITH ROLLUP; DROP TABLE t1; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2); SELECT a FROM t1 GROUP BY NULLIF( CONVERT('', DATE), '2015-10-15' ) WITH ROLLUP; DROP TABLE t1; --echo # --echo # End of 10.1 tests --echo # --echo # --echo # End of 10.2 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; --echo # --echo # MDEV-15576: Server crashed in Cached_item_str::cmp / sortcmp or --echo # Assertion `item->null_value' failed in --echo # Type_handler_temporal_result::make_sort_key upon SELECT with NULLIF --echo # and ROLLUP --echo # CREATE TABLE t1 (i INT); INSERT INTO t1 VALUES (1),(2); --disable_warnings SELECT NULLIF( CAST( 'foo' AS DATE ), NULL & 'bar' ) AS f FROM t1 GROUP BY f WITH ROLLUP; --enable_warnings DROP TABLE t1; --echo # End of 10.3 Tests