~drizzle-trunk/drizzle/development : contents of tests/r/olap.result at revision 1340.1.5

~drizzle-trunk/drizzle/development : (revision 1340.1.5)

drop table if exists t1,t2;
set @sav_dpi= @@div_precision_increment;
set div_precision_increment= 5;
show variables like 'div_precision_increment';
Variable_name	Value
div_precision_increment	5
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');
select product, sum(profit) from t1 group by product;
product	sum(profit)
Calculator	275
Computer	6900
Phone	10
TV	600
select product, sum(profit) from t1 group by product with rollup;
product	sum(profit)
Calculator	275
Computer	6900
Phone	10
TV	600
NULL	7785
select product, sum(profit) from t1 group by 1 with rollup;
product	sum(profit)
Calculator	275
Computer	6900
Phone	10
TV	600
NULL	7785
select product, sum(profit),avg(profit) from t1 group by product with rollup;
product	sum(profit)	avg(profit)
Calculator	275	68.75000
Computer	6900	1380.00000
Phone	10	10.00000
TV	600	120.00000
NULL	7785	519.00000
select product, country_id , year, sum(profit) from t1 group by product, country_id, year;
product	country_id	year	sum(profit)
Calculator	1	1999	50
Calculator	1	2000	75
Calculator	2	2000	150
Computer	1	1999	2700
Computer	1	2000	1500
Computer	2	2000	2700
Phone	3	2003	10
TV	1	1999	250
TV	1	2000	150
TV	2	2000	200
select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup;
product	country_id	year	sum(profit)
Calculator	1	1999	50
Calculator	1	2000	75
Calculator	1	NULL	125
Calculator	2	2000	150
Calculator	2	NULL	150
Calculator	NULL	NULL	275
Computer	1	1999	2700
Computer	1	2000	1500
Computer	1	NULL	4200
Computer	2	2000	2700
Computer	2	NULL	2700
Computer	NULL	NULL	6900
Phone	3	2003	10
Phone	3	NULL	10
Phone	NULL	NULL	10
TV	1	1999	250
TV	1	2000	150
TV	1	NULL	400
TV	2	2000	200
TV	2	NULL	200
TV	NULL	NULL	600
NULL	NULL	NULL	7785
explain extended select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	15	100.00	Using filesort
Warnings:
Note	1003	select `test`.`t1`.`product` AS `product`,`test`.`t1`.`country_id` AS `country_id`,`test`.`t1`.`year` AS `year`,sum(`test`.`t1`.`profit`) AS `sum(profit)` from `test`.`t1` group by `test`.`t1`.`product`,`test`.`t1`.`country_id`,`test`.`t1`.`year` with rollup
select product, country_id , sum(profit) from t1 group by product desc, country_id with rollup;
product	country_id	sum(profit)
TV	1	400
TV	2	200
TV	NULL	600
Phone	3	10
Phone	NULL	10
Computer	1	4200
Computer	2	2700
Computer	NULL	6900
Calculator	1	125
Calculator	2	150
Calculator	NULL	275
NULL	NULL	7785
select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup limit 5;
product	country_id	year	sum(profit)
Calculator	1	1999	50
Calculator	1	2000	75
Calculator	1	NULL	125
Calculator	2	2000	150
Calculator	2	NULL	150
select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup limit 3,3;
product	country_id	year	sum(profit)
Calculator	2	2000	150
Calculator	2	NULL	150
Calculator	NULL	NULL	275
select product, country_id, count(*), count(distinct year) from t1 group by product, country_id;
product	country_id	count(*)	count(distinct year)
Calculator	1	2	2
Calculator	2	2	1
Computer	1	3	2
Computer	2	2	1
Phone	3	1	1
TV	1	3	2
TV	2	2	1
select product, country_id, count(*), count(distinct year) from t1 group by product, country_id with rollup;
product	country_id	count(*)	count(distinct year)
Calculator	1	2	2
Calculator	2	2	1
Calculator	NULL	4	2
Computer	1	3	2
Computer	2	2	1
Computer	NULL	5	2
Phone	3	1	1
Phone	NULL	1	1
TV	1	3	2
TV	2	2	1
TV	NULL	5	2
NULL	NULL	15	3
select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup having country_id = 1;
product	country_id	year	sum(profit)
Calculator	1	1999	50
Calculator	1	2000	75
Calculator	1	NULL	125
Computer	1	1999	2700
Computer	1	2000	1500
Computer	1	NULL	4200
TV	1	1999	250
TV	1	2000	150
TV	1	NULL	400
select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup having sum(profit) > 200;
product	country_id	year	sum(profit)
Calculator	NULL	NULL	275
Computer	1	1999	2700
Computer	1	2000	1500
Computer	1	NULL	4200
Computer	2	2000	2700
Computer	2	NULL	2700
Computer	NULL	NULL	6900
TV	1	1999	250
TV	1	NULL	400
TV	NULL	NULL	600
NULL	NULL	NULL	7785
select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup having sum(profit) > 7000;
product	country_id	year	sum(profit)
NULL	NULL	NULL	7785
select concat(product,':',country_id) as 'prod', concat(":",year,":") as 'year',1+1, sum(profit)/count(*) from t1 group by 1,2 with rollup;
prod	year	1+1	sum(profit)/count(*)
Calculator:1	:1999:	2	50.00000
Calculator:1	:2000:	2	75.00000
Calculator:1	NULL	2	62.50000
Calculator:2	:2000:	2	75.00000
Calculator:2	NULL	2	75.00000
Computer:1	:1999:	2	1350.00000
Computer:1	:2000:	2	1500.00000
Computer:1	NULL	2	1400.00000
Computer:2	:2000:	2	1350.00000
Computer:2	NULL	2	1350.00000
Phone:3	:2003:	2	10.00000
Phone:3	NULL	2	10.00000
TV:1	:1999:	2	125.00000
TV:1	:2000:	2	150.00000
TV:1	NULL	2	133.33333
TV:2	:2000:	2	100.00000
TV:2	NULL	2	100.00000
NULL	NULL	2	519.00000
select product, sum(profit)/count(*) from t1 group by product with rollup;
product	sum(profit)/count(*)
Calculator	68.75000
Computer	1380.00000
Phone	10.00000
TV	120.00000
NULL	519.00000
select left(product,4) as prod, sum(profit)/count(*) from t1 group by prod with rollup;
prod	sum(profit)/count(*)
Calc	68.75000
Comp	1380.00000
Phon	10.00000
TV	120.00000
NULL	519.00000
select concat(product,':',country_id), 1+1, sum(profit)/count(*) from t1 group by concat(product,':',country_id) with rollup;
concat(product,':',country_id)	1+1	sum(profit)/count(*)
Calculator:1	2	62.50000
Calculator:2	2	75.00000
Computer:1	2	1400.00000
Computer:2	2	1350.00000
Phone:3	2	10.00000
TV:1	2	133.33333
TV:2	2	100.00000
NULL	2	519.00000
select product, country , year, sum(profit) from t1,t2 where t1.country_id=t2.country_id group by product, country, year with rollup;
product	country	year	sum(profit)
Calculator	India	2000	150
Calculator	India	NULL	150
Calculator	USA	1999	50
Calculator	USA	2000	75
Calculator	USA	NULL	125
Calculator	NULL	NULL	275
Computer	India	2000	2700
Computer	India	NULL	2700
Computer	USA	1999	2700
Computer	USA	2000	1500
Computer	USA	NULL	4200
Computer	NULL	NULL	6900
Phone	Finland	2003	10
Phone	Finland	NULL	10
Phone	NULL	NULL	10
TV	India	2000	200
TV	India	NULL	200
TV	USA	1999	250
TV	USA	2000	150
TV	USA	NULL	400
TV	NULL	NULL	600
NULL	NULL	NULL	7785
select product, `sum` from (select product, sum(profit) as 'sum' from t1 group by product with rollup) as tmp where product is null;
product	sum
NULL	7785
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);
product
Computer
Computer
Computer
Computer
Computer
select product, country_id , year, sum(profit) from t1 group by product, country_id, year having country_id is NULL;
product	country_id	year	sum(profit)
select concat(':',product,':'), sum(profit),avg(profit) from t1 group by product with rollup;
concat(':',product,':')	sum(profit)	avg(profit)
:Calculator:	275	68.75000
:Computer:	6900	1380.00000
:Phone:	10	10.00000
:TV:	600	120.00000
NULL	7785	519.00000
select product, country_id , year, sum(profit) from t1 group by product, country_id, year with cube;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'cube' at line 1
explain select product, country_id , year, sum(profit) from t1 group by product, country_id, year with cube;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'cube' at line 1
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;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'cube union all select product, country_id , year, sum(profit) from t1 group by p' at line 1
drop table t1,t2;
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;
i	COUNT(*)
100	1
NULL	1
SELECT t1.i, t2.i, COUNT(*) FROM t1,t2 GROUP BY t1.i,t2.i WITH ROLLUP;
i	i	COUNT(*)
100	100	1
100	200	1
100	NULL	2
NULL	NULL	2
drop table t1,t2;
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;
day	sample	not_cancelled
2004-06-06	2	1
2004-06-07	1	0
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;
day	sample	not_cancelled
2004-06-06	2	1
2004-06-07	1	0
NULL	3	1
DROP TABLE user_day;
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;
SUM(b)
4
6
4
14
SELECT DISTINCT SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
SUM(b)
4
6
14
SELECT SUM(b), COUNT(DISTINCT b) FROM t1 GROUP BY a WITH ROLLUP;
SUM(b)	COUNT(DISTINCT b)
4	1
6	2
4	1
14	3
SELECT DISTINCT SUM(b), COUNT(DISTINCT b) FROM t1 GROUP BY a WITH ROLLUP;
SUM(b)	COUNT(DISTINCT b)
4	1
6	2
14	3
SELECT SUM(b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
SUM(b)	COUNT(*)
4	1
6	4
4	4
14	9
SELECT DISTINCT SUM(b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
SUM(b)	COUNT(*)
4	1
6	4
4	4
14	9
SELECT SUM(b), COUNT(DISTINCT b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
SUM(b)	COUNT(DISTINCT b)	COUNT(*)
4	1	1
6	2	4
4	1	4
14	3	9
SELECT DISTINCT SUM(b), COUNT(DISTINCT b), COUNT(*) FROM t1
GROUP BY a WITH ROLLUP;
SUM(b)	COUNT(DISTINCT b)	COUNT(*)
4	1	1
6	2	4
4	1	4
14	3	9
SELECT a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;
a	sum(b)
1	4
1	4
2	2
2	4
2	6
4	4
4	4
NULL	14
SELECT DISTINCT a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;
a	sum(b)
1	4
2	2
2	4
2	6
4	4
NULL	14
SELECT b, a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;
b	a	sum(b)
4	1	4
NULL	1	4
1	2	2
2	2	4
NULL	2	6
1	4	4
NULL	4	4
NULL	NULL	14
SELECT DISTINCT b,a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;
b	a	sum(b)
4	1	4
NULL	1	4
1	2	2
2	2	4
NULL	2	6
1	4	4
NULL	4	4
NULL	NULL	14
ALTER TABLE t1 ADD COLUMN c INT;
SELECT a,b,sum(c) FROM t1 GROUP BY a,b,c WITH ROLLUP;
a	b	sum(c)
1	4	NULL
1	4	NULL
1	NULL	NULL
2	1	NULL
2	1	NULL
2	2	NULL
2	2	NULL
2	NULL	NULL
4	1	NULL
4	1	NULL
4	NULL	NULL
NULL	NULL	NULL
SELECT distinct a,b,sum(c) FROM t1 GROUP BY a,b,c WITH ROLLUP;
a	b	sum(c)
1	4	NULL
1	NULL	NULL
2	1	NULL
2	2	NULL
2	NULL	NULL
4	1	NULL
4	NULL	NULL
NULL	NULL	NULL
DROP TABLE t1;
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;
a	SUM(b)
1	4
SELECT SQL_CALC_FOUND_ROWS a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1;
a	SUM(b)
1	4
DROP TABLE t1;
CREATE TABLE t1 (a int NOT NULL);
INSERT INTO t1 VALUES (1),(2);
SELECT a, SUM(a) m FROM  t1 GROUP BY a WITH ROLLUP;
a	m
1	1
2	2
NULL	3
SELECT * FROM ( SELECT a, SUM(a) m FROM  t1 GROUP BY a WITH ROLLUP ) t2;
a	m
1	1
2	2
NULL	3
DROP TABLE t1;
set div_precision_increment= @sav_dpi;
CREATE TABLE t1 (a int);
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;
a	SUM(a)	SUM(a)+1
1	1	2
2	2	3
SELECT a, SUM(a), SUM(a)+1 FROM (SELECT a FROM t1 UNION select 2) d 
GROUP BY a WITH ROLLUP;
a	SUM(a)	SUM(a)+1
1	1	2
2	2	3
NULL	3	4
SELECT a, SUM(a), SUM(a)+1 FROM (SELECT 1 a UNION select 2) d 
GROUP BY a;
a	SUM(a)	SUM(a)+1
1	1	2
2	2	3
SELECT a, SUM(a), SUM(a)+1 FROM (SELECT 1 a UNION select 2) d 
GROUP BY a WITH ROLLUP;
a	SUM(a)	SUM(a)+1
1	1	2
2	2	3
NULL	3	4
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;
a	SUM(a)	SUM(a)+1	CONCAT(SUM(a),'x')	SUM(a)+SUM(a)	SUM(a)
1	1	2	1x	2	1
2	2	3	2x	4	2
5	5	6	5x	10	5
NULL	8	9	8x	16	8
DROP TABLE t1;
CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES (1),(2);
SELECT a, a+1, SUM(a) FROM t1 GROUP BY a WITH ROLLUP;
a	a+1	SUM(a)
1	2	1
2	3	2
NULL	NULL	3
SELECT a+1 FROM t1 GROUP BY a WITH ROLLUP;
a+1
2
3
NULL
SELECT a+SUM(a) FROM t1 GROUP BY a WITH ROLLUP;
a+SUM(a)
2
4
NULL
SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING b > 2;
a	b
2	3
SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING a IS NULL;
a	b
NULL	NULL
SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING b IS NULL;
a	b
NULL	NULL
SELECT IFNULL(a, 'TEST') FROM t1 GROUP BY a WITH ROLLUP;
IFNULL(a, 'TEST')
1
2
TEST
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;
a	b	SUM(b)
1	4	4
1	NULL	4
2	1	2
2	2	4
2	NULL	6
4	1	4
4	NULL	4
NULL	NULL	14
SELECT a,b,SUM(b), a+b as c FROM t2
GROUP BY a,b WITH ROLLUP HAVING c IS NULL;
a	b	SUM(b)	c
1	NULL	4	NULL
2	NULL	6	NULL
4	NULL	4	NULL
NULL	NULL	14	NULL
SELECT IFNULL(a, 'TEST'), COALESCE(b, 'TEST') FROM t2 
GROUP BY a, b WITH ROLLUP;
IFNULL(a, 'TEST')	COALESCE(b, 'TEST')
1	4
1	TEST
2	1
2	2
2	TEST
4	1
4	TEST
TEST	TEST
DROP TABLE t1,t2;
CREATE TABLE t1 (a INT NOT NULL, b INT 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;
a	b	c	count
1	1	1	1
1	1	NULL	1
1	2	1	1
1	2	NULL	1
1	NULL	NULL	2
NULL	NULL	NULL	2
DROP TABLE t1;
CREATE TABLE t1 (a int NOT NULL);
INSERT INTO t1 VALUES (1),(2);
SELECT * FROM (SELECT a, a + 1, COUNT(*) FROM t1 GROUP BY a WITH ROLLUP) t;
a	a + 1	COUNT(*)
1	2	1
2	3	1
NULL	NULL	2
SELECT * FROM (SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP) t;
a	LENGTH(a)	COUNT(*)
1	1	1
2	1	1
NULL	NULL	2
DROP TABLE t1;
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;
a	max(b)
NULL	2
a	1
NULL	2
select distinct a, max(b) from t1 group by a with rollup;
a	max(b)
NULL	2
a	1
drop table t1;
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;
left(a,10)	a	sum(b)
2006-07-01	2006-07-01 21:30	1
2006-07-01	2006-07-01 23:30	10
2006-07-01	NULL	11
NULL	NULL	11
select left(a,10) x, a, sum(b) from t1 group by x,a with rollup;
x	a	sum(b)
2006-07-01	2006-07-01 21:30	1
2006-07-01	2006-07-01 23:30	10
2006-07-01	NULL	11
NULL	NULL	11
drop table t1;
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;
a	SUM(b)
1	30
2	90
3	30
NULL	150
SELECT DISTINCT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
a	SUM(b)
1	30
2	90
3	30
NULL	150
SELECT a, b, COUNT(*) FROM t1 GROUP BY a,b WITH ROLLUP;
a	b	COUNT(*)
1	10	1
1	20	1
1	NULL	2
2	10	2
2	30	1
2	40	1
2	NULL	4
3	30	1
3	NULL	1
NULL	NULL	7
SELECT DISTINCT a, b, COUNT(*) FROM t1 GROUP BY a,b WITH ROLLUP;
a	b	COUNT(*)
1	10	1
1	20	1
1	NULL	2
2	10	2
2	30	1
2	40	1
2	NULL	4
3	30	1
3	NULL	1
NULL	NULL	7
SELECT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP;
x	a	SUM(b)
x	1	30
x	2	90
x	3	30
x	NULL	150
NULL	NULL	150
SELECT DISTINCT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP;
x	a	SUM(b)
x	1	30
x	2	90
x	3	30
x	NULL	150
NULL	NULL	150
SELECT DISTINCT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP;
x	a	SUM(b)
x	1	30
x	2	90
x	3	30
x	NULL	150
NULL	NULL	150
DROP TABLE t1;
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;
a	SUM(a)
1	4
3	6
4	4
NULL	14
DROP TABLE t1;
#
# Bug#31095: Unexpected NULL constant caused server crash.
#
create table t1(a int);
insert into t1 values (1),(2),(3);
select count(a) from t1 group by null with rollup;
count(a)
3
3
drop table t1;
##############################################################
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES(0);
SELECT 1 FROM t1 GROUP BY (DATE(NULL)) WITH ROLLUP;
1
1
1
DROP TABLE t1;
End of 5.0 tests

1 by brian clean slate	1	drop table if exists t1,t2;
	2	set @sav_dpi= @@div_precision_increment;
	3	set div_precision_increment= 5;
	4	show variables like 'div_precision_increment';
	5	Variable_name Value
	6	div_precision_increment 5
	7	create table t1 (product varchar(32), country_id int not null, year int, profit int);
	8	insert into t1 values ( 'Computer', 2,2000, 1200),
	9	( 'TV', 1, 1999, 150),
	10	( 'Calculator', 1, 1999,50),
	11	( 'Computer', 1, 1999,1500),
	12	( 'Computer', 1, 2000,1500),
	13	( 'TV', 1, 2000, 150),
	14	( 'TV', 2, 2000, 100),
	15	( 'TV', 2, 2000, 100),
	16	( 'Calculator', 1, 2000,75),
	17	( 'Calculator', 2, 2000,75),
	18	( 'TV', 1, 1999, 100),
	19	( 'Computer', 1, 1999,1200),
	20	( 'Computer', 2, 2000,1500),
	21	( 'Calculator', 2, 2000,75),
	22	( 'Phone', 3, 2003,10)
	23	;
	24	create table t2 (country_id int primary key, country char(20) not null);
	25	insert into t2 values (1, 'USA'),(2,'India'), (3,'Finland');
	26	select product, sum(profit) from t1 group by product;
	27	product sum(profit)
	28	Calculator 275
	29	Computer 6900
	30	Phone 10
	31	TV 600
	32	select product, sum(profit) from t1 group by product with rollup;
	33	product sum(profit)
	34	Calculator 275
	35	Computer 6900
	36	Phone 10
	37	TV 600
	38	NULL 7785
	39	select product, sum(profit) from t1 group by 1 with rollup;
	40	product sum(profit)
	41	Calculator 275
	42	Computer 6900
	43	Phone 10
	44	TV 600
	45	NULL 7785
	46	select product, sum(profit),avg(profit) from t1 group by product with rollup;
	47	product sum(profit) avg(profit)
	48	Calculator 275 68.75000
	49	Computer 6900 1380.00000
	50	Phone 10 10.00000
	51	TV 600 120.00000
	52	NULL 7785 519.00000
	53	select product, country_id , year, sum(profit) from t1 group by product, country_id, year;
	54	product country_id year sum(profit)
	55	Calculator 1 1999 50
	56	Calculator 1 2000 75
	57	Calculator 2 2000 150
	58	Computer 1 1999 2700
	59	Computer 1 2000 1500
	60	Computer 2 2000 2700
	61	Phone 3 2003 10
	62	TV 1 1999 250
	63	TV 1 2000 150
	64	TV 2 2000 200
65	select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup;
66	product country_id year sum(profit)
67	Calculator 1 1999 50
68	Calculator 1 2000 75
69	Calculator 1 NULL 125
70	Calculator 2 2000 150
71	Calculator 2 NULL 150
72	Calculator NULL NULL 275
73	Computer 1 1999 2700
74	Computer 1 2000 1500
75	Computer 1 NULL 4200
76	Computer 2 2000 2700
77	Computer 2 NULL 2700
78	Computer NULL NULL 6900
79	Phone 3 2003 10
80	Phone 3 NULL 10
81	Phone NULL NULL 10
82	TV 1 1999 250
83	TV 1 2000 150
84	TV 1 NULL 400
85	TV 2 2000 200
86	TV 2 NULL 200
87	TV NULL NULL 600
88	NULL NULL NULL 7785
89	explain extended select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup;
90	id select_type table type possible_keys key key_len ref rows filtered Extra
91	1 SIMPLE t1 ALL NULL NULL NULL NULL 15 100.00 Using filesort
92	Warnings:
93	Note 1003 select `test`.`t1`.`product` AS `product`,`test`.`t1`.`country_id` AS `country_id`,`test`.`t1`.`year` AS `year`,sum(`test`.`t1`.`profit`) AS `sum(profit)` from `test`.`t1` group by `test`.`t1`.`product`,`test`.`t1`.`country_id`,`test`.`t1`.`year` with rollup
94	select product, country_id , sum(profit) from t1 group by product desc, country_id with rollup;
95	product country_id sum(profit)
96	TV 1 400
97	TV 2 200
98	TV NULL 600
99	Phone 3 10
100	Phone NULL 10
101	Computer 1 4200
102	Computer 2 2700
103	Computer NULL 6900
104	Calculator 1 125
105	Calculator 2 150
106	Calculator NULL 275
107	NULL NULL 7785
108	select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup limit 5;
109	product country_id year sum(profit)
110	Calculator 1 1999 50
111	Calculator 1 2000 75
112	Calculator 1 NULL 125
113	Calculator 2 2000 150
114	Calculator 2 NULL 150
115	select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup limit 3,3;
116	product country_id year sum(profit)
117	Calculator 2 2000 150
118	Calculator 2 NULL 150
119	Calculator NULL NULL 275
120	select product, country_id, count(*), count(distinct year) from t1 group by product, country_id;
121	product country_id count(*) count(distinct year)
122	Calculator 1 2 2
123	Calculator 2 2 1
124	Computer 1 3 2
125	Computer 2 2 1
126	Phone 3 1 1
127	TV 1 3 2
128	TV 2 2 1
129	select product, country_id, count(*), count(distinct year) from t1 group by product, country_id with rollup;
130	product country_id count(*) count(distinct year)
131	Calculator 1 2 2
132	Calculator 2 2 1
133	Calculator NULL 4 2
134	Computer 1 3 2
135	Computer 2 2 1
136	Computer NULL 5 2
137	Phone 3 1 1
138	Phone NULL 1 1
139	TV 1 3 2
140	TV 2 2 1
141	TV NULL 5 2
142	NULL NULL 15 3
143	select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup having country_id = 1;
144	product country_id year sum(profit)
145	Calculator 1 1999 50
146	Calculator 1 2000 75
147	Calculator 1 NULL 125
148	Computer 1 1999 2700
149	Computer 1 2000 1500
150	Computer 1 NULL 4200
151	TV 1 1999 250
152	TV 1 2000 150
153	TV 1 NULL 400
154	select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup having sum(profit) > 200;
155	product country_id year sum(profit)
156	Calculator NULL NULL 275
157	Computer 1 1999 2700
158	Computer 1 2000 1500
159	Computer 1 NULL 4200
160	Computer 2 2000 2700
161	Computer 2 NULL 2700
162	Computer NULL NULL 6900
163	TV 1 1999 250
164	TV 1 NULL 400
165	TV NULL NULL 600
166	NULL NULL NULL 7785
167	select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup having sum(profit) > 7000;
168	product country_id year sum(profit)
169	NULL NULL NULL 7785
170	select concat(product,':',country_id) as 'prod', concat(":",year,":") as 'year',1+1, sum(profit)/count(*) from t1 group by 1,2 with rollup;
171	prod year 1+1 sum(profit)/count(*)
172	Calculator:1 :1999: 2 50.00000
173	Calculator:1 :2000: 2 75.00000
174	Calculator:1 NULL 2 62.50000
175	Calculator:2 :2000: 2 75.00000
176	Calculator:2 NULL 2 75.00000
177	Computer:1 :1999: 2 1350.00000
178	Computer:1 :2000: 2 1500.00000
179	Computer:1 NULL 2 1400.00000
180	Computer:2 :2000: 2 1350.00000
181	Computer:2 NULL 2 1350.00000
182	Phone:3 :2003: 2 10.00000
183	Phone:3 NULL 2 10.00000
184	TV:1 :1999: 2 125.00000
185	TV:1 :2000: 2 150.00000
186	TV:1 NULL 2 133.33333
187	TV:2 :2000: 2 100.00000
188	TV:2 NULL 2 100.00000
189	NULL NULL 2 519.00000
190	select product, sum(profit)/count(*) from t1 group by product with rollup;
191	product sum(profit)/count(*)
192	Calculator 68.75000
193	Computer 1380.00000
194	Phone 10.00000
195	TV 120.00000
196	NULL 519.00000
197	select left(product,4) as prod, sum(profit)/count(*) from t1 group by prod with rollup;
198	prod sum(profit)/count(*)
199	Calc 68.75000
200	Comp 1380.00000
201	Phon 10.00000
202	TV 120.00000
203	NULL 519.00000
204	select concat(product,':',country_id), 1+1, sum(profit)/count(*) from t1 group by concat(product,':',country_id) with rollup;
205	concat(product,':',country_id) 1+1 sum(profit)/count(*)
206	Calculator:1 2 62.50000
207	Calculator:2 2 75.00000
208	Computer:1 2 1400.00000
209	Computer:2 2 1350.00000
210	Phone:3 2 10.00000
211	TV:1 2 133.33333
212	TV:2 2 100.00000
213	NULL 2 519.00000
214	select product, country , year, sum(profit) from t1,t2 where t1.country_id=t2.country_id group by product, country, year with rollup;
215	product country year sum(profit)
216	Calculator India 2000 150
217	Calculator India NULL 150
218	Calculator USA 1999 50
219	Calculator USA 2000 75
220	Calculator USA NULL 125
221	Calculator NULL NULL 275
222	Computer India 2000 2700
223	Computer India NULL 2700
224	Computer USA 1999 2700
225	Computer USA 2000 1500
226	Computer USA NULL 4200
227	Computer NULL NULL 6900
228	Phone Finland 2003 10
229	Phone Finland NULL 10
230	Phone NULL NULL 10
231	TV India 2000 200
232	TV India NULL 200
233	TV USA 1999 250
234	TV USA 2000 150
235	TV USA NULL 400
236	TV NULL NULL 600
237	NULL NULL NULL 7785
238	select product, `sum` from (select product, sum(profit) as 'sum' from t1 group by product with rollup) as tmp where product is null;
239	product sum
240	NULL 7785
241	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);
242	product
243	Computer
244	Computer
245	Computer
246	Computer
247	Computer
248	select product, country_id , year, sum(profit) from t1 group by product, country_id, year having country_id is NULL;
249	product country_id year sum(profit)
250	select concat(':',product,':'), sum(profit),avg(profit) from t1 group by product with rollup;
251	concat(':',product,':') sum(profit) avg(profit)
252	:Calculator: 275 68.75000
253	:Computer: 6900 1380.00000
254	:Phone: 10 10.00000
255	:TV: 600 120.00000
256	NULL 7785 519.00000
257	select product, country_id , year, sum(profit) from t1 group by product, country_id, year with cube;
685.4.13 by Jay Pipes OLAP test now fixed. We throw a syntax error upon seeing WITH CUBE, not	258	ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'cube' at line 1
1 by brian clean slate	259	explain select product, country_id , year, sum(profit) from t1 group by product, country_id, year with cube;
685.4.13 by Jay Pipes OLAP test now fixed. We throw a syntax error upon seeing WITH CUBE, not	260	ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'cube' at line 1
1 by brian clean slate	261	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;
685.4.13 by Jay Pipes OLAP test now fixed. We throw a syntax error upon seeing WITH CUBE, not	262	ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'cube union all select product, country_id , year, sum(profit) from t1 group by p' at line 1
1 by brian clean slate	263	drop table t1,t2;
	264	CREATE TABLE t1 (i int);
	265	INSERT INTO t1 VALUES(100);
	266	CREATE TABLE t2 (i int);
	267	INSERT INTO t2 VALUES (100),(200);
	268	SELECT i, COUNT(*) FROM t1 GROUP BY i WITH ROLLUP;
	269	i COUNT(*)
	270	100 1
	271	NULL 1
	272	SELECT t1.i, t2.i, COUNT(*) FROM t1,t2 GROUP BY t1.i,t2.i WITH ROLLUP;
	273	i i COUNT(*)
	274	100 100 1
	275	100 200 1
	276	100 NULL 2
	277	NULL NULL 2
	278	drop table t1,t2;
	279	CREATE TABLE user_day(
	280	user_id INT NOT NULL,
	281	date DATE NOT NULL,
	282	UNIQUE INDEX user_date (user_id, date)
	283	);
	284	INSERT INTO user_day VALUES
	285	(1, '2004-06-06' ),
	286	(1, '2004-06-07' ),
	287	(2, '2004-06-06' );
	288	SELECT
	289	d.date AS day,
	290	COUNT(d.user_id) as sample,
	291	COUNT(next_day.user_id) AS not_cancelled
	292	FROM user_day d
	293	LEFT JOIN user_day next_day
	294	ON next_day.user_id=d.user_id AND
	295	next_day.date= DATE_ADD( d.date, interval 1 day )
	296	GROUP BY day;
	297	day sample not_cancelled
	298	2004-06-06 2 1
	299	2004-06-07 1 0
	300	SELECT
	301	d.date AS day,
	302	COUNT(d.user_id) as sample,
	303	COUNT(next_day.user_id) AS not_cancelled
	304	FROM user_day d
	305	LEFT JOIN user_day next_day
	306	ON next_day.user_id=d.user_id AND
	307	next_day.date= DATE_ADD( d.date, interval 1 day )
	308	GROUP BY day
	309	WITH ROLLUP;
	310	day sample not_cancelled
	311	2004-06-06 2 1
	312	2004-06-07 1 0
	313	NULL 3 1
	314	DROP TABLE user_day;
	315	CREATE TABLE t1 (a int, b int);
	316	INSERT INTO t1 VALUES
	317	(1,4),
	318	(2,2), (2,2),
	319	(4,1), (4,1), (4,1), (4,1),
	320	(2,1), (2,1);
	321	SELECT SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
	322	SUM(b)
	323	4
	324	6
	325	4
	326	14
327	SELECT DISTINCT SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
328	SUM(b)
329	4
330	6
331	14
332	SELECT SUM(b), COUNT(DISTINCT b) FROM t1 GROUP BY a WITH ROLLUP;
333	SUM(b) COUNT(DISTINCT b)
334	4 1
335	6 2
336	4 1
337	14 3
338	SELECT DISTINCT SUM(b), COUNT(DISTINCT b) FROM t1 GROUP BY a WITH ROLLUP;
339	SUM(b) COUNT(DISTINCT b)
340	4 1
341	6 2
342	14 3
343	SELECT SUM(b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
344	SUM(b) COUNT(*)
345	4 1
346	6 4
347	4 4
348	14 9
349	SELECT DISTINCT SUM(b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
350	SUM(b) COUNT(*)
351	4 1
352	6 4
353	4 4
354	14 9
355	SELECT SUM(b), COUNT(DISTINCT b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
356	SUM(b) COUNT(DISTINCT b) COUNT(*)
357	4 1 1
358	6 2 4
359	4 1 4
360	14 3 9
361	SELECT DISTINCT SUM(b), COUNT(DISTINCT b), COUNT(*) FROM t1
362	GROUP BY a WITH ROLLUP;
363	SUM(b) COUNT(DISTINCT b) COUNT(*)
364	4 1 1
365	6 2 4
366	4 1 4
367	14 3 9
368	SELECT a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;
369	a sum(b)
370	1 4
371	1 4
372	2 2
373	2 4
374	2 6
375	4 4
376	4 4
377	NULL 14
378	SELECT DISTINCT a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;
379	a sum(b)
380	1 4
381	2 2
382	2 4
383	2 6
384	4 4
385	NULL 14
386	SELECT b, a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;
387	b a sum(b)
388	4 1 4
389	NULL 1 4
390	1 2 2
391	2 2 4
392	NULL 2 6
393	1 4 4
394	NULL 4 4
395	NULL NULL 14
396	SELECT DISTINCT b,a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;
397	b a sum(b)
398	4 1 4
399	NULL 1 4
400	1 2 2
401	2 2 4
402	NULL 2 6
403	1 4 4
404	NULL 4 4
405	NULL NULL 14
406	ALTER TABLE t1 ADD COLUMN c INT;
407	SELECT a,b,sum(c) FROM t1 GROUP BY a,b,c WITH ROLLUP;
408	a b sum(c)
409	1 4 NULL
410	1 4 NULL
411	1 NULL NULL
412	2 1 NULL
413	2 1 NULL
414	2 2 NULL
415	2 2 NULL
416	2 NULL NULL
417	4 1 NULL
418	4 1 NULL
419	4 NULL NULL
420	NULL NULL NULL
421	SELECT distinct a,b,sum(c) FROM t1 GROUP BY a,b,c WITH ROLLUP;
422	a b sum(c)
423	1 4 NULL
424	1 NULL NULL
425	2 1 NULL
426	2 2 NULL
427	2 NULL NULL
428	4 1 NULL
429	4 NULL NULL
430	NULL NULL NULL
431	DROP TABLE t1;
432	CREATE TABLE t1 (a int, b int);
433	INSERT INTO t1 VALUES
434	(1,4),
435	(2,2), (2,2),
436	(4,1), (4,1), (4,1), (4,1),
437	(2,1), (2,1);
438	SELECT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1;
439	a SUM(b)
440	1 4
441	SELECT SQL_CALC_FOUND_ROWS a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1;
442	a SUM(b)
443	1 4
444	DROP TABLE t1;
685.4.13 by Jay Pipes OLAP test now fixed. We throw a syntax error upon seeing WITH CUBE, not	445	CREATE TABLE t1 (a int NOT NULL);
1 by brian clean slate	446	INSERT INTO t1 VALUES (1),(2);
	447	SELECT a, SUM(a) m FROM t1 GROUP BY a WITH ROLLUP;
	448	a m
	449	1 1
	450	2 2
	451	NULL 3
	452	SELECT * FROM ( SELECT a, SUM(a) m FROM t1 GROUP BY a WITH ROLLUP ) t2;
	453	a m
	454	1 1
	455	2 2
	456	NULL 3
	457	DROP TABLE t1;
	458	set div_precision_increment= @sav_dpi;
685.4.13 by Jay Pipes OLAP test now fixed. We throw a syntax error upon seeing WITH CUBE, not	459	CREATE TABLE t1 (a int);
1 by brian clean slate	460	INSERT INTO t1 VALUES (1),(2);
	461	SELECT a, SUM(a), SUM(a)+1 FROM (SELECT a FROM t1 UNION select 2) d
	462	GROUP BY a;
	463	a SUM(a) SUM(a)+1
	464	1 1 2
	465	2 2 3
	466	SELECT a, SUM(a), SUM(a)+1 FROM (SELECT a FROM t1 UNION select 2) d
	467	GROUP BY a WITH ROLLUP;
	468	a SUM(a) SUM(a)+1
	469	1 1 2
	470	2 2 3
	471	NULL 3 4
	472	SELECT a, SUM(a), SUM(a)+1 FROM (SELECT 1 a UNION select 2) d
	473	GROUP BY a;
	474	a SUM(a) SUM(a)+1
	475	1 1 2
	476	2 2 3
	477	SELECT a, SUM(a), SUM(a)+1 FROM (SELECT 1 a UNION select 2) d
	478	GROUP BY a WITH ROLLUP;
	479	a SUM(a) SUM(a)+1
	480	1 1 2
	481	2 2 3
	482	NULL 3 4
	483	SELECT a, SUM(a), SUM(a)+1, CONCAT(SUM(a),'x'), SUM(a)+SUM(a), SUM(a)
	484	FROM (SELECT 1 a, 2 b UNION SELECT 2,3 UNION SELECT 5,6 ) d
	485	GROUP BY a WITH ROLLUP;
	486	a SUM(a) SUM(a)+1 CONCAT(SUM(a),'x') SUM(a)+SUM(a) SUM(a)
	487	1 1 2 1x 2 1
	488	2 2 3 2x 4 2
	489	5 5 6 5x 10 5
	490	NULL 8 9 8x 16 8
	491	DROP TABLE t1;
685.4.13 by Jay Pipes OLAP test now fixed. We throw a syntax error upon seeing WITH CUBE, not	492	CREATE TABLE t1 (a int);
1 by brian clean slate	493	INSERT INTO t1 VALUES (1),(2);
	494	SELECT a, a+1, SUM(a) FROM t1 GROUP BY a WITH ROLLUP;
	495	a a+1 SUM(a)
	496	1 2 1
	497	2 3 2
	498	NULL NULL 3
	499	SELECT a+1 FROM t1 GROUP BY a WITH ROLLUP;
	500	a+1
	501	2
	502	3
	503	NULL
	504	SELECT a+SUM(a) FROM t1 GROUP BY a WITH ROLLUP;
	505	a+SUM(a)
	506	2
	507	4
	508	NULL
	509	SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING b > 2;
	510	a b
	511	2 3
	512	SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING a IS NULL;
	513	a b
	514	NULL NULL
	515	SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING b IS NULL;
	516	a b
	517	NULL NULL
	518	SELECT IFNULL(a, 'TEST') FROM t1 GROUP BY a WITH ROLLUP;
	519	IFNULL(a, 'TEST')
	520	1
	521	2
	522	TEST
	523	CREATE TABLE t2 (a int, b int);
	524	INSERT INTO t2 VALUES
	525	(1,4),
	526	(2,2), (2,2),
	527	(4,1), (4,1), (4,1), (4,1),
	528	(2,1), (2,1);
	529	SELECT a,b,SUM(b) FROM t2 GROUP BY a,b WITH ROLLUP;
	530	a b SUM(b)
	531	1 4 4
	532	1 NULL 4
	533	2 1 2
	534	2 2 4
	535	2 NULL 6
	536	4 1 4
	537	4 NULL 4
	538	NULL NULL 14
	539	SELECT a,b,SUM(b), a+b as c FROM t2
	540	GROUP BY a,b WITH ROLLUP HAVING c IS NULL;
	541	a b SUM(b) c
	542	1 NULL 4 NULL
	543	2 NULL 6 NULL
	544	4 NULL 4 NULL
	545	NULL NULL 14 NULL
	546	SELECT IFNULL(a, 'TEST'), COALESCE(b, 'TEST') FROM t2
	547	GROUP BY a, b WITH ROLLUP;
	548	IFNULL(a, 'TEST') COALESCE(b, 'TEST')
	549	1 4
	550	1 TEST
	551	2 1
	552	2 2
	553	2 TEST
	554	4 1
	555	4 TEST
	556	TEST TEST
557	DROP TABLE t1,t2;
685.4.13 by Jay Pipes OLAP test now fixed. We throw a syntax error upon seeing WITH CUBE, not	558	CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL);
1 by brian clean slate	559	INSERT INTO t1 VALUES (1, 1);
	560	INSERT INTO t1 VALUES (1, 2);
	561	SELECT a, b, a AS c, COUNT(*) AS count FROM t1 GROUP BY a, b, c WITH ROLLUP;
	562	a b c count
	563	1 1 1 1
	564	1 1 NULL 1
	565	1 2 1 1
	566	1 2 NULL 1
	567	1 NULL NULL 2
	568	NULL NULL NULL 2
	569	DROP TABLE t1;
685.4.13 by Jay Pipes OLAP test now fixed. We throw a syntax error upon seeing WITH CUBE, not	570	CREATE TABLE t1 (a int NOT NULL);
1 by brian clean slate	571	INSERT INTO t1 VALUES (1),(2);
	572	SELECT * FROM (SELECT a, a + 1, COUNT(*) FROM t1 GROUP BY a WITH ROLLUP) t;
	573	a a + 1 COUNT(*)
	574	1 2 1
	575	2 3 1
	576	NULL NULL 2
	577	SELECT * FROM (SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP) t;
	578	a LENGTH(a) COUNT(*)
	579	1 1 1
	580	2 1 1
	581	NULL NULL 2
	582	DROP TABLE t1;
	583	create table t1 ( a varchar(9), b int );
	584	insert into t1 values('a',1),(null,2);
	585	select a, max(b) from t1 group by a with rollup;
	586	a max(b)
	587	NULL 2
	588	a 1
	589	NULL 2
	590	select distinct a, max(b) from t1 group by a with rollup;
	591	a max(b)
	592	NULL 2
	593	a 1
	594	drop table t1;
	595	create table t1 (a varchar(22) not null , b int);
	596	insert into t1 values ("2006-07-01 21:30", 1), ("2006-07-01 23:30", 10);
	597	select left(a,10), a, sum(b) from t1 group by 1,2 with rollup;
	598	left(a,10) a sum(b)
	599	2006-07-01 2006-07-01 21:30 1
	600	2006-07-01 2006-07-01 23:30 10
	601	2006-07-01 NULL 11
	602	NULL NULL 11
	603	select left(a,10) x, a, sum(b) from t1 group by x,a with rollup;
	604	x a sum(b)
	605	2006-07-01 2006-07-01 21:30 1
	606	2006-07-01 2006-07-01 23:30 10
	607	2006-07-01 NULL 11
	608	NULL NULL 11
	609	drop table t1;
	610	CREATE TABLE t1 (a int, b int);
	611	INSERT INTO t1
	612	VALUES (2,10),(3,30),(2,40),(1,10),(2,30),(1,20),(2,10);
	613	SELECT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
	614	a SUM(b)
	615	1 30
	616	2 90
	617	3 30
	618	NULL 150
	619	SELECT DISTINCT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
	620	a SUM(b)
	621	1 30
	622	2 90
	623	3 30
	624	NULL 150
	625	SELECT a, b, COUNT(*) FROM t1 GROUP BY a,b WITH ROLLUP;
	626	a b COUNT(*)
	627	1 10 1
	628	1 20 1
	629	1 NULL 2
	630	2 10 2
	631	2 30 1
	632	2 40 1
	633	2 NULL 4
	634	3 30 1
635	3 NULL 1
636	NULL NULL 7
637	SELECT DISTINCT a, b, COUNT(*) FROM t1 GROUP BY a,b WITH ROLLUP;
638	a b COUNT(*)
639	1 10 1
640	1 20 1
641	1 NULL 2
642	2 10 2
643	2 30 1
644	2 40 1
645	2 NULL 4
646	3 30 1
647	3 NULL 1
648	NULL NULL 7
649	SELECT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP;
650	x a SUM(b)
651	x 1 30
652	x 2 90
653	x 3 30
654	x NULL 150
655	NULL NULL 150
656	SELECT DISTINCT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP;
657	x a SUM(b)
658	x 1 30
659	x 2 90
660	x 3 30
661	x NULL 150
662	NULL NULL 150
663	SELECT DISTINCT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP;
664	x a SUM(b)
665	x 1 30
666	x 2 90
667	x 3 30
668	x NULL 150
669	NULL NULL 150
670	DROP TABLE t1;
671	CREATE TABLE t1 (a int, KEY (a));
672	INSERT INTO t1 VALUES (3), (1), (4), (1), (3), (1), (1);
673	SELECT * FROM (SELECT a, SUM(a) FROM t1 GROUP BY a WITH ROLLUP) as t;
674	a SUM(a)
675	1 4
676	3 6
677	4 4
678	NULL 14
679	DROP TABLE t1;
680	#
681	# Bug#31095: Unexpected NULL constant caused server crash.
682	#
683	create table t1(a int);
684	insert into t1 values (1),(2),(3);
685	select count(a) from t1 group by null with rollup;
686	count(a)
687	3
688	3
689	drop table t1;
690	##############################################################
691	CREATE TABLE t1(a INT);
692	INSERT INTO t1 VALUES(0);
693	SELECT 1 FROM t1 GROUP BY (DATE(NULL)) WITH ROLLUP;
694	1
695	1
696	1
697	DROP TABLE t1;
698	End of 5.0 tests