mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-06-09 06:41:19 +03:00
11324c468d
The fix is simple: enable subtotals in single-phase aggregation and disable parallel processing when there are subtotals and aggregation is single-phase.
96 lines
3.4 KiB
Plaintext
96 lines
3.4 KiB
Plaintext
DROP DATABASE IF EXISTS mcs84_db;
|
|
CREATE DATABASE mcs84_db;
|
|
USE mcs84_db;
|
|
CREATE TABLE booksales (
|
|
country CHAR(35), genre CHAR(15), year INT, sales INT)
|
|
ENGINE=Columnstore;
|
|
INSERT INTO booksales VALUES
|
|
('Senegal','fiction',2014,12234), ('Senegal','fiction',2015,15647),
|
|
('Senegal','non-fiction',2014,64980), ('Senegal','non-fiction',2015,78901),
|
|
('Paraguay','fiction',2014,87970), ('Paraguay','fiction',2015,76940),
|
|
('Paraguay','non-fiction',2014,8760), ('Paraguay','non-fiction',2015,9030);
|
|
SELECT year, SUM(sales) FROM booksales GROUP BY year ORDER BY year;
|
|
year SUM(sales)
|
|
2014 173944
|
|
2015 180518
|
|
SELECT year, SUM(sales) FROM booksales GROUP BY year WITH ROLLUP ORDER BY year;
|
|
ERROR HY000: Incorrect usage of CUBE/ROLLUP and ORDER BY
|
|
SELECT year, sales, MAX(country) FROM booksales GROUP BY year, sales;
|
|
year sales MAX(country)
|
|
2014 12234 Senegal
|
|
2014 64980 Senegal
|
|
2014 8760 Paraguay
|
|
2014 87970 Paraguay
|
|
2015 15647 Senegal
|
|
2015 76940 Paraguay
|
|
2015 78901 Senegal
|
|
2015 9030 Paraguay
|
|
SELECT year, MAX(country) FROM booksales GROUP BY year;
|
|
year MAX(country)
|
|
2014 Senegal
|
|
2015 Senegal
|
|
SELECT year, sales, MAX(country) FROM booksales GROUP BY year, sales WITH ROLLUP;
|
|
year sales MAX(country)
|
|
2014 12234 Senegal
|
|
2014 64980 Senegal
|
|
2014 8760 Paraguay
|
|
2014 87970 Paraguay
|
|
2014 NULL Senegal
|
|
2015 15647 Senegal
|
|
2015 76940 Paraguay
|
|
2015 78901 Senegal
|
|
2015 9030 Paraguay
|
|
2015 NULL Senegal
|
|
NULL NULL Senegal
|
|
SELECT year, SUM(sales) FROM booksales GROUP BY year WITH ROLLUP;
|
|
year SUM(sales)
|
|
2014 173944
|
|
2015 180518
|
|
NULL 354462
|
|
SELECT country, genre, SUM(sales) FROM booksales GROUP BY country, genre WITH ROLLUP;
|
|
country genre SUM(sales)
|
|
NULL NULL 354462
|
|
Paraguay NULL 182700
|
|
Paraguay fiction 164910
|
|
Paraguay non-fiction 17790
|
|
Senegal NULL 171762
|
|
Senegal fiction 27881
|
|
Senegal non-fiction 143881
|
|
SELECT country, genre, SUM(sales) FROM (SELECT country, genre, sales FROM booksales) t1 GROUP BY country, genre WITH ROLLUP;
|
|
country genre SUM(sales)
|
|
NULL NULL 354462
|
|
Paraguay NULL 182700
|
|
Paraguay fiction 164910
|
|
Paraguay non-fiction 17790
|
|
Senegal NULL 171762
|
|
Senegal fiction 27881
|
|
Senegal non-fiction 143881
|
|
CREATE TABLE three_cols ( key1 INTEGER, key2 INTEGER, value DECIMAL(38)) ENGINE=COLUMNSTORE;
|
|
INSERT INTO three_cols(key1, key2, value) VALUES
|
|
(NULL, NULL, NULL)
|
|
, (NULL, NULL, 1)
|
|
, (NULL, 1, 2)
|
|
, ( 1, 1, 3)
|
|
, ( 1, 2, 4)
|
|
, ( 1, 2, 5)
|
|
, ( 2, 3, 6)
|
|
, ( 2, 3, 7);
|
|
SELECT key1, key2, SUM(value), AVG(value), MIN(value), MAX(value), COUNT(value), COUNT(*) FROM three_cols GROUP BY key1, key2 WITH ROLLUP;
|
|
key1 key2 SUM(value) AVG(value) MIN(value) MAX(value) COUNT(value) COUNT(*)
|
|
1 1 3 3.0000 3 3 1 1
|
|
1 2 9 4.5000 4 5 2 2
|
|
1 NULL 12 4.0000 3 5 3 3
|
|
2 3 13 6.5000 6 7 2 2
|
|
2 NULL 13 6.5000 6 7 2 2
|
|
NULL 1 2 2.0000 2 2 1 1
|
|
NULL NULL 1 1.0000 1 1 1 2
|
|
NULL NULL 28 4.0000 1 7 7 8
|
|
NULL NULL 3 1.5000 1 2 2 3
|
|
SELECT key1, key2, GROUP_CONCAT(value) FROM three_cols GROUP BY key1, key2 WITH ROLLUP;
|
|
ERROR HY000: Internal error: GROUP_CONCAT and JSONARRAYAGG aggregations are not supported when WITH ROLLUP modifier is used
|
|
SELECT key1, key2, JSON_ARRAYAGG(value) FROM three_cols GROUP BY key1, key2 WITH ROLLUP;
|
|
ERROR HY000: Internal error: GROUP_CONCAT and JSONARRAYAGG aggregations are not supported when WITH ROLLUP modifier is used
|
|
SELECT 100, SUM(value) FROM three_cols GROUP BY 1 WITH ROLLUP;
|
|
ERROR HY000: Internal error: constant GROUP BY columns are not supported when WITH ROLLUP is used
|
|
DROP DATABASE IF EXISTS mcs84_db;
|