~drizzle-trunk/drizzle/development

--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 End of 5.0 tests