Changeset: 49f7fabfb8df for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=49f7fabfb8df
Modified Files:
sql/common/sql_types.c
sql/common/sql_types.h
sql/include/sql_relation.h
sql/server/rel_optimizer.c
sql/server/rel_rel.h
sql/server/rel_select.c
sql/test/analytics/Tests/analytics11.sql
sql/test/analytics/Tests/analytics11.stable.err
sql/test/analytics/Tests/analytics11.stable.out
Branch: grouping-analytics
Log Message:
Working grouping aggregate calls.
The aggregate is replaced with constants during the unions generation. Also we
won't allow it to be called without ROLLUP, CUBE and GROUPING SETS clauses,
otherwise its output would be always 0, which is pointless.
diffs (truncated from 848 to 300 lines):
diff --git a/sql/common/sql_types.c b/sql/common/sql_types.c
--- a/sql/common/sql_types.c
+++ b/sql/common/sql_types.c
@@ -535,19 +535,19 @@ sql_bind_aggr(sql_allocator *sa, sql_sch
}
sql_subaggr *
-sql_bind_aggr_(sql_allocator *sa, sql_schema *s, const char *sqlaname, list
*ops)
+sql_bind_aggr_(sql_allocator *sa, sql_schema *s, const char *sqlaname, list
*inputs, bool args)
{
node *n = aggrs->h;
sql_subtype *type = NULL;
- if (ops->h)
- type = ops->h->data;
+ if (inputs->h)
+ type = inputs->h->data;
while (n) {
sql_func *a = n->data;
if (strcmp(a->base.name, sqlaname) == 0 &&
- list_cmp(a->ops, ops, (fcmp) &arg_subtype_cmp) == 0)
+ list_cmp(args ? a->ops : a->res, inputs, (fcmp)
&arg_subtype_cmp) == 0)
return _dup_subaggr(sa, a, type);
n = n->next;
}
@@ -561,7 +561,7 @@ sql_bind_aggr_(sql_allocator *sa, sql_sc
continue;
if (strcmp(a->base.name, sqlaname) == 0 &&
- list_cmp(a->ops, ops, (fcmp) &arg_subtype_cmp) == 0)
+ list_cmp(args ? a->ops : a->res, inputs, (fcmp)
&arg_subtype_cmp) == 0)
return _dup_subaggr(sa, a, type);
}
}
@@ -1535,7 +1535,13 @@ sqltypeinit( sql_allocator *sa)
*t = NULL;
// sql_create_func(sa, "st_pointfromtext", "geom", "st_pointformtext",
OID, NULL, OID, SCALE_FIX);
- sql_create_aggr(sa, "grouping", "sql", "grouping", ANY, ANY);
+ sql_create_aggr(sa, "grouping", "sql", "grouping", ANY, BTE);
+ sql_create_aggr(sa, "grouping", "sql", "grouping", ANY, SHT);
+ sql_create_aggr(sa, "grouping", "sql", "grouping", ANY, INT);
+ sql_create_aggr(sa, "grouping", "sql", "grouping", ANY, LNG);
+#ifdef HAVE_HGE
+ sql_create_aggr(sa, "grouping", "sql", "grouping", ANY, HGE);
+#endif
sql_create_aggr(sa, "not_unique", "sql", "not_unique", OID, BIT);
/* well to be precise it does reduce and map */
diff --git a/sql/common/sql_types.h b/sql/common/sql_types.h
--- a/sql/common/sql_types.h
+++ b/sql/common/sql_types.h
@@ -49,7 +49,7 @@ extern sql_arg *sql_create_arg(sql_alloc
extern sql_arg *arg_dup(sql_allocator *sa, sql_arg *a);
extern sql_subaggr *sql_bind_aggr(sql_allocator *sa, sql_schema *s, const char
*name, sql_subtype *type);
-extern sql_subaggr *sql_bind_aggr_(sql_allocator *sa, sql_schema *s, const
char *name, list *types);
+extern sql_subaggr *sql_bind_aggr_(sql_allocator *sa, sql_schema *s, const
char *name, list *types, bool args);
extern sql_subaggr *sql_bind_member_aggr(sql_allocator *sa, sql_schema *s,
const char *name, sql_subtype *tp, int nrargs);
extern sql_subaggr *sql_find_aggr(sql_allocator *sa, sql_schema *s, const char
*name);
extern int subaggr_cmp( sql_subaggr *a1, sql_subaggr *a2);
diff --git a/sql/include/sql_relation.h b/sql/include/sql_relation.h
--- a/sql/include/sql_relation.h
+++ b/sql/include/sql_relation.h
@@ -62,6 +62,7 @@ typedef struct expression {
#define get_cmp(e) (e->flag&CMPMASK)
#define HAS_NO_NIL 32
#define NULLS_LAST 64
+#define GROUPING_TOTALS 128
#define UPD_COMP 1
#define UPD_LOCKED 2
@@ -277,6 +278,11 @@ typedef enum operator_type {
#define set_nodistinct(e) \
e->flag &= (~EXP_DISTINCT)
+#define set_grouping_totals(e) \
+ e->flag |= GROUPING_TOTALS
+#define is_grouping_totals(e) \
+ ((e->flag&GROUPING_TOTALS)==GROUPING_TOTALS)
+
#define is_processed(rel) \
((rel)->processed)
#define set_processed(rel) \
diff --git a/sql/server/rel_optimizer.c b/sql/server/rel_optimizer.c
--- a/sql/server/rel_optimizer.c
+++ b/sql/server/rel_optimizer.c
@@ -1456,7 +1456,7 @@ project_unsafe(sql_rel *rel, int allow_i
sql_rel *sub = rel->l;
node *n;
- if (need_distinct(rel) || rel->r /* order by */)
+ if (need_distinct(rel) || rel->r /* order by */ ||
is_grouping_totals(rel))
return 1;
if (!rel->exps)
return 0;
diff --git a/sql/server/rel_rel.h b/sql/server/rel_rel.h
--- a/sql/server/rel_rel.h
+++ b/sql/server/rel_rel.h
@@ -21,6 +21,7 @@
#define sql_partitionby 64 //ORed
#define sql_aggr 128 //ORed
#define sql_farg 256 //ORed
+#define sql_group_totals 512 //ORed
#define is_sql_from(X) ((X & sql_from) == sql_from)
#define is_sql_where(X) ((X & sql_where) == sql_where)
@@ -31,6 +32,7 @@
#define is_sql_partitionby(X) ((X & sql_partitionby) == sql_partitionby)
#define is_sql_aggr(X) ((X & sql_aggr) == sql_aggr)
#define is_sql_farg(X) ((X & sql_farg) == sql_farg)
+#define is_sql_group_totals(X) ((X & sql_group_totals) == sql_group_totals)
#define ERR_AMBIGUOUS 050000
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
@@ -3905,7 +3905,7 @@ static sql_exp *
if (uaname)
GDKfree(uaname);
return NULL;
- } else if(is_sql_groupby(f) || (is_sql_partitionby(f) && groupby->op !=
op_groupby)) {
+ } else if (is_sql_groupby(f) || (is_sql_partitionby(f) && groupby->op
!= op_groupby)) {
const char *clause = is_sql_groupby(f) ? "GROUP BY":"PARTITION
BY";
char *uaname = GDKmalloc(strlen(aname) + 1);
(void) sql_error(sql, 02, SQLSTATE(42000) "%s: aggregate
function '%s' not allowed in %s clause",
@@ -3913,7 +3913,8 @@ static sql_exp *
if (uaname)
GDKfree(uaname);
return NULL;
- }
+ } else if (is_grouping && !is_sql_group_totals(f))
+ return sql_error(sql, 02, SQLSTATE(42000) "GROUPING aggregate
function requires ROLLUP, CUBE or GROUPING SETS clauses in GROUP BY");
if (groupby->op != op_groupby) { /* implicit groupby */
sql_rel *np = rel_project2groupby(sql, groupby);
@@ -4008,10 +4009,34 @@ static sql_exp *
list_append(exps, e);
}
- if (is_grouping)
- a = sql_bind_aggr(sql->sa, s, aname, NULL);
- else
- a = sql_bind_aggr_(sql->sa, s, aname, exp_types(sql->sa, exps));
+ if (is_grouping) {
+ sql_subtype *tpe;
+ list *l = (list*) groupby->r;
+
+ if (list_length(l) <= 7)
+ tpe = sql_bind_localtype("bte");
+ else if (list_length(l) <= 15)
+ tpe = sql_bind_localtype("sht");
+ else if (list_length(l) <= 31)
+ tpe = sql_bind_localtype("int");
+ else if (list_length(l) <= 63)
+ tpe = sql_bind_localtype("lng");
+#ifdef HAVE_HGE
+ else if (list_length(l) <= 127)
+ tpe = sql_bind_localtype("hge");
+#endif
+ else
+ return sql_error(sql, 02, SQLSTATE(42000) "SELECT:
GROUPING the number of grouping columns is larger"
+ " than the
maximum number of representable bits from this server (%d > %d)",
list_length(l),
+#ifdef HAVE_HGE
+ 127
+#else
+ 63
+#endif
+ );
+ a = sql_bind_aggr_(sql->sa, s, aname,
list_append(sa_list(sql->sa), tpe), false);
+ } else
+ a = sql_bind_aggr_(sql->sa, s, aname, exp_types(sql->sa, exps),
true);
if (!a && list_length(exps) > 1) {
sql_subtype *t1 = exp_subtype(exps->h->data);
@@ -4028,7 +4053,7 @@ static sql_exp *
append(sargs, tstr);
if (list_length(exps) == 2)
append(sargs, tstr);
- a = sql_bind_aggr_(sql->sa, s, aname, sargs);
+ a = sql_bind_aggr_(sql->sa, s, aname, sargs,
true);
}
if (a) {
node *n, *op = a->aggr->ops->h;
@@ -4058,7 +4083,7 @@ static sql_exp *
list_append(tps, t1);
t2 = exp_subtype(r);
list_append(tps, t2);
- a = sql_bind_aggr_(sql->sa, s, aname, tps);
+ a = sql_bind_aggr_(sql->sa, s, aname, tps,
true);
}
if (!a) {
sql->session->status = 0;
@@ -4089,7 +4114,7 @@ static sql_exp *
break;
list_append(nexps, e);
}
- a = sql_bind_aggr_(sql->sa, s, aname, exp_types(sql->sa,
nexps));
+ a = sql_bind_aggr_(sql->sa, s, aname, exp_types(sql->sa,
nexps), true);
if (a && list_length(nexps)) /* count(col) has |exps| !=
|nexps| */
exps = nexps;
if (!a) {
@@ -4562,7 +4587,7 @@ lists_cartesian_product_and_distinct(sql
}
static list*
-rel_groupings(sql_query *query, sql_rel **rel, symbol *groupby, dlist
*selection, int f, bool combined_totals, bool grouping_sets, list **sets)
+rel_groupings(sql_query *query, sql_rel **rel, symbol *groupby, dlist
*selection, int f, bool grouping_sets, list **sets)
{
mvc *sql = query->sql;
list *exps = new_exp_list(sql->sa);
@@ -4575,7 +4600,7 @@ rel_groupings(sql_query *query, sql_rel
list *next_set = NULL;
if (grouping->token == SQL_GROUPING_SETS) { /* call
recursively, and merge the genererated sets */
- list *other = rel_groupings(query, rel, grouping,
selection, f, combined_totals, true, &next_set);
+ list *other = rel_groupings(query, rel, grouping,
selection, f, true, &next_set);
if (!other)
return NULL;
exps = list_distinct(list_merge(exps, other, (fdup)
NULL), (fcmp) exp_equal, (fdup) NULL);
@@ -4589,7 +4614,7 @@ rel_groupings(sql_query *query, sql_rel
list *next_tuple =
new_exp_list(sql->sa); /* next tuple of columns */
if (grp->token == SQL_COLUMN_GROUP) {
/* set of columns */
- assert(combined_totals);
+ assert(is_sql_group_totals(f));
for (dnode *ooo =
grp->data.lval->h; ooo; ooo = ooo->next) {
symbol *elm =
ooo->data.sym;
sql_exp *e =
rel_group_column(query, rel, elm, selection, f);
@@ -4604,7 +4629,7 @@ rel_groupings(sql_query *query, sql_rel
if (!e)
return NULL;
if (e->type != e_column) { /*
store group by expressions in the stack */
- if (combined_totals) {
+ if
(is_sql_group_totals(f)) {
(void)
sql_error(sql, 02, SQLSTATE(42000) "Group by with expressions not possible with
ROLLUP, CUBE, GROUPING SETS");
return NULL;
}
@@ -4616,7 +4641,7 @@ rel_groupings(sql_query *query, sql_rel
}
list_append(set_cols, next_tuple);
}
- if (combined_totals) {
+ if (is_sql_group_totals(f)) {
if (grouping->token == SQL_ROLLUP)
next_set = list_rollup(sql->sa,
set_cols);
else if (grouping->token == SQL_CUBE)
@@ -4624,10 +4649,10 @@ rel_groupings(sql_query *query, sql_rel
else /* the list of sets is not used in
the "GROUP BY a, b, ..." case */
next_set =
list_append(new_exp_list(sql->sa), set_cols);
}
- } else if (combined_totals && grouping_sets) /* The
GROUP BY () case is the global aggregate which is always added by ROLLUP and
CUBE */
+ } else if (is_sql_group_totals(f) && grouping_sets) /*
The GROUP BY () case is the global aggregate which is always added by ROLLUP
and CUBE */
next_set = list_append(new_exp_list(sql->sa),
new_exp_list(sql->sa));
}
- if (combined_totals) { /* if there are no sets, set the found
one, otherwise calculate cartesian product and merge the distinct ones */
+ if (is_sql_group_totals(f)) { /* if there are no sets, set the
found one, otherwise calculate cartesian product and merge the distinct ones */
assert(next_set);
if (!*sets)
*sets = next_set;
@@ -4835,8 +4860,10 @@ rel_order_by_column_exp(sql_query *query
sql_rel *r = *R;
sql_exp *e = NULL;
exp_kind ek = {type_value, card_column, FALSE};
- int added_project = 0;
-
+ int added_project = 0, ff = sql_sel | sql_orderby;
+
+ if (is_sql_group_totals(f))
+ ff |= sql_group_totals;
if (is_sql_orderby(f)) {
assert(is_project(r->op));
r = r->l;
@@ -4851,7 +4878,7 @@ rel_order_by_column_exp(sql_query *query
}
if (!e) {
- e = rel_value_exp(query, &r, column_r, sql_sel | sql_orderby,
ek);
+ e = rel_value_exp(query, &r, column_r, ff, ek);
/* add to internal project */
if (e && is_processed(r) && !is_groupby(r->op)) {
e = rel_project_add_exp(sql, r, e);
@@ -4873,7 +4900,7 @@ rel_order_by_column_exp(sql_query *query
sql->session->status = 0;
sql->errstr[0] = '\0';
- e = rel_value_exp(query, &nr, column_r, sql_sel |
sql_orderby, ek);
+ e = rel_value_exp(query, &nr, column_r, ff, ek);
if (e) {
/* first rewrite e back into current column
names */
e = exp_rewrite(sql, e, nr);
@@ -4917,7 +4944,10 @@ rel_order_by(sql_query *query, sql_rel *
list *exps = new_exp_list(sql->sa);
dnode *o = orderby->data.lval->h;
dlist *selection = NULL;
-
+ int ff = sql_sel;
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
