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 checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list