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 -- [email protected]
To unsubscribe send an email to [email protected]
