Changeset: f30ce02a6bde for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=f30ce02a6bde
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:
Delay generation of subgroups for rollup/cubes/groupingsets to after ORDER BY
clause compilation, because ORDER BY might introduce new aggregates to be
propagated
diffs (161 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
@@ -4586,6 +4586,7 @@ rel_groupings(sql_query *query, sql_rel
sql_exp *e =
rel_group_column(query, rel, elm, selection, f);
if (!e)
return NULL;
+ assert(e->type ==
e_column);
list_append(elements,
e);
list_append(exps, e);
}
@@ -4624,8 +4625,7 @@ rel_groupings(sql_query *query, sql_rel
}
} else if (combined_totals && (grouping->token
== SQL_GROUPBY)) { /* the list of sets is not used in the "GROUP BY a, b, ..."
case */
if (!*sets) {
- *sets = new_exp_list(sql->sa);
- list_append(*sets, set_exps);
+ *sets =
list_append(new_exp_list(sql->sa), set_exps);
} else {
list *new_set =
list_append(new_exp_list(sql->sa), set_exps);
*sets = grouping_sets ?
list_merge(*sets, new_set, (fdup) NULL) :
lists_cartesian_product_and_distinct(sql->sa, *sets, new_set);
@@ -6246,7 +6246,7 @@ rel_select_exp(sql_query *query, sql_rel
mvc *sql = query->sql;
dnode *n;
//int aggr = 0;
- sql_rel *inner = NULL, *group, *l;
+ sql_rel *inner = NULL, *group, *left;
list *sets = NULL;
assert(sn->s.token == SQL_SELECT);
@@ -6357,7 +6357,7 @@ rel_select_exp(sql_query *query, sql_rel
}
group = rel->l;
- l = rel;
+ left = rel;
if (sn->having) {
inner = rel->l;
@@ -6373,8 +6373,24 @@ rel_select_exp(sql_query *query, sql_rel
return sql_error(sql, 02, SQLSTATE(42000) "SELECT:
cannot compare sets with values, probably an aggregate function missing");
rel->l = inner;
group = inner->l;
- l = inner;
- }
+ left = inner;
+ }
+
+ if (rel && sn->orderby) {
+ list *obe = NULL;
+
+ set_processed(rel);
+ rel = rel_orderby(sql, rel);
+ obe = rel_order_by(query, &rel, sn->orderby, sql_orderby);
+ 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) {
@@ -6414,25 +6430,12 @@ rel_select_exp(sql_query *query, sql_rel
if (!unions)
return unions;
}
- l->l = unions;
+ left->l = unions;
}
if (rel && sn->distinct)
rel = rel_distinct(rel);
- if (rel && sn->orderby) {
- list *obe = NULL;
-
- set_processed(rel);
- rel = rel_orderby(sql, rel);
- obe = rel_order_by(query, &rel, sn->orderby, sql_orderby);
- if (!obe)
- return NULL;
- rel->r = obe;
- }
- if (!rel)
- return NULL;
-
if (sn->limit || sn->offset) {
sql_subtype *lng = sql_bind_localtype("lng");
list *exps = new_exp_list(sql->sa);
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
@@ -219,4 +219,20 @@ SELECT
FROM tbl_ProductSales
GROUP BY GROUPING SETS (ROLLUP(ColID), (), GROUPING SETS ((Product_Category,
Product_Name), CUBE(ColID), ColID));
+SELECT
+ CAST(SUM(TotalSales) as BIGINT) AS TotalSales
+FROM tbl_ProductSales GROUP BY ROLLUP (Product_Category, ColID)
+ORDER BY SUM(TotalSales);
+
+SELECT
+ CAST(SUM(TotalSales) as BIGINT) AS TotalSales
+FROM tbl_ProductSales GROUP BY ROLLUP (Product_Category, ColID)
+HAVING SUM(TotalSales) > 600;
+
+SELECT
+ CAST(SUM(TotalSales) as BIGINT) AS TotalSales
+FROM tbl_ProductSales GROUP BY ROLLUP (Product_Category, ColID)
+HAVING SUM(TotalSales) > 600
+ORDER BY AVG(TotalSales);
+
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
@@ -752,6 +752,40 @@ stdout of test 'analytics10` in director
[ 400 ]
[ 500 ]
[ 100 ]
+#SELECT
+# CAST(SUM(TotalSales) as BIGINT) AS TotalSales
+#FROM tbl_ProductSales GROUP BY ROLLUP (Product_Category, ColID)
+#ORDER BY SUM(TotalSales);
+% .L3 # table_name
+% totalsales # name
+% bigint # type
+% 4 # length
+[ 100 ]
+[ 200 ]
+[ 400 ]
+[ 500 ]
+[ 600 ]
+[ 600 ]
+[ 1200 ]
+#SELECT
+# CAST(SUM(TotalSales) as BIGINT) AS TotalSales
+#FROM tbl_ProductSales GROUP BY ROLLUP (Product_Category, ColID)
+#HAVING SUM(TotalSales) > 600;
+% .L3 # table_name
+% totalsales # name
+% bigint # type
+% 4 # length
+[ 1200 ]
+#SELECT
+# CAST(SUM(TotalSales) as BIGINT) AS TotalSales
+#FROM tbl_ProductSales GROUP BY ROLLUP (Product_Category, ColID)
+#HAVING SUM(TotalSales) > 600
+#ORDER BY AVG(TotalSales);
+% .L3 # table_name
+% totalsales # name
+% bigint # type
+% 4 # length
+[ 1200 ]
#DROP TABLE tbl_ProductSales;
# 11:30:14 >
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
