Changeset: de367bfd5f32 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=de367bfd5f32
Modified Files:
sql/backends/monet5/rel_bin.c
sql/server/rel_exp.c
sql/server/rel_select.c
sql/server/sql_query.c
sql/server/sql_query.h
sql/test/miscellaneous/Tests/groupby_error.stable.out
sql/test/subquery/Tests/subquery3.sql
sql/test/subquery/Tests/subquery3.stable.err
sql/test/subquery/Tests/subquery3.stable.out
sql/test/subquery/Tests/subquery5.sql
Branch: Jun2020
Log Message:
improved errors/checks for nested aggregates
improved errors/checks for using non-grouped columns
solved issue with non-aligned bats caused by batcalc pushdown changes
diffs (truncated from 450 to 300 lines):
diff --git a/sql/backends/monet5/rel_bin.c b/sql/backends/monet5/rel_bin.c
--- a/sql/backends/monet5/rel_bin.c
+++ b/sql/backends/monet5/rel_bin.c
@@ -1058,6 +1058,15 @@ exp_bin(backend *be, sql_exp *e, stmt *l
stmt_uselect(be, l, r2,
range2rcompare(e->flag), sel, is_anti(e), 0), is_anti(e), 0);
#endif
} else {
+ if (sel && ((l->cand && l->nrcols) ||
(r->cand && r->nrcols) || (r2->cand && r->nrcols))) {
+ if (!l->cand && l->nrcols)
+ l = stmt_project(be,
sel, l);
+ if (!r->cand && r->nrcols)
+ r = stmt_project(be,
sel, r);
+ if (!r2->cand && r2->nrcols)
+ r2 = stmt_project(be,
sel, r2);
+ sel = NULL;
+ }
s = stmt_uselect2(be, l, r, r2,
(comp_type)e->flag, sel, is_anti(e));
}
} else {
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
@@ -660,7 +660,7 @@ exp_alias_or_copy( mvc *sql, const char
sql_exp *ne = NULL;
if (!tname)
- tname = old->alias.rname;
+ tname = exp_relname(old);
if (!cname && exp_name(old) && has_label(old)) {
ne = exp_column(sql->sa, exp_relname(old), exp_name(old),
exp_subtype(old), orel?orel->card:CARD_ATOM, has_nil(old), is_intern(old));
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
@@ -1193,6 +1193,12 @@ rel_column_ref(sql_query *query, sql_rel
}
if (exp && outer && outer->card <= CARD_AGGR &&
exp->card > CARD_AGGR && (!is_sql_aggr(f) || is_sql_farg(f)))
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 && outer && (is_sql_groupby(f) ||
is_sql_aggr(f))) {
+ if (query_outer_used_exp( query, i, exp,
is_sql_aggr(f) && !is_sql_farg(f))) {
+ sql_exp *lu =
query_outer_last_used(query, i);
+ return sql_error(sql, 05,
SQLSTATE(42000) "SELECT: subquery uses ungrouped column \"%s.%s\" from outer
query", exp_relname(lu), exp_name(lu));
+ }
+ }
if (exp) {
if (is_groupby(outer->op) && !is_sql_aggr(f)) {
exp = rel_groupby_add_aggr(sql, outer,
exp);
@@ -1256,6 +1262,12 @@ rel_column_ref(sql_query *query, sql_rel
}
if (exp && outer && outer->card <= CARD_AGGR &&
exp->card > CARD_AGGR && (!is_sql_aggr(f) || is_sql_farg(f)))
return sql_error(sql, ERR_GROUPBY,
SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s.%s' in query
results without an aggregate function", tname, cname);
+ if (exp && outer && (is_sql_groupby(f) ||
is_sql_aggr(f))) {
+ if (query_outer_used_exp( query, i, exp,
is_sql_aggr(f) && !is_sql_farg(f))) {
+ sql_exp *lu =
query_outer_last_used(query, i);
+ return sql_error(sql, 05,
SQLSTATE(42000) "SELECT: subquery uses ungrouped column \"%s.%s\" from outer
query", exp_relname(lu), exp_name(lu));
+ }
+ }
if (exp) {
if (is_groupby(outer->op) && !is_sql_aggr(f)) {
exp = rel_groupby_add_aggr(sql, outer,
exp);
@@ -3345,7 +3357,7 @@ static sql_exp *
for ( ; args && args->data.sym; args = args->next ) {
int base = (!groupby || !is_project(groupby->op) ||
is_base(groupby->op) || is_processed(groupby));
sql_rel *gl = base?groupby:groupby->l, *ogl = gl; /*
handle case of subqueries without correlation */
- sql_exp *e = rel_value_exp(query, &gl, args->data.sym,
(f | sql_aggr)& ~sql_farg, ek);
+ sql_exp *e = rel_value_exp(query, &gl, args->data.sym,
(f | sql_aggr)& ~sql_farg, ek), *lu;
has_args = true;
if (gl && gl != ogl) {
@@ -3372,11 +3384,17 @@ static sql_exp *
GDKfree(uaname);
return 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)));
+ if (all_aggr && is_freevar(e) &&
(lu=query_outer_last_used(query, is_freevar(e)-1)) != NULL) {
+ all_aggr &= is_aggr(lu->type);
+ } else {
+ 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);
}
+ if (all_freevar && all_aggr)
+ return sql_error(sql, 05, SQLSTATE(42000) "SELECT:
aggregate function calls cannot be nested");
if (!all_freevar) {
if (all_aggr) {
char *uaname = GDKmalloc(strlen(aname) + 1);
@@ -3425,6 +3443,7 @@ static sql_exp *
}
if (all_freevar) { /* case 2, ie use outer */
+ //int card;
sql_exp *exp = NULL;
/* find proper relation, base on freevar (stack hight) */
for (node *n = exps->h; n; n = n->next) {
@@ -3436,6 +3455,7 @@ static sql_exp *
}
int sql_state = query_fetch_outer_state(query,all_freevar-1);
res = groupby = query_fetch_outer(query, all_freevar-1);
+ //card = query_outer_used_card(query, all_freevar-1);
if (exp && !is_groupby_col(res, exp)) {
if (is_sql_groupby(sql_state))
return sql_error(sql, 05, SQLSTATE(42000)
"SELECT: aggregate function '%s' not allowed in GROUP BY clause", aname);
@@ -3453,6 +3473,12 @@ static sql_exp *
return sql_error(sql, 05, SQLSTATE(42000)
"CALL: aggregate functions not allowed inside CALL");
if (is_sql_from(sql_state))
return sql_error(sql, 05, SQLSTATE(42000)
"SELECT: aggregate functions not allowed in functions in FROM");
+#if 0
+ if (card > CARD_AGGR) { /* used a expression before on
the non grouped relation */
+ sql_exp *lu = query_outer_last_used(query,
all_freevar-1);
+ return sql_error(sql, 05, SQLSTATE(42000)
"SELECT: subquery uses ungrouped column \"%s.%s\" from outer query",
exp_relname(lu), exp_name(lu));
+ }
+#endif
}
}
@@ -3535,6 +3561,8 @@ static sql_exp *
if (!groupby)
return e;
+ if (all_freevar)
+ query_outer_used_exp(query, all_freevar-1, e, 1);
e = rel_groupby_add_aggr(sql, groupby, e);
if (!group && !all_freevar)
return e;
@@ -3689,6 +3717,8 @@ static sql_exp *
if (!groupby)
return e;
+ if (all_freevar)
+ query_outer_used_exp(query, all_freevar-1, e, 1);
e = rel_groupby_add_aggr(sql, groupby, e);
if (!group && !all_freevar)
return e;
diff --git a/sql/server/sql_query.c b/sql/server/sql_query.c
--- a/sql/server/sql_query.c
+++ b/sql/server/sql_query.c
@@ -17,6 +17,9 @@ sq_create( sql_allocator *sa, sql_rel *r
assert(rel);
q->rel = rel;
q->sql_state = sql_state;
+ q->last_used = NULL;
+ q->used_card = 0;
+ q->groupby = 0;
return q;
}
@@ -69,6 +72,8 @@ query_update_outer(sql_query *q, sql_rel
{
stacked_query *sq = sql_stack_fetch(q->outer, i);
sq->rel = r;
+ sq->last_used = NULL;
+ sq->used_card = 0;
}
int
@@ -76,3 +81,33 @@ query_has_outer(sql_query *q)
{
return sql_stack_top(q->outer);
}
+
+int
+query_outer_used_exp(sql_query *q, int i, sql_exp *e, bool aggr)
+{
+ stacked_query *sq = sql_stack_fetch(q->outer, i);
+
+ if (aggr && sq->groupby)
+ return -1;
+ sq->last_used = e;
+ sq->used_card = sq->rel->card;
+ if (!aggr)
+ sq->groupby = 1;
+ return 0;
+}
+
+int
+query_outer_used_card(sql_query *q, int i)
+{
+ stacked_query *sq = sql_stack_fetch(q->outer, i);
+
+ return sq->used_card;
+}
+
+sql_exp *
+query_outer_last_used(sql_query *q, int i)
+{
+ stacked_query *sq = sql_stack_fetch(q->outer, i);
+
+ return sq->last_used;
+}
diff --git a/sql/server/sql_query.h b/sql/server/sql_query.h
--- a/sql/server/sql_query.h
+++ b/sql/server/sql_query.h
@@ -16,6 +16,9 @@
typedef struct stacked_query {
sql_rel *rel;
int sql_state;
+ sql_exp *last_used;
+ int used_card;
+ int groupby;
} stacked_query;
typedef struct sql_query {
@@ -31,4 +34,8 @@ extern int query_fetch_outer_state(sql_q
extern void query_update_outer(sql_query *q, sql_rel *r, int i);
extern int query_has_outer(sql_query *q); /* returns number of outer relations
*/
+extern int query_outer_used_exp(sql_query *q, int i, sql_exp *e, bool aggr);
+extern int query_outer_used_card(sql_query *q, int i);
+extern sql_exp *query_outer_last_used(sql_query *q, int i);
+
#endif
diff --git a/sql/test/miscellaneous/Tests/groupby_error.stable.out
b/sql/test/miscellaneous/Tests/groupby_error.stable.out
--- a/sql/test/miscellaneous/Tests/groupby_error.stable.out
+++ b/sql/test/miscellaneous/Tests/groupby_error.stable.out
@@ -182,6 +182,16 @@ project (
[ 2 ]
[ 2 ]
[ 2 ]
+#SELECT * FROM tab0 WHERE NOT - col0 - col1 * col2 <= ( + col0 ) AND NOT ( +
col2 + col1 - col1 ) NOT BETWEEN - col0 AND - col1 + - col2 / col1;
+% sys.tab0, sys.tab0, sys.tab0 # table_name
+% col0, col1, col2 # name
+% int, int, int # type
+% 1, 1, 1 # length
+#SELECT * FROM tab0 AS cor0 WHERE NOT col1 BETWEEN - col0 AND col0 + col1 *
col1 AND - col1 BETWEEN col0 AND ( NULL ) OR NOT col0 * col0 + col0 <= NULL;
+% sys.cor0, sys.cor0, sys.cor0 # table_name
+% col0, col1, col2 # name
+% int, int, int # type
+% 1, 1, 1 # length
#SELECT DISTINCT * FROM tab2 WHERE NOT ( - + 50 + ( 70 ) ) = + col2;
% sys.tab2, sys.tab2, sys.tab2 # table_name
% col0, col1, col2 # name
diff --git a/sql/test/subquery/Tests/subquery3.sql
b/sql/test/subquery/Tests/subquery3.sql
--- a/sql/test/subquery/Tests/subquery3.sql
+++ b/sql/test/subquery/Tests/subquery3.sql
@@ -361,7 +361,7 @@ FROM integers i1; --SUM(i1.i) is a corre
SELECT
(SELECT SUM(SUM(i1.i)) FROM integers i2 GROUP BY i2.i)
-FROM integers i1; --error, more than one row returned by a subquery used as an
expression
+FROM integers i1; --error, aggregation functions cannot be nested
SELECT
(SELECT SUM(SUM(i2.i)) FROM integers i2 GROUP BY i2.i)
diff --git a/sql/test/subquery/Tests/subquery3.stable.err
b/sql/test/subquery/Tests/subquery3.stable.err
--- a/sql/test/subquery/Tests/subquery3.stable.err
+++ b/sql/test/subquery/Tests/subquery3.stable.err
@@ -178,10 +178,10 @@ CODE = M0M29
MAPI = (monetdb) /var/tmp/mtest-224250/.s.monetdb.30823
QUERY = SELECT
(SELECT SUM(SUM(i1.i)) FROM integers i2 GROUP BY i2.i)
- FROM integers i1; --error, more than one row returned by a subquery
used as an expression
-ERROR = !zero_or_one: cardinality violation, scalar expression expected
-CODE = M0M29
-MAPI = (monetdb) /var/tmp/mtest-22911/.s.monetdb.35494
+ FROM integers i1; --error, aggregation functions cannot be nested
+ERROR = !SELECT: aggregate function calls cannot be nested
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-766253/.s.monetdb.34755
QUERY = SELECT
(SELECT SUM(SUM(i2.i)) FROM integers i2 GROUP BY i2.i)
FROM integers i1; --error, aggregation functions cannot be nested
diff --git a/sql/test/subquery/Tests/subquery3.stable.out
b/sql/test/subquery/Tests/subquery3.stable.out
--- a/sql/test/subquery/Tests/subquery3.stable.out
+++ b/sql/test/subquery/Tests/subquery3.stable.out
@@ -36,7 +36,7 @@ stdout of test 'subquery3` in directory
#FROM another_T t1
#GROUP BY t1.col6, t1.col7;
% . # table_name
-% %32 # name
+% %36 # name
% boolean # type
% 5 # length
[ false ]
@@ -63,7 +63,7 @@ stdout of test 'subquery3` in directory
#FROM another_T
#GROUP BY col1, col2, col5;
% ., . # table_name
-% %50, %51 # name
+% %53, %54 # name
% int, int # type
% 1, 1 # length
[ 1, 0 ]
@@ -193,8 +193,8 @@ stdout of test 'subquery3` in directory
# NOT SUM(t1.col2) * MIN(t1.col6 + t1.col6 - t1.col6 * t1.col6) NOT IN
(SELECT MAX(t2.col6) FROM another_T t2 GROUP BY t1.col6 HAVING t1.col7 +
MIN(t2.col8) < MAX(t2.col7 - t1.col6))
#FROM another_T t1
#GROUP BY t1.col7, t1.col6;
-% .%22 # table_name
-% %22 # name
+% .%23 # table_name
+% %23 # name
% boolean # type
% 5 # length
[ false ]
@@ -207,7 +207,7 @@ stdout of test 'subquery3` in directory
#FROM another_T t1
#GROUP BY t1.col1, t1.col2;
% ., . # table_name
-% %205, %206 # name
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
