319 lines
8.2 KiB
Plaintext
319 lines
8.2 KiB
Plaintext
CREATE TABLE t1(
|
|
product VARCHAR(32),
|
|
country_id INTEGER NOT NULL,
|
|
year INTEGER,
|
|
profit INTEGER);
|
|
|
|
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 INTEGER PRIMARY KEY,
|
|
country CHAR(20) NOT NULL);
|
|
|
|
INSERT INTO t2 VALUES (1, 'USA'),(2,'India'), (3,'Finland');
|
|
|
|
--echo # First simple rollups, with just grand total
|
|
--echo
|
|
--sorted_result
|
|
SELECT product, SUM(profit) FROM t1 GROUP BY product;
|
|
--echo
|
|
--sorted_result
|
|
SELECT product, SUM(profit) FROM t1 GROUP BY product WITH ROLLUP;
|
|
--echo
|
|
--sorted_result
|
|
SELECT product, SUM(profit) FROM t1 GROUP BY 1 WITH ROLLUP;
|
|
--echo
|
|
--sorted_result
|
|
SELECT product, SUM(profit),AVG(profit) FROM t1 GROUP BY product WITH ROLLUP;
|
|
|
|
--echo
|
|
--echo # Sub totals
|
|
--sorted_result
|
|
SELECT product, country_id , year, SUM(profit) FROM t1
|
|
GROUP BY product, country_id, year;
|
|
--echo
|
|
--sorted_result
|
|
SELECT product, country_id , year, SUM(profit) FROM t1
|
|
GROUP BY product, country_id, year WITH ROLLUP;
|
|
--echo
|
|
ANALYZE TABLE t1;
|
|
--echo
|
|
EXPLAIN FORMAT='plan_tree' SELECT product, country_id , year, SUM(profit)
|
|
FROM t1 GROUP BY product, country_id, year WITH ROLLUP;
|
|
--echo
|
|
SELECT product, country_id , SUM(profit) FROM t1
|
|
GROUP BY product, country_id WITH ROLLUP
|
|
ORDER BY product DESC, country_id;
|
|
|
|
--echo
|
|
--echo # limit
|
|
--sorted_result
|
|
SELECT product, country_id , year, SUM(profit) FROM t1
|
|
GROUP BY product, country_id, year WITH ROLLUP
|
|
ORDER BY product, country_id, year LIMIT 5;
|
|
--echo
|
|
--sorted_result
|
|
SELECT product, country_id , year, SUM(profit) FROM t1
|
|
GROUP BY product, country_id, year WITH ROLLUP
|
|
ORDER BY product, country_id, year limit 3,3;
|
|
--echo
|
|
--sorted_result
|
|
SELECT product, country_id, COUNT(*), COUNT(distinct year)
|
|
FROM t1 GROUP BY product, country_id;
|
|
--echo
|
|
--sorted_result
|
|
SELECT product, country_id, COUNT(*), COUNT(distinct year)
|
|
FROM t1 GROUP BY product, country_id WITH ROLLUP;
|
|
|
|
--echo
|
|
--echo # Test of having
|
|
--sorted_result
|
|
SELECT product, country_id , year, SUM(profit) FROM t1
|
|
GROUP BY product, country_id, year WITH ROLLUP HAVING country_id = 1;
|
|
--echo
|
|
--sorted_result
|
|
SELECT product, country_id , year, SUM(profit) FROM t1
|
|
GROUP BY product, country_id, year WITH ROLLUP HAVING SUM(profit) > 200;
|
|
--echo
|
|
--sorted_result
|
|
SELECT product, country_id , year, SUM(profit) FROM t1
|
|
GROUP BY product, country_id, year WITH ROLLUP HAVING SUM(profit) > 7000;
|
|
|
|
--echo
|
|
--echo # Functions
|
|
--sorted_result
|
|
SELECT CONCAT(product,':',country_id) AS 'prod', CONCAT(":",year,":") AS 'year',
|
|
1+1, SUM(profit)/COUNT(*) FROM t1 GROUP BY 1,2 WITH ROLLUP;
|
|
--echo
|
|
--sorted_result
|
|
SELECT product, SUM(profit)/COUNT(*) FROM t1 GROUP BY product WITH ROLLUP;
|
|
--echo
|
|
--sorted_result
|
|
SELECT LEFT(product,4) AS prod, SUM(profit)/COUNT(*) FROM t1
|
|
GROUP BY prod WITH ROLLUP;
|
|
--echo
|
|
--sorted_result
|
|
SELECT CONCAT(product,':',country_id), 1+1, SUM(profit)/COUNT(*) FROM t1
|
|
GROUP BY CONCAT(product,':',country_id) WITH ROLLUP;
|
|
|
|
SET @saved_sql_mode = @@session.sql_mode;
|
|
SET SESSION sql_mode= '';
|
|
--echo
|
|
--sorted_result
|
|
SELECT UPPER(product) AS prod,
|
|
SUM(profit)/COUNT(*)
|
|
FROM t1 GROUP BY prod WITH ROLLUP HAVING prod='COMPUTER' ;
|
|
SET SESSION sql_mode= @saved_sql_mode;
|
|
|
|
--echo
|
|
--echo # Joins
|
|
--sorted_result
|
|
SELECT product, country , year, SUM(profit) FROM t1,t2 WHERE
|
|
t1.country_id=t2.country_id GROUP BY product, country, year WITH ROLLUP;
|
|
|
|
--echo
|
|
--sorted_result
|
|
SELECT product, `SUM` FROM (SELECT product, SUM(profit) AS 'sum' FROM t1
|
|
GROUP BY product WITH ROLLUP) AS tmp
|
|
WHERE product is null;
|
|
|
|
--echo
|
|
--sorted_result
|
|
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);
|
|
|
|
--echo
|
|
--sorted_result
|
|
# The following does not 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;
|
|
|
|
--echo
|
|
--sorted_result
|
|
SELECT CONCAT(':',product,':'), SUM(profit), AVG(profit) FROM t1
|
|
GROUP BY product WITH ROLLUP;
|
|
|
|
#
|
|
# Test bug with const tables
|
|
#
|
|
--echo
|
|
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);
|
|
|
|
--echo
|
|
--sorted_result
|
|
SELECT i, COUNT(*) FROM t1 GROUP BY i WITH ROLLUP;
|
|
|
|
--echo
|
|
--sorted_result
|
|
SELECT t1.i, t2.i, COUNT(*) FROM t1,t2 GROUP BY t1.i,t2.i WITH ROLLUP;
|
|
|
|
#bug #4767: ROLLUP with LEFT JOIN
|
|
--echo
|
|
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' );
|
|
|
|
--echo
|
|
--sorted_result
|
|
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;
|
|
|
|
--echo
|
|
--sorted_result
|
|
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;
|
|
|
|
--echo
|
|
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);
|
|
|
|
--echo
|
|
--sorted_result
|
|
SELECT SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
|
|
--echo
|
|
--sorted_result
|
|
SELECT DISTINCT SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
|
|
--echo
|
|
--sorted_result
|
|
SELECT SUM(b), COUNT(DISTINCT b) FROM t1 GROUP BY a WITH ROLLUP;
|
|
--echo
|
|
--sorted_result
|
|
SELECT DISTINCT SUM(b), COUNT(DISTINCT b) FROM t1 GROUP BY a WITH ROLLUP;
|
|
--echo
|
|
--sorted_result
|
|
SELECT SUM(b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
|
|
--echo
|
|
--sorted_result
|
|
SELECT DISTINCT SUM(b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
|
|
--echo
|
|
--sorted_result
|
|
SELECT SUM(b), COUNT(DISTINCT b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
|
|
--echo
|
|
--sorted_result
|
|
SELECT DISTINCT SUM(b), COUNT(DISTINCT b), COUNT(*) FROM t1
|
|
GROUP BY a WITH ROLLUP;
|
|
--echo
|
|
--sorted_result
|
|
SELECT a, SUM(b) FROM t1 GROUP BY a,b WITH ROLLUP;
|
|
--echo
|
|
--sorted_result
|
|
SELECT DISTINCT a, SUM(b) FROM t1 GROUP BY a,b WITH ROLLUP;
|
|
--echo
|
|
--sorted_result
|
|
SELECT b, a, SUM(b) FROM t1 GROUP BY a,b WITH ROLLUP;
|
|
--echo
|
|
--sorted_result
|
|
SELECT DISTINCT b,a, SUM(b) FROM t1 GROUP BY a,b WITH ROLLUP;
|
|
--echo
|
|
ALTER TABLE t1 ADD COLUMN c INT;
|
|
--echo
|
|
--sorted_result
|
|
SELECT a,b,SUM(c) FROM t1 GROUP BY a,b,c WITH ROLLUP;
|
|
--echo
|
|
--sorted_result
|
|
SELECT distinct a,b,SUM(c) FROM t1 GROUP BY a,b,c WITH ROLLUP;
|
|
|
|
DROP TABLE t1;
|
|
|
|
# Test Issue 56218
|
|
# Test with normal constant case
|
|
|
|
--echo
|
|
SELECT
|
|
(SELECT 100.00
|
|
FROM
|
|
(SELECT '2024-09-15' AS DATE ) newTb
|
|
WHERE T0.DATE = DATE_ADD(newTb.DATE, INTERVAL 1 MONTH)) AS 'PROFIT'
|
|
FROM
|
|
(SELECT '2024-09-15' AS DATE) T0
|
|
GROUP BY T0.DATE WITH ROLLUP;
|
|
|
|
--echo
|
|
EXPLAIN SELECT
|
|
(SELECT 100.00
|
|
FROM
|
|
(SELECT '2024-09-15' AS DATE ) newTb
|
|
WHERE T0.DATE = DATE_ADD(newTb.DATE, INTERVAL 1 MONTH)) AS 'PROFIT'
|
|
FROM
|
|
(SELECT '2024-09-15' AS DATE) T0
|
|
GROUP BY T0.DATE WITH ROLLUP;
|
|
|
|
# Test with real correlated column case
|
|
drop table if exists tr;
|
|
create table tr(a date);
|
|
insert into tr values('2024-09-15');
|
|
|
|
--echo
|
|
SELECT
|
|
(SELECT 100.00
|
|
FROM (SELECT '2024-09-15' AS DATE ) newTb
|
|
WHERE T0.DATE = DATE_ADD(newTb.DATE, INTERVAL 0 MONTH)
|
|
) AS 'PROFIT'
|
|
FROM (select tr.a as DATE from tr) T0
|
|
GROUP BY T0.DATE WITH ROLLUP;
|
|
|
|
--echo
|
|
EXPLAIN SELECT
|
|
(SELECT 100.00
|
|
FROM (SELECT '2024-09-15' AS DATE ) newTb
|
|
WHERE T0.DATE = DATE_ADD(newTb.DATE, INTERVAL 0 MONTH)
|
|
) AS 'PROFIT'
|
|
FROM (select tr.a as DATE from tr) T0
|
|
GROUP BY T0.DATE WITH ROLLUP;
|