Changeset: 2c2bfc484616 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB/rev/2c2bfc484616 Added Files: sql/test/miscellaneous/Tests/group_by_all.test sql/test/sets/Tests/All sql/test/sets/Tests/union.test Modified Files: sql/include/sql_relation.h sql/rel.txt sql/server/rel_exp.c sql/server/rel_exp.h sql/server/rel_rel.c sql/server/rel_select.c sql/server/rel_unnest.c sql/server/sql_parser.y sql/test/BugTracker-2011/Tests/crash_on_alias.Bug-2798.test sql/test/BugTracker-2024/Tests/aggr-exp-copy-crash-7435.test sql/test/SQLancer/Tests/sqlancer01.test sql/test/SQLancer/Tests/sqlancer03.test sql/test/SQLancer/Tests/sqlancer08.test sql/test/SQLancer/Tests/sqlancer09.test sql/test/SQLancer/Tests/sqlancer10.test sql/test/SQLancer/Tests/sqlancer14.test sql/test/SQLancer/Tests/sqlancer15.test sql/test/SQLancer/Tests/sqlancer23.test sql/test/miscellaneous/Tests/All sql/test/miscellaneous/Tests/groupby_expressions.test sql/test/miscellaneous/Tests/simple_selects.test sql/test/subquery/Tests/subquery3.test sql/test/subquery/Tests/subquery6.test Branch: default Log Message:
implemented group by and order by reference number corrected handling of constants in group by (ie we now disallow this as defined by sql standard) add support for group by and order by all/* diffs (truncated from 1011 to 300 lines): 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 @@ -50,7 +50,7 @@ typedef struct expression { unsigned short tmp; unsigned int card:2, /* card (0 truth value!) (1 atoms) (2 aggr) (3 multi value) */ - freevar:4, /* free variable, ie binds to the upper dependent join */ + freevar:8, /* free variable, ie binds to the upper dependent join */ intern:1, selfref:1, /* set when the expression references a expression in the same projection list */ anti:1, @@ -298,7 +298,7 @@ typedef struct relation { int nrcols; /* nr of cols */ unsigned int flag:16, - card:4, /* 0, 1 (row), 2 aggr, 3 */ + card:2, /* 0, 1 (row), 2 aggr, 3 */ dependent:1, /* dependent join */ distinct:1, processed:1, /* fully processed or still in the process of building */ diff --git a/sql/rel.txt b/sql/rel.txt --- a/sql/rel.txt +++ b/sql/rel.txt @@ -66,6 +66,7 @@ GROUPBY (card ATOM (no group by exps), -> exps is list of (groupby and) aggregate expressions -> l is relation -> r is list of group by expressions + -> flag 1 has group by nr , 2 group by all TOPN (card ATOM, AGGR, or MULTI (same card as lower relation)) -> exps (list) lng limit, [ lng offset ] -> if the limit is a NULL value, then it's ignored, ie only the offset will be used diff --git a/sql/server/rel_exp.c b/sql/server/rel_exp.c --- a/sql/server/rel_exp.c +++ b/sql/server/rel_exp.c @@ -2058,6 +2058,99 @@ exp_is_atom( sql_exp *e ) return 0; } +static int +exps_are_aggr(sql_rel *r, list *exps) +{ + int aggr = 1; + if (!list_empty(exps)) + for(node *n=exps->h; n && aggr; n=n->next) + aggr &= exp_is_aggr(r, n->data); + return aggr; +} + +/* is expression e an aggregated result of r */ +int +exp_is_aggr(sql_rel *r, sql_exp *e) +{ + sql_exp *ne = NULL; + + switch (e->type) { + case e_atom: + return true; + case e_convert: + return exp_is_aggr(r, e->l); + case e_func: + return exps_are_aggr(r, e->l); + case e_aggr: + return true; + case e_cmp: + if (e->card != CARD_ATOM) + return false; + if (e->flag == cmp_or || e->flag == cmp_filter) + return exps_are_aggr(r, e->l) && exps_are_aggr(r, e->r); + if (e->flag == cmp_in || e->flag == cmp_notin) + return exp_is_aggr(r, e->l) && exps_are_aggr(r, e->r); + return exp_is_aggr(r, e->l) && exp_is_aggr(r, e->r) && (!e->f || exp_is_aggr(r, e->f)); + case e_column: + if (e->freevar) + return true; + ne = rel_find_exp(r, e); + if (ne) /* found local */ + return true; + else + return false; + case e_psm: + return false; + } + return false; +} + +static int +exps_have_aggr(sql_rel *r, list *exps) +{ + int aggr = 0; + if (!list_empty(exps)) + for(node *n=exps->h; n && !aggr; n=n->next) + aggr |= exp_has_aggr(r, n->data); + return aggr; +} + +int +exp_has_aggr(sql_rel *r, sql_exp *e ) +{ + sql_exp *ne = NULL; + + switch (e->type) { + case e_atom: + return false; + case e_convert: + return exp_has_aggr(r, e->l); + case e_func: + return exps_have_aggr(r, e->l); + case e_aggr: + return true; + case e_cmp: + if (e->card != CARD_ATOM) + return false; + if (e->flag == cmp_or || e->flag == cmp_filter) + return exps_have_aggr(r, e->l) && exps_have_aggr(r, e->r); + if (e->flag == cmp_in || e->flag == cmp_notin) + return exp_has_aggr(r, e->l) && exps_have_aggr(r, e->r); + return exp_has_aggr(r, e->l) && exp_has_aggr(r, e->r) && (!e->f || exp_has_aggr(r, e->f)); + case e_column: + if (e->freevar) + return false; + ne = rel_find_exp(r->l, e); + if (ne) /* found lower */ + return false; + else + return true; + case e_psm: + return false; + } + return false; +} + int exp_has_rel( sql_exp *e ) { diff --git a/sql/server/rel_exp.h b/sql/server/rel_exp.h --- a/sql/server/rel_exp.h +++ b/sql/server/rel_exp.h @@ -165,6 +165,8 @@ extern int exp_is_not_null(sql_exp *e); extern int exp_is_null(sql_exp *e); extern int exp_is_rel(sql_exp *e); extern int exps_one_is_rel(list *exps); +extern int exp_is_aggr(sql_rel *r, sql_exp *e); /* check if e is aggregation result of r */ +extern int exp_has_aggr(sql_rel *r, sql_exp *e); /* check if group by expresssion has some aggregate function from r */ extern int exp_has_rel(sql_exp *e); extern int exps_have_rel_exp(list *exps); extern int exps_have_func(list *exps); diff --git a/sql/server/rel_rel.c b/sql/server/rel_rel.c --- a/sql/server/rel_rel.c +++ b/sql/server/rel_rel.c @@ -971,6 +971,10 @@ rel_groupby(mvc *sql, sql_rel *l, list * for (en = groupbyexps->h; en; en = en->next) { sql_exp *e = en->data, *ne; + if (exp_is_atom(e) && !e->alias.name) { /* numeric lookup done later */ + rel->flag = 1; + continue; + } /* after the group by the cardinality reduces */ e->card = MIN(e->card, rel->card); /* if the column is an atom, the cardinality should not change */ ne = exp_ref(sql, e); 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 @@ -1356,7 +1356,8 @@ rel_column_ref(sql_query *query, sql_rel if (!exp && inner) if (!(exp = rel_bind_column(sql, inner, name, f, 0)) && sql->session->status == -ERR_AMBIGUOUS) return NULL; - if (!exp && inner && is_sql_aggr(f) && (is_groupby(inner->op) || is_select(inner->op))) { + if (!exp && inner && ((is_sql_aggr(f) && (is_groupby(inner->op) || is_select(inner->op))) || + (is_groupby(inner->op) && inner->flag))) { /* if inner is selection, ie having clause, get the left relation to reach group by */ sql_rel *gp = inner; while (gp && is_select(gp->op)) @@ -1435,9 +1436,9 @@ rel_column_ref(sql_query *query, sql_rel if (!exp) return sql_error(sql, ERR_NOTFOUND, SQLSTATE(42000) "SELECT: identifier '%s' unknown", name); - if (exp && inner && inner->card <= CARD_AGGR && exp->card > CARD_AGGR && (is_sql_sel(f) || is_sql_having(f)) && !is_sql_aggr(f)) + if (exp && inner && inner->card <= CARD_AGGR && exp->card > CARD_AGGR && (is_sql_sel(f) || is_sql_having(f)) && (!is_sql_aggr(f) && !(inner->flag))) return sql_error(sql, ERR_GROUPBY, SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s' in query results without an aggregate function", name); - if (exp && inner && is_groupby(inner->op) && !is_sql_aggr(f) && !is_freevar(exp)) + if (exp && inner && is_groupby(inner->op) && !is_sql_aggr(f) && !is_freevar(exp) && !inner->flag) exp = rel_groupby_add_aggr(sql, inner, exp); } else if (dlist_length(l) == 2 || dlist_length(l) == 3) { const char *sname = NULL; @@ -4070,7 +4071,7 @@ rel_group_column(sql_query *query, sql_r if (e && exp_is_atom(e)) { sql_subtype *tpe = exp_subtype(e); - if (!tpe || tpe->type->eclass != EC_NUM) { + if (!is_atom(e->type) ||!tpe || tpe->type->eclass != EC_NUM) { if (!tpe) return sql_error(sql, 02, SQLSTATE(42000) "Cannot have a parameter (?) for group by column"); return sql_error(sql, 02, SQLSTATE(42000) "SELECT: non-integer constant in GROUP BY"); @@ -4229,7 +4230,7 @@ rel_groupings(sql_query *query, sql_rel sql_exp *e = rel_group_column(query, rel, grp, selection, exps, f); if (!e) return NULL; - if (e->type != e_column) { /* store group by expressions in the stack */ + if (e->type != e_column && !exp_is_atom(e)) { /* store group by expressions in the stack */ if (is_sql_group_totals(f)) return sql_error(sql, 02, SQLSTATE(42000) "GROUP BY: grouping expressions not possible with ROLLUP, CUBE and GROUPING SETS"); if (!exp_has_rel(e) && !frame_push_groupby_expression(sql, grp, e)) @@ -4401,6 +4402,19 @@ rel_order_by(sql_query *query, sql_rel * mvc *sql = query->sql; sql_rel *rel = *R, *or = rel; /* the order by relation */ list *exps = new_exp_list(sql->sa); + + if (!orderby->data.lval) { /* by all */ + if (is_sql_orderby(f)) { + assert(is_project(rel->op)); + for(node *n = rel->exps->h; n; n = n->next) { + sql_exp *e = n->data; + append(exps, exp_ref(sql, e)); + } + return exps; + } + return NULL; + } + dnode *o = orderby->data.lval->h; dlist *selection = NULL; @@ -5186,47 +5200,17 @@ exps_has_rank(list *exps) sql_exp * rel_value_exp(sql_query *query, sql_rel **rel, symbol *se, int f, exp_kind ek) { - SelectNode *sn = NULL; - sql_exp *e; if (!se) return NULL; - if (se->token == SQL_SELECT) - sn = (SelectNode*)se; if (mvc_highwater(query->sql)) return sql_error(query->sql, 10, SQLSTATE(42000) "Query too complex: running out of stack space"); - e = rel_value_exp2(query, rel, se, f, ek); + sql_exp *e = rel_value_exp2(query, rel, se, f, ek); if (e && (se->token == SQL_SELECT || se->token == SQL_TABLE) && !exp_is_rel(e)) { assert(*rel); return rel_lastexp(query->sql, *rel); } - if (exp_has_rel(e) && sn && !sn->from && !sn->where && (ek.card < card_set || ek.card == card_exists) && ek.type != type_relation) { - sql_rel *r = exp_rel_get_rel(query->sql->sa, e); - sql_rel *l = r->l; - - if (r && is_simple_project(r->op) && l && is_simple_project(l->op) && !l->l && !exps_has_rank(r->exps) && list_length(r->exps) == 1) { /* should be a simple column or value */ - if (list_length(r->exps) > 1) { /* Todo make sure the in handling can handle a list ( value lists), instead of just a list of relations */ - e = exp_values(query->sql->sa, r->exps); - } else { - sql_exp *ne = r->exps->h->data; - if (rel && *rel && !exp_has_rel(ne)) { - e = ne; - rel_bind_var(query->sql, *rel, e); - unsigned int fv = exp_has_freevar(query->sql, e); - if (fv && is_sql_aggr(f)) { - if (fv <= query_has_outer(query)) { - sql_rel *outer = query_fetch_outer(query, fv-1); - query_outer_pop_last_used(query, fv-1); - reset_outer(outer); - } else { - reset_freevar(e); - } - } - } - } - } - } return e; } @@ -5393,18 +5377,25 @@ rel_where_groupby_nodes(sql_query *query query_processed(query); if (rel && sn->groupby) { - list *gbe, *sets = NULL; - for (dnode *o = sn->groupby->data.lval->h; o ; o = o->next) { - symbol *grouping = o->data.sym; - if (grouping->token == SQL_ROLLUP || grouping->token == SQL_CUBE || grouping->token == SQL_GROUPING_SETS) { - *group_totals |= sql_group_totals; - break; + list *gbe = NULL, *sets = NULL; + int all = 0; + if (sn->groupby->data.lval == NULL) { /* ALL */ + all = 1; + } else { + for (dnode *o = sn->groupby->data.lval->h; o ; o = o->next) { + symbol *grouping = o->data.sym; + if (grouping->token == SQL_ROLLUP || grouping->token == SQL_CUBE || grouping->token == SQL_GROUPING_SETS) { + *group_totals |= sql_group_totals; + break; + } } - } - gbe = rel_groupings(query, &rel, sn->groupby, sn->selection, sql_sel | sql_groupby | *group_totals, false, &sets); - if (!gbe) - return NULL; + gbe = rel_groupings(query, &rel, sn->groupby, sn->selection, sql_sel | sql_groupby | *group_totals, false, &sets); _______________________________________________ checkin-list mailing list -- checkin-list@monetdb.org To unsubscribe send an email to checkin-list-le...@monetdb.org