Changeset: ce47a38f5294 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=ce47a38f5294
Modified Files:
sql/include/sql_relation.h
sql/server/rel_rel.c
sql/server/rel_select.c
sql/test/BugTracker-2017/Tests/aggr_udf_with_more_than_2params.Bug-6385.stable.err
sql/test/BugTracker-2018/Tests/sqlitelogictest-groupby-coalesce-error-message.Bug-6608.stable.err
sql/test/subquery/Tests/subquery4.stable.out
Branch: default
Log Message:
fixed proper use of nested aggregation
reduced complexity in the rel_unop, rel_binop and rel_nop cases, ie
first try if we have an aggregation function with the required number of
arguments, iff
so continue with rel_aggr else continue in the functional route.
diffs (truncated from 301 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
@@ -267,6 +267,7 @@ typedef struct relation {
dependent:1, /* dependent join */
distinct:1,
processed:1, /* fully processed or still in the process of building
*/
+ grouped:1, /* groupby processed all the group by exps */
subquery:1; /* is this part a subquery, this is needed for proper
name binding */
void *p; /* properties for the optimizer, distribution */
} sql_rel;
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
@@ -900,6 +900,7 @@ rel_groupby(mvc *sql, sql_rel *l, list *
rel->exps = aggrs;
rel->nrcols = l->nrcols;
rel->op = op_groupby;
+ rel->grouped = 1;
return rel;
}
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
@@ -2858,40 +2858,16 @@ rel_unop(sql_query *query, sql_rel **rel
exp_kind iek = {type_value, card_column, FALSE};
sql_exp *e = NULL;
sql_subfunc *sf = NULL;
- sql_ftype type = (ek.card == card_loader)?F_LOADER:((ek.card ==
card_none)?F_PROC:F_FUNC);
if (sname && !(s = mvc_bind_schema(sql, sname)))
return sql_error(sql, 02, SQLSTATE(3F000) "SELECT: no such
schema '%s'", sname);
+ sf = find_func(sql, s, fname, 1, F_AGGR, NULL);
+ if (sf)
+ return rel_aggr(query, rel, se, f);
+
e = rel_value_exp(query, rel, l->next->next->data.sym, f|sql_farg, iek);
if (!e)
- sf = find_func(sql, s, fname, 1, F_AGGR, NULL);
-
- if (!sf && !e && *rel && (*rel)->card == CARD_AGGR) {
- if (is_sql_having(f) || is_sql_orderby(f))
- return NULL;
- /* reset error */
- sql->session->status = 0;
- sql->errstr[0] = '\0';
- return sql_error(sql, 02, SQLSTATE(42000) "SELECT: no such
aggregate '%s'", fname);
- }
- if (!e && sf) { /* possibly we cannot resolve the argument as the
function maybe an aggregate */
- /* reset error */
- sql->session->status = 0;
- sql->errstr[0] = '\0';
- return rel_aggr(query, rel, se, f);
- }
- if (type == F_FUNC) {
- sf = find_func(sql, s, fname, 1, F_AGGR, NULL);
- if (sf) {
- if (!e) { /* reset error */
- sql->session->status = 0;
- sql->errstr[0] = '\0';
- }
- return _rel_aggr(query, rel, l->next->data.i_val, s,
fname, l->next->next, f);
- }
- }
- if (!e)
return NULL;
return rel_unop_(sql, rel ? *rel : NULL, e, s, fname, ek.card);
}
@@ -3137,42 +3113,17 @@ rel_binop(sql_query *query, sql_rel **re
char *sname = qname_schema(dl->data.lval);
sql_schema *s = cur_schema(sql);
exp_kind iek = {type_value, card_column, FALSE};
- sql_ftype type = (ek.card == card_loader)?F_LOADER:((ek.card ==
card_none)?F_PROC:F_FUNC);
sql_subfunc *sf = NULL;
if (sname && !(s = mvc_bind_schema(sql, sname)))
return sql_error(sql, 02, SQLSTATE(3F000) "SELECT: no such
schema '%s'", sname);
+ sf = find_func(sql, s, fname, 2, F_AGGR, NULL);
+ if (sf)
+ return rel_aggr(query, rel, se, f);
+
l = rel_value_exp(query, rel, dl->next->next->data.sym, f|sql_farg,
iek);
r = rel_value_exp(query, rel, dl->next->next->next->data.sym,
f|sql_farg, iek);
-
- if (!l || !r)
- sf = find_func(sql, s, fname, 2, F_AGGR, NULL);
- if (!sf && (!l || !r) && *rel && (*rel)->card == CARD_AGGR) {
- if (mvc_status(sql) || is_sql_having(f) || is_sql_orderby(f))
- return NULL;
- /* reset error */
- sql->session->status = 0;
- sql->errstr[0] = '\0';
- return sql_error(sql, 02, SQLSTATE(42000) "SELECT: no such
aggregate '%s'", fname);
- }
- if (!l && !r && sf) { /* possibly we cannot resolve the argument as the
function maybe an aggregate */
- /* reset error */
- sql->session->status = 0;
- sql->errstr[0] = '\0';
- return rel_aggr(query, rel, se, f);
- }
- if (type == F_FUNC) {
- sf = find_func(sql, s, fname, 2, F_AGGR, NULL);
- if (sf) {
- if (!l || !r) { /* reset error */
- sql->session->status = 0;
- sql->errstr[0] = '\0';
- }
- return _rel_aggr(query, rel, dl->next->data.i_val, s,
fname, dl->next->next, f);
- }
- }
-
if (!l || !r)
return NULL;
return rel_binop_(sql, rel ? *rel : NULL, l, r, s, fname, ek.card);
@@ -3204,15 +3155,15 @@ rel_nop_(mvc *sql, sql_rel *rel, sql_exp
}
static sql_exp *
-rel_nop(sql_query *query, sql_rel **rel, symbol *se, int fs, exp_kind ek)
+rel_nop(sql_query *query, sql_rel **rel, symbol *se, int f, exp_kind ek)
{
mvc *sql = query->sql;
- int nr_args = 0;
dnode *l = se->data.lval->h;
+ int nr_args = dlist_length(l->next->next->data.lval);
dnode *ops = l->next->next->data.lval->h;
list *exps = new_exp_list(sql->sa);
list *tl = sa_list(sql->sa);
- sql_subfunc *f = NULL;
+ sql_subfunc *sf = NULL;
sql_subtype *obj_type = NULL;
char *fname = qname_fname(l->data.lval);
char *sname = qname_schema(l->data.lval);
@@ -3223,8 +3174,12 @@ rel_nop(sql_query *query, sql_rel **rel,
if (sname && !(s = mvc_bind_schema(sql, sname)))
return sql_error(sql, 02, SQLSTATE(3F000) "SELECT: no such
schema '%s'", sname);
- for (; ops; ops = ops->next, nr_args++) {
- sql_exp *e = rel_value_exp(query, rel, ops->data.sym,
fs|sql_farg, iek);
+ sf = find_func(sql, s, fname, nr_args, F_AGGR, NULL);
+ if (sf) /* We have to pas the arguments properly, so skip call to
rel_aggr */
+ return _rel_aggr(query, rel, l->next->data.i_val, s, fname,
l->next->next->data.lval->h, f);
+
+ for (nr_args = 0; ops; ops = ops->next, nr_args++) {
+ sql_exp *e = rel_value_exp(query, rel, ops->data.sym,
f|sql_farg, iek);
sql_subtype *tpe;
if (!e)
@@ -3237,25 +3192,6 @@ rel_nop(sql_query *query, sql_rel **rel,
append(tl, tpe);
}
}
-
- /* first try aggregate */
- f = find_func(sql, s, fname, nr_args, F_AGGR, NULL);
- if (!f && err && *rel && (*rel)->card == CARD_AGGR) {
- if (is_sql_having(fs) || is_sql_orderby(fs))
- return NULL;
- /* reset error */
- sql->session->status = 0;
- sql->errstr[0] = '\0';
- return sql_error(sql, 02, SQLSTATE(42000) "SELECT: no such
aggregate '%s'", fname);
- }
- if (f) {
- if (err) {
- /* reset error */
- sql->session->status = 0;
- sql->errstr[0] = '\0';
- }
- return _rel_aggr(query, rel, l->next->data.i_val, s, fname,
l->next->next->data.lval->h, fs);
- }
if (err)
return NULL;
return _rel_nop(sql, s, fname, tl, rel ? *rel : NULL, exps, obj_type,
nr_args, ek);
@@ -3352,6 +3288,14 @@ static sql_exp *
has_args = true;
if (gl && gl != ogl) {
+ if (groupby->grouped) {
+ char *uaname = GDKmalloc(strlen(aname)
+ 1);
+ sql_exp *e = sql_error(sql, 02,
SQLSTATE(42000) "%s: aggregate functions cannot be nested",
+ uaname ? toUpperCopy(uaname,
aname) : aname);
+ if (uaname)
+ GDKfree(uaname);
+ return e;
+ }
if (!base)
groupby->l = subquery = gl;
else
@@ -3367,7 +3311,7 @@ static sql_exp *
GDKfree(uaname);
return e;
}
- all_aggr &= (exp_card(e) <= CARD_AGGR &&
!exp_is_atom(e) && !is_func(e->type) && (!groupby || !is_groupby(groupby->op)
|| !groupby->r || !exps_find_exp(groupby->r, e)));
+ all_aggr &= (exp_card(e) <= CARD_AGGR &&
!exp_is_atom(e) && is_aggr(e->type) && !is_func(e->type) && (!groupby ||
!is_groupby(groupby->op) || !groupby->r || !exps_find_exp(groupby->r, e)));
has_freevar |= exp_has_freevar(sql, e);
all_freevar &= (is_freevar(e)>0);
list_append(exps, e);
@@ -3709,7 +3653,7 @@ static sql_exp *
type = exp_subtype(e)->type->sqlname;
}
- e = sql_error(sql, 02, SQLSTATE(42000) "%s: no such operator
'%s(%s)'",
+ e = sql_error(sql, 02, SQLSTATE(42000) "%s: no such aggregate
'%s(%s)'",
uaname ? toUpperCopy(uaname, aname) : aname,
aname, type);
if (uaname)
@@ -5291,7 +5235,6 @@ rel_where_groupby_nodes(sql_query *query
if (rel->op != op_groupby)
rel = rel_groupby(sql, rel, NULL);
}
-
return rel;
}
diff --git
a/sql/test/BugTracker-2017/Tests/aggr_udf_with_more_than_2params.Bug-6385.stable.err
b/sql/test/BugTracker-2017/Tests/aggr_udf_with_more_than_2params.Bug-6385.stable.err
---
a/sql/test/BugTracker-2017/Tests/aggr_udf_with_more_than_2params.Bug-6385.stable.err
+++
b/sql/test/BugTracker-2017/Tests/aggr_udf_with_more_than_2params.Bug-6385.stable.err
@@ -31,11 +31,11 @@ stderr of test 'aggr_udf_with_more_than_
MAPI = (monetdb) /var/tmp/mtest-31488/.s.monetdb.35710
QUERY = SELECT groupnr, aggr2(value) FROM grouped_ints GROUP BY groupnr;
-ERROR = !SELECT: no such aggregate 'aggr2'
+ERROR = !SELECT: cannot use non GROUP BY column 'value' in query results
without an aggregate function
CODE = 42000
MAPI = (monetdb) /var/tmp/mtest-7242/.s.monetdb.39126
QUERY = SELECT groupnr, aggr2(value, value, value) FROM grouped_ints GROUP BY
groupnr;
-ERROR = !SELECT: no such aggregate 'aggr2'
+ERROR = !SELECT: cannot use non GROUP BY column 'value' in query results
without an aggregate function
CODE = 42000
# 16:36:13 >
diff --git
a/sql/test/BugTracker-2018/Tests/sqlitelogictest-groupby-coalesce-error-message.Bug-6608.stable.err
b/sql/test/BugTracker-2018/Tests/sqlitelogictest-groupby-coalesce-error-message.Bug-6608.stable.err
---
a/sql/test/BugTracker-2018/Tests/sqlitelogictest-groupby-coalesce-error-message.Bug-6608.stable.err
+++
b/sql/test/BugTracker-2018/Tests/sqlitelogictest-groupby-coalesce-error-message.Bug-6608.stable.err
@@ -30,7 +30,7 @@ stderr of test 'sqlitelogictest-groupby-
MAPI = (monetdb) /var/tmp/mtest-4323/.s.monetdb.30108
QUERY = SELECT DISTINCT - COALESCE ( - 86, + cor0.col1, cor0.col1, - cor0.col0
) AS col2 FROM tab0 AS cor0 GROUP BY cor0.col2, cor0.col0;
-ERROR = !SELECT: no such aggregate 'sql_neg'
+ERROR = !SELECT: cannot use non GROUP BY column 'cor0.col1' in query results
without an aggregate function
CODE = 42000
MAPI = (monetdb) /var/tmp/mtest-4323/.s.monetdb.30108
QUERY = SELECT COALESCE ( - 86, + cor0.col1, cor0.col1, - cor0.col0 ) AS col2
FROM tab0 AS cor0 GROUP BY cor0.col2, cor0.col0;
@@ -38,7 +38,7 @@ ERROR = !SELECT: cannot use non GROUP BY
CODE = 42000
MAPI = (monetdb) /var/tmp/mtest-381150/.s.monetdb.38204
QUERY = SELECT ALL + 33 * - COALESCE ( - 86, tab1.col2 ) + + col1 FROM tab1
GROUP BY tab1.col1;
-ERROR = !SELECT: no such aggregate 'sql_neg'
+ERROR = !SELECT: cannot use non GROUP BY column 'tab1.col2' in query results
without an aggregate function
CODE = 42000
MAPI = (monetdb) /var/tmp/mtest-4323/.s.monetdb.30108
QUERY = SELECT COALESCE ( - 86, tab1.col2 ) FROM tab1 GROUP BY tab1.col1;
@@ -46,7 +46,7 @@ ERROR = !SELECT: cannot use non GROUP BY
CODE = 42000
MAPI = (monetdb) /var/tmp/mtest-381150/.s.monetdb.38204
QUERY = SELECT ALL + COALESCE ( - cor0.col1, cor0.col1, 63, - cor0.col2 ) * -
cor0.col1 AS col1 FROM tab0 cor0 GROUP BY cor0.col0, col1;
-ERROR = !SELECT: no such aggregate 'sql_neg'
+ERROR = !SELECT: cannot use non GROUP BY column 'cor0.col2' in query results
without an aggregate function
CODE = 42000
MAPI = (monetdb) /var/tmp/mtest-4323/.s.monetdb.30108
QUERY = SELECT ALL + COALESCE ( - cor0.col1, cor0.col1, 63, cor0.col2 ) AS
col1 FROM tab0 cor0 GROUP BY cor0.col0, col1;
@@ -54,7 +54,7 @@ ERROR = !SELECT: cannot use non GROUP BY
CODE = 42000
MAPI = (monetdb) /var/tmp/mtest-381150/.s.monetdb.38204
QUERY = SELECT - 38 + - tab1.col1 - tab1.col1 / COALESCE ( + 20, - tab1.col0 )
FROM tab1 GROUP BY tab1.col1;
-ERROR = !SELECT: no such aggregate 'sql_neg'
+ERROR = !SELECT: cannot use non GROUP BY column 'tab1.col0' in query results
without an aggregate function
CODE = 42000
MAPI = (monetdb) /var/tmp/mtest-4323/.s.monetdb.30108
QUERY = SELECT COALESCE ( + 20, tab1.col0 ) FROM tab1 GROUP BY tab1.col1;
@@ -62,7 +62,7 @@ ERROR = !SELECT: cannot use non GROUP BY
CODE = 42000
MAPI = (monetdb) /var/tmp/mtest-381150/.s.monetdb.38204
QUERY = SELECT DISTINCT COALESCE ( - 82, - cor0.col0, - CAST ( NULL AS INTEGER
) ) / - 70 FROM tab0 AS cor0 GROUP BY cor0.col2;
-ERROR = !SELECT: no such aggregate 'sql_neg'
+ERROR = !SELECT: cannot use non GROUP BY column 'cor0.col0' in query results
without an aggregate function
CODE = 42000
MAPI = (monetdb) /var/tmp/mtest-4323/.s.monetdb.30108
QUERY = SELECT DISTINCT COALESCE ( - 82, cor0.col0, - CAST ( NULL AS INTEGER )
) FROM tab0 AS cor0 GROUP BY cor0.col2;
diff --git a/sql/test/subquery/Tests/subquery4.stable.out
b/sql/test/subquery/Tests/subquery4.stable.out
--- a/sql/test/subquery/Tests/subquery4.stable.out
+++ b/sql/test/subquery/Tests/subquery4.stable.out
@@ -135,6 +135,14 @@ stdout of test 'subquery4` in directory
[ 1 ]
[ 2 ]
[ 3 ]
+#SELECT
+# (SELECT MAX(i2.i) FROM (SELECT MIN(i1.i)) AS i2(i))
+#FROM integers i1;
+% .%2 # table_name
+% %2 # name
+% int # type
+% 1 # length
+[ 1 ]
#UPDATE another_T SET col4 = (SELECT SUM(col4 + ColID) FROM tbl_ProductSales);
[ 4 ]
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
