Changeset: 8c8941b18109 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=8c8941b18109
Modified Files:
sql/server/rel_select.c
sql/test/analytics/Tests/analytics11.sql
sql/test/analytics/Tests/analytics11.stable.out
Branch: grouping-analytics
Log Message:
More defensive way to find underlying grouping aggregation
diffs (151 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
@@ -6273,7 +6273,7 @@ rel_select_exp(sql_query *query, sql_rel
mvc *sql = query->sql;
dnode *n;
//int aggr = 0;
- sql_rel *inner = NULL, *group, *left;
+ sql_rel *inner = NULL;
list *sets = NULL;
int group_totals = 0;
@@ -6383,9 +6383,6 @@ rel_select_exp(sql_query *query, sql_rel
list_merge( rel->exps, te, (fdup)NULL);
}
- group = rel->l;
- left = rel;
-
if (sn->having) {
inner = rel->l;
assert(is_project(rel->op) && inner);
@@ -6399,8 +6396,6 @@ rel_select_exp(sql_query *query, sql_rel
if (inner -> exps && exps_card(inner->exps) > CARD_AGGR)
return sql_error(sql, 02, SQLSTATE(42000) "SELECT:
cannot compare sets with values, probably an aggregate function missing");
rel->l = inner;
- group = inner->l;
- left = inner;
}
if (rel && sn->orderby) {
@@ -6412,17 +6407,18 @@ rel_select_exp(sql_query *query, sql_rel
if (!obe)
return NULL;
rel->r = obe;
-
- if (!is_select(left->op)) /* if the rollup query has a having
clause, it's no longer needed to update left */
- left = rel->l;
}
if (!rel)
return NULL;
/* ROLLUP, CUBE, GROUPING SETS cases */
if (sets && list_length(sets) > 1) { /* if there is only one
combination, there is no reason to generate unions */
- sql_rel *unions = NULL;
-
+ sql_rel *unions = NULL, *group = rel->l, *left = rel;
+
+ while (!is_groupby(group->op)) {
+ left = group;
+ group = group->l;
+ }
for (node *n = sets->h ; n ; n = n->next) {
sql_rel *nrel;
list *l = (list*) n->data, *exps = sa_list(sql->sa),
*pexps = sa_list(sql->sa);
diff --git a/sql/test/analytics/Tests/analytics11.sql
b/sql/test/analytics/Tests/analytics11.sql
--- a/sql/test/analytics/Tests/analytics11.sql
+++ b/sql/test/analytics/Tests/analytics11.sql
@@ -115,4 +115,19 @@ GROUP BY ROLLUP(Product_Category, Produc
HAVING GROUPING(Product_Category, Product_Name, ColID) <> 3
ORDER BY GROUPING(Product_Category, Product_Name, ColID) DESC;
+SELECT
+ GROUPING(Product_Category), AVG(SUM(TotalSales)) OVER (ROWS UNBOUNDED
PRECEDING)
+FROM tbl_ProductSales
+GROUP BY GROUPING SETS((Product_Category), (Product_Name), (Product_Category,
Product_Name), ());
+
+SELECT
+ GROUPING(Product_Category), RANK() OVER (PARTITION BY SUM(TotalSales))
+FROM tbl_ProductSales
+GROUP BY GROUPING SETS((Product_Category), (Product_Name), (Product_Category,
Product_Name), ());
+
+SELECT
+ CASE WHEN GROUPING(Product_Category, Product_Name, ColID) * 10 = 30 THEN 2
ELSE NULL END
+FROM tbl_ProductSales
+GROUP BY ROLLUP(Product_Category, Product_Name, ColID);
+
DROP TABLE tbl_ProductSales;
diff --git a/sql/test/analytics/Tests/analytics11.stable.out
b/sql/test/analytics/Tests/analytics11.stable.out
--- a/sql/test/analytics/Tests/analytics11.stable.out
+++ b/sql/test/analytics/Tests/analytics11.stable.out
@@ -173,7 +173,7 @@ stdout of test 'analytics11` in director
[ 0, 0 ]
[ 7, 3 ]
#SELECT
-# GROUPING(Product_Category, Product_Name, ColID)
+# GROUPING(Product_Category, Product_Name, ColID) + 1
#FROM tbl_ProductSales
#GROUP BY ROLLUP(Product_Category, Product_Name, ColID)
#HAVING GROUPING(Product_Category, Product_Name, ColID) <> 3
@@ -191,6 +191,60 @@ stdout of test 'analytics11` in director
[ 1 ]
[ 1 ]
[ 1 ]
+#SELECT GROUPING(Product_Category), AVG(SUM(TotalSales)) OVER (ROWS UNBOUNDED
PRECEDING) FROM tbl_ProductSales GROUP BY GROUPING SETS((Product_Category),
(Product_Name), (Product_Category, Product_Name), ());
+% .L1, .L3 # table_name
+% L1, L3 # name
+% tinyint, double # type
+% 1, 24 # length
+[ 0, 600 ]
+[ 0, 600 ]
+[ 1, 466.6666667 ]
+[ 1, 450 ]
+[ 1, 460 ]
+[ 1, 400 ]
+[ 0, 371.4285714 ]
+[ 0, 375 ]
+[ 0, 388.8888889 ]
+[ 0, 360 ]
+[ 1, 436.3636364 ]
+#SELECT
+# GROUPING(Product_Category), RANK() OVER (PARTITION BY SUM(TotalSales))
+#FROM tbl_ProductSales
+#GROUP BY GROUPING SETS((Product_Category), (Product_Name), (Product_Category,
Product_Name), ());
+% .L1, .L4 # table_name
+% L1, L4 # name
+% tinyint, int # type
+% 1, 1 # length
+[ 1, 1 ]
+[ 0, 1 ]
+[ 1, 1 ]
+[ 0, 1 ]
+[ 1, 1 ]
+[ 0, 1 ]
+[ 1, 1 ]
+[ 0, 1 ]
+[ 0, 1 ]
+[ 0, 1 ]
+[ 1, 1 ]
+#SELECT
+# CASE WHEN GROUPING(Product_Category, Product_Name, ColID) * 10 = 30 THEN
2 ELSE NULL END
+#FROM tbl_ProductSales
+#GROUP BY ROLLUP(Product_Category, Product_Name, ColID);
+% .L2 # table_name
+% L2 # name
+% tinyint # type
+% 1 # length
+[ NULL ]
+[ NULL ]
+[ NULL ]
+[ NULL ]
+[ NULL ]
+[ NULL ]
+[ NULL ]
+[ NULL ]
+[ 2 ]
+[ 2 ]
+[ NULL ]
#DROP TABLE tbl_ProductSales;
# 14:50:27 >
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
