Changeset: 3cc0ed9dd490 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=3cc0ed9dd490
Modified Files:
sql/server/rel_select.c
sql/test/analytics/Tests/analytics10.sql
sql/test/analytics/Tests/analytics10.stable.out
Branch: grouping-analytics
Log Message:
Grouping sets fix and tests
diffs (200 lines):
diff --git a/sql/server/rel_select.c b/sql/server/rel_select.c
--- a/sql/server/rel_select.c
+++ b/sql/server/rel_select.c
@@ -4562,10 +4562,14 @@ rel_groupings(sql_query *query, sql_rel
for (dnode *o = groupby->data.lval->h; o; o = o->next) {
symbol *grouping = o->data.sym;
if (grouping->token == SQL_GROUPING_SETS) {
- list *other = rel_groupings(query, rel, grouping,
selection, f, combined_totals, true, sets);
+ list *nsets = NULL, *other = rel_groupings(query, rel,
grouping, selection, f, combined_totals, true, &nsets);
if (!other)
return NULL;
- exps = list_merge(exps, other, (fdup) NULL);
+ exps = list_distinct(list_merge(exps, other, (fdup)
NULL), (fcmp) exp_equal, (fdup) NULL);
+ if (!*sets)
+ *sets = nsets;
+ else
+ *sets = grouping_sets ? list_merge(*sets,
nsets, (fdup) NULL) : lists_cartesian_product_and_distinct(sql->sa, *sets,
nsets);
} else {
dlist *dl = grouping->data.lval;
if (dl) {
@@ -6372,7 +6376,7 @@ rel_select_exp(sql_query *query, sql_rel
l = inner;
}
- /* ROLLUP and CUBE cases */
+ /* ROLLUP, CUBE, GROUPING SETS cases */
if (sets) {
sql_rel *unions = NULL;
list *group_exps = list_dup(group->exps, (fdup)NULL);
diff --git a/sql/test/analytics/Tests/analytics10.sql
b/sql/test/analytics/Tests/analytics10.sql
--- a/sql/test/analytics/Tests/analytics10.sql
+++ b/sql/test/analytics/Tests/analytics10.sql
@@ -197,6 +197,26 @@ GROUP BY GROUPING SETS((Product_Category
SELECT
CAST(SUM(TotalSales) as BIGINT) AS TotalSales
FROM tbl_ProductSales
+GROUP BY ColID, GROUPING SETS ((Product_Name), (Product_Category));
+
+SELECT
+ CAST(SUM(TotalSales) as BIGINT) AS TotalSales
+FROM tbl_ProductSales
GROUP BY ColID, CUBE (Product_Category, ColID), GROUPING SETS ((Product_Name),
(Product_Category));
+SELECT
+ CAST(SUM(TotalSales) as BIGINT) AS TotalSales
+FROM tbl_ProductSales
+GROUP BY GROUPING SETS(CUBE(Product_Category, Product_Name), ROLLUP(ColID,
Product_Name));
+
+SELECT
+ CAST(SUM(TotalSales) as BIGINT) AS TotalSales
+FROM tbl_ProductSales
+GROUP BY ColID, GROUPING SETS (GROUPING SETS (()), (Product_Name),
(Product_Category));
+
+SELECT
+ CAST(SUM(TotalSales) as BIGINT) AS TotalSales
+FROM tbl_ProductSales
+GROUP BY GROUPING SETS (ROLLUP(ColID), (), GROUPING SETS ((Product_Category,
Product_Name), CUBE(ColID), ColID));
+
DROP TABLE tbl_ProductSales;
diff --git a/sql/test/analytics/Tests/analytics10.stable.out
b/sql/test/analytics/Tests/analytics10.stable.out
--- a/sql/test/analytics/Tests/analytics10.stable.out
+++ b/sql/test/analytics/Tests/analytics10.stable.out
@@ -621,6 +621,137 @@ stdout of test 'analytics10` in director
[ 600 ]
[ 600 ]
[ 600 ]
+#SELECT
+# CAST(SUM(TotalSales) as BIGINT) AS TotalSales
+#FROM tbl_ProductSales
+#GROUP BY ColID, GROUPING SETS ((Product_Name), (Product_Category));
+% .L3 # table_name
+% totalsales # name
+% bigint # type
+% 3 # length
+[ 200 ]
+[ 400 ]
+[ 500 ]
+[ 100 ]
+[ 200 ]
+[ 400 ]
+[ 500 ]
+[ 100 ]
+#SELECT
+# CAST(SUM(TotalSales) as BIGINT) AS TotalSales
+#FROM tbl_ProductSales
+#GROUP BY ColID, CUBE (Product_Category, ColID), GROUPING SETS
((Product_Name), (Product_Category));
+% .L3 # table_name
+% totalsales # name
+% bigint # type
+% 3 # length
+[ 200 ]
+[ 400 ]
+[ 500 ]
+[ 100 ]
+[ 200 ]
+[ 400 ]
+[ 500 ]
+[ 100 ]
+[ 200 ]
+[ 400 ]
+[ 500 ]
+[ 100 ]
+[ 200 ]
+[ 400 ]
+[ 500 ]
+[ 100 ]
+[ 200 ]
+[ 400 ]
+[ 500 ]
+[ 100 ]
+[ 200 ]
+[ 400 ]
+[ 500 ]
+[ 100 ]
+[ 200 ]
+[ 400 ]
+[ 500 ]
+[ 100 ]
+[ 200 ]
+[ 400 ]
+[ 500 ]
+[ 100 ]
+#SELECT
+# CAST(SUM(TotalSales) as BIGINT) AS TotalSales
+#FROM tbl_ProductSales
+#GROUP BY GROUPING SETS(CUBE(Product_Category, Product_Name), ROLLUP(ColID,
Product_Name));
+% .L3 # table_name
+% totalsales # name
+% bigint # type
+% 4 # length
+[ 1200 ]
+[ 600 ]
+[ 600 ]
+[ 200 ]
+[ 400 ]
+[ 500 ]
+[ 100 ]
+[ 200 ]
+[ 400 ]
+[ 500 ]
+[ 100 ]
+[ 200 ]
+[ 400 ]
+[ 500 ]
+[ 100 ]
+[ 200 ]
+[ 400 ]
+[ 500 ]
+[ 100 ]
+[ 1200 ]
+#SELECT
+# CAST(SUM(TotalSales) as BIGINT) AS TotalSales
+#FROM tbl_ProductSales
+#GROUP BY ColID, GROUPING SETS (GROUPING SETS (()), (Product_Name),
(Product_Category));
+% .L3 # table_name
+% totalsales # name
+% bigint # type
+% 3 # length
+[ 200 ]
+[ 400 ]
+[ 500 ]
+[ 100 ]
+[ 200 ]
+[ 400 ]
+[ 500 ]
+[ 100 ]
+[ 200 ]
+[ 400 ]
+[ 500 ]
+[ 100 ]
+#SELECT
+# CAST(SUM(TotalSales) as BIGINT) AS TotalSales
+#FROM tbl_ProductSales
+#GROUP BY GROUPING SETS (ROLLUP(ColID), (), GROUPING SETS ((Product_Category,
Product_Name), CUBE(ColID), ColID));
+% .L3 # table_name
+% totalsales # name
+% bigint # type
+% 4 # length
+[ 200 ]
+[ 400 ]
+[ 500 ]
+[ 100 ]
+[ 1200 ]
+[ 1200 ]
+[ 200 ]
+[ 400 ]
+[ 500 ]
+[ 100 ]
+[ 1200 ]
+[ 200 ]
+[ 400 ]
+[ 500 ]
+[ 100 ]
+[ 200 ]
+[ 400 ]
+[ 500 ]
+[ 100 ]
#DROP TABLE tbl_ProductSales;
# 11:30:14 >
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
