1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
|
# name: test/sql/collate/test_collate_and_grouping_sets.test
# description: Test collation and grouping sets.
# group: [collate]
require icu
statement ok
set default_collation=c;
statement ok
CREATE TABLE sales (
product_id INT,
region VARCHAR(50),
year INT,
amount_sold DECIMAL(10,2)
);
statement ok
INSERT INTO sales VALUES
(1, 'North', 2020, 1000.00),
(1, 'North', 2021, 1500.00),
(1, 'South', 2020, 800.00),
(1, 'South', 2021, 700.00),
(2, 'North', 2020, 500.00),
(2, 'North', 2021, 600.00),
(2, 'South', 2020, 400.00),
(2, 'South', 2021, 550.00);
statement ok
set default_collation=c;
query III nosort grouping_sets_collation_result
SELECT product_id, region, SUM(amount_sold) AS total_amount
FROM sales
GROUP BY GROUPING SETS ((product_id), (region), ())
ORDER BY product_id, region, total_amount;
statement ok
set default_collation=en_us;
query III nosort grouping_sets_collation_result
SELECT product_id, region, SUM(amount_sold) AS total_amount
FROM sales
GROUP BY GROUPING SETS ((product_id), (region), ())
ORDER BY product_id, region, total_amount;
statement ok
set default_collation=c
query III nosort union_groups_collation_result
select NULL product_id, region, sum(amount_sold) from sales group by region
UNION ALL
select NULL product_id, NULL region, sum(amount_sold) from sales
UNION ALL
select product_id, NULL region, sum(amount_sold) from sales group by product_id order by 1,2;
statement ok
set default_collation=en_us;
query III nosort union_groups_collation_result
select NULL product_id, region, sum(amount_sold) from sales group by region
UNION ALL
select NULL product_id, NULL region, sum(amount_sold) from sales
UNION ALL
select product_id, NULL region, sum(amount_sold) from sales group by product_id order by 1,2;
# also test that union all is the same as using grouping sets
query III nosort grouping_sets_collation
SELECT product_id, region, SUM(amount_sold) AS total_amount
FROM sales
GROUP BY GROUPING SETS ((product_id), (region), ())
ORDER BY product_id, region, total_amount;
query III nosort grouping_sets_collation
select NULL product_id, region, sum(amount_sold) from sales group by region
UNION ALL
select NULL product_id, NULL region, sum(amount_sold) from sales
UNION ALL
select product_id, NULL region, sum(amount_sold) from sales group by product_id order by 1,2;
|
