Changeset: e1032bcf624e for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/e1032bcf624e
Modified Files:
sql/server/rel_select.c
sql/server/rel_updates.c
sql/server/sql_query.c
sql/server/sql_query.h
sql/test/BugTracker-2021/Tests/distinct-union.Bug-7148.test
sql/test/BugTracker-2021/Tests/sum-union.Bug-7147.test
sql/test/subquery/Tests/correlated.test
sql/test/subquery/Tests/subquery3.test
sql/test/subquery/Tests/subquery5.test
sql/test/subquery/Tests/subquery6.test
Branch: default
Log Message:
improved query context/stack, handles more errors with incorrect combination of
grouped/aggregated columns vs non aggregated cols.
diffs (truncated from 508 to 300 lines):
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
@@ -27,13 +27,6 @@
#define VALUE_FUNC(f) (f->func->type == F_FUNC || f->func->type == F_FILT)
#define check_card(card,f) ((card == card_none && !f->res) ||
(CARD_VALUE(card) && f->res && VALUE_FUNC(f)) || card == card_loader || (card
== card_relation && f->func->type == F_UNION))
-static void
-query_processed(sql_query *query )
-{
- query -> last_exp = NULL;
- query -> last_state = 0;
-}
-
/* return all expressions, with table name == tname */
static list *
rel_table_projections( mvc *sql, sql_rel *rel, char *tname, int level )
@@ -1138,7 +1131,7 @@ rel_column_ref(sql_query *query, sql_rel
mvc *sql = query->sql;
sql_exp *exp = NULL;
dlist *l = NULL;
- sql_rel *inner = rel?*rel:NULL;
+ sql_rel *inner = rel?*rel:NULL, *outer = NULL;
int used_lower_after_processed = 0;
assert((column_r->token == SQL_COLUMN || column_r->token == SQL_IDENT)
&& column_r->type == type_list);
@@ -1155,7 +1148,6 @@ rel_column_ref(sql_query *query, sql_rel
return NULL;
if (!exp && query && query_has_outer(query)) {
int i;
- sql_rel *outer;
for (i=query_has_outer(query)-1; i>= 0 && !exp &&
(outer = query_fetch_outer(query,i)); i--) {
if (!(exp = rel_bind_column(sql, outer, name,
f, 0)) && sql->session->status == -ERR_AMBIGUOUS)
@@ -1175,7 +1167,7 @@ rel_column_ref(sql_query *query, sql_rel
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 && outer->card <= CARD_AGGR &&
exp->card > CARD_AGGR && !is_sql_aggr(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_aggr(f)) {
+ if (exp && outer && !is_sql_aggr(f) &&
!is_sql_aggr(query_fetch_outer_state(query, i))) {
if (used_lower_after_processed ||
query_outer_used_exp( query, i, exp, f)) {
sql_exp *lu =
used_lower_after_processed?exp:query_outer_last_used(query, i);
if (exp_name(lu) && exp_relname(lu) &&
!has_label(lu))
@@ -1241,7 +1233,6 @@ rel_column_ref(sql_query *query, sql_rel
return NULL;
if (!exp && query && query_has_outer(query)) {
int i;
- sql_rel *outer;
for (i=query_has_outer(query)-1; i>= 0 && !exp &&
(outer = query_fetch_outer(query,i)); i--) {
if (!(exp = rel_bind_column2(sql, outer, tname,
cname, f | sql_outer)) && sql->session->status == -ERR_AMBIGUOUS)
@@ -1326,10 +1317,16 @@ rel_column_ref(sql_query *query, sql_rel
} else if (dlist_length(l) >= 3) {
return sql_error(sql, 02, SQLSTATE(42000) "TODO: column names
of level >= 3");
}
- if (exp && !exp_is_atom(exp)) {
- query->last_exp = exp;
- query->last_state = f;
- }
+ if (exp && !exp_is_atom(exp) && rel && !outer) {
+ if (query->last_exp && query->last_rel == *rel &&
!is_sql_aggr(query->last_state) && is_sql_aggr(f)) {
+ if (!is_groupby(query->last_rel->op) ||
list_empty(query->last_rel->r) || !exps_find_exp(query->last_rel->r,
query->last_exp))
+ return sql_error(sql, ERR_NOTFOUND,
SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s.%s' in query
results without an aggregate function", exp_relname(query->last_exp),
exp_name(query->last_exp));
+ }
+ query->prev = query->last_exp;
+ query->last_exp = exp;
+ query->last_state = f;
+ query->last_rel = *rel;
+ }
return exp;
}
@@ -1899,8 +1896,10 @@ rel_exists_exp(sql_query *query, sql_rel
if (is_psm_call(f) || is_sql_merge(f))
return sql_error(sql, 02, SQLSTATE(42000) "%s: subqueries not
supported inside %s", is_psm_call(f) ? "CALL" : "MERGE", is_psm_call(f) ? "CALL
statements" : "MERGE conditions");
- if (rel)
+ if (rel) {
+ query_processed(query);
query_push_outer(query, rel, f);
+ }
sq = rel_subquery(query, NULL, sc->data.sym, ek);
if (rel)
rel = query_pop_outer(query);
@@ -2772,6 +2771,8 @@ rel_logical_exp(sql_query *query, sql_re
if (!sq)
return NULL;
assert(ek.type == type_value); /* I don't expect IN tuple
matching calls to land here */
+ if (is_sql_where(f) && is_groupby(rel->op))
+ assert(0);
if (ek.card <= card_set && is_project(sq->op) &&
list_length(sq->exps) > 1)
return sql_error(sql, 02, SQLSTATE(42000) "SELECT:
subquery must return only one column");
if (!rel)
@@ -3490,6 +3491,10 @@ static sql_exp *
else
groupby = subquery = gl;
}
+ /*
+ if (!subquery && exp_has_rel(e))
+ subquery = gl;
+ */
if (!exp_subtype(e)) { /* we also do not expect
parameters here */
char *uaname = SA_NEW_ARRAY(sql->ta, char,
strlen(aname) + 1);
return sql_error(sql, 02, SQLSTATE(42000) "%s:
parameters not allowed as arguments to aggregate functions",
toUpperCopy(uaname, aname));
@@ -3580,7 +3585,7 @@ static sql_exp *
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);
- if (is_sql_aggr(sql_state))
+ if (0 && is_sql_aggr(sql_state))
return sql_error(sql, 05,
SQLSTATE(42000) "SELECT: aggregate function calls cannot be nested");
if (is_sql_values(sql_state))
return sql_error(sql, 05,
SQLSTATE(42000) "SELECT: aggregate functions not allowed on an unique value");
@@ -3648,7 +3653,7 @@ static sql_exp *
}
if (!subquery && groupby && groupby->op != op_groupby) {
/* implicit groupby */
- if (query->last_exp && !is_sql_aggr(query->last_state)) {
+ if (!all_freevar && query->last_exp &&
!is_sql_aggr(query->last_state)) {
return sql_error(sql, ERR_GROUPBY, SQLSTATE(42000)
"SELECT: cannot use non GROUP BY column '%s.%s' in query results without an
aggregate function", exp_relname(query->last_exp), exp_name(query->last_exp));
}
res = groupby = rel_groupby(sql, groupby, NULL);
@@ -3671,6 +3676,10 @@ static sql_exp *
query_update_outer(query, res, all_freevar-1);
} else if (rel) {
*rel = res;
+ if (query->last_rel != res) {
+ query->last_rel = res;
+ query->last_state |= sql_aggr;
+ }
}
if (!has_args) { /* count(*) case */
@@ -5174,8 +5183,16 @@ rel_value_exp2(sql_query *query, sql_rel
assert(se->token == SQL_SELECT);
r = rel_subquery(query, NULL, se, ek);
}
- if (rel && *rel)
+ if (rel && *rel) {
*rel = query_pop_outer(query);
+ if (is_sql_join(f) && is_groupby((*rel)->op)) {
+ return sql_error(sql, 05, SQLSTATE(42000)
"SELECT: aggregate functions not allowed in JOIN conditions");
+ } else if (is_sql_where(f) && is_groupby((*rel)->op)) {
+ return sql_error(sql, 02, SQLSTATE(42000)
"SELECT: aggregate functions not allowed in WHERE clause");
+ } else if ((is_sql_update_set(f) || is_sql_psm(f)) &&
is_groupby((*rel)->op)) {
+ return sql_error(sql, 02, SQLSTATE(42000)
"SELECT: aggregate functions not allowed in SET, WHILE, IF, ELSE, CASE, WHEN,
RETURN, ANALYZE clauses");
+ }
+ }
if (!r)
return NULL;
if (ek.type == type_value && ek.card <= card_set &&
is_project(r->op) && list_length(r->exps) > 1)
@@ -5295,10 +5312,15 @@ rel_value_exp(sql_query *query, sql_rel
if (rel && *rel && !exp_has_rel(ne)) {
e = ne;
rel_bind_var(query->sql, *rel, e);
- if (exp_has_freevar(query->sql, e) &&
is_sql_aggr(f)) {
- sql_rel *outer =
query_fetch_outer(query, exp_has_freevar(query->sql, e)-1);
-
query_outer_pop_last_used(query, exp_has_freevar(query->sql, e)-1);
- reset_outer(outer);
+ 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 {
+ e->freevar = 0;
+ }
}
}
}
@@ -5939,12 +5961,9 @@ rel_joinquery_(sql_query *query, sql_rel
t1 = table_ref(query, NULL, tab1, 0, refs);
}
if (t1) {
- t2 = table_ref(query, NULL, tab2, 0, refs);
- if (lateral && !t2 && sql->session->status != -ERR_AMBIGUOUS) {
- /* reset error */
- sql->session->status = 0;
- sql->errstr[0] = 0;
-
+ if (!lateral)
+ t2 = table_ref(query, NULL, tab2, 0, refs);
+ else if (lateral && !t2) {
query_push_outer(query, t1, sql_from);
t2 = table_ref(query, NULL, tab2, 0, refs);
t1 = query_pop_outer(query);
@@ -5955,6 +5974,7 @@ rel_joinquery_(sql_query *query, sql_rel
if (!t1 || !t2)
return NULL;
+ query_processed(query);
inner = rel = rel_crossproduct(sql->sa, t1, t2, op_join);
inner->op = op;
if (lateral)
@@ -6071,22 +6091,15 @@ sql_rel *
rel_subquery(sql_query *query, sql_rel *rel, symbol *sq, exp_kind ek)
{
mvc *sql = query->sql;
- sql_exp *last_exp = query->last_exp;
- int last_state = query->last_state;
+ int toplevel = (!rel || (rel->op == op_project && (!rel->exps ||
list_length(rel->exps) == 0)));
+
query_processed(query);
- int toplevel = 0;
-
if (!stack_push_frame(sql, NULL))
return sql_error(sql, 02, SQLSTATE(HY013) MAL_MALLOC_FAIL);
-
- if (!rel || (rel->op == op_project &&
- (!rel->exps || list_length(rel->exps) == 0)))
- toplevel = 1;
-
rel = rel_query(query, rel, sq, toplevel, ek);
stack_pop_frame(sql);
- query->last_exp = last_exp;
- query->last_state = last_state;
+ if (toplevel)
+ query_processed(query);
if (rel && ek.type == type_relation && ek.card < card_set && rel->card
>= CARD_AGGR)
return rel_zero_or_one(sql, rel, ek);
return rel;
diff --git a/sql/server/rel_updates.c b/sql/server/rel_updates.c
--- a/sql/server/rel_updates.c
+++ b/sql/server/rel_updates.c
@@ -1050,8 +1050,11 @@ update_generate_assignments(sql_query *q
if (r)
query_push_outer(query, r, sql_sel |
sql_update_set);
rel_val = rel_subquery(query, NULL, a, ek);
- if (r)
+ if (r) {
r = query_pop_outer(query);
+ if (r && is_groupby(r->op))
+ return sql_error(sql, 02,
SQLSTATE(42000) "SELECT: aggregate functions not allowed in SET, WHILE, IF,
ELSE, CASE, WHEN, RETURN, ANALYZE clauses");
+ }
outer = 1;
}
if ((single && !v) || (!single && !rel_val))
@@ -2102,5 +2105,6 @@ rel_updates(sql_query *query, symbol *s)
default:
return sql_error(sql, 01, SQLSTATE(42000) "Updates statement
unknown Symbol(%p)->token = %s", s, token2string(s->token));
}
+ query_processed(query);
return ret;
}
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
@@ -10,15 +10,27 @@
#include "sql_query.h"
#include "rel_rel.h"
+void
+query_processed(sql_query *query )
+{
+ query -> last_rel = NULL;
+ query -> last_exp = NULL;
+ query -> last_state = 0;
+ query -> last_card = 0;
+}
+
static stacked_query *
-sq_create( sql_allocator *sa, sql_rel *rel, int sql_state)
+sq_create( sql_allocator *sa, sql_rel *rel, sql_exp *exp, sql_exp *prev, int
sql_state, int card)
{
stacked_query *q = SA_NEW(sa, stacked_query);
assert(rel);
*q = (stacked_query) {
.rel = rel,
+ .last_used = exp,
.sql_state = sql_state,
+ .prev = prev,
+ .used_card = card,
.grouped = is_groupby(rel->op)|rel->grouped,
.groupby = 0, /* not used for groupby of inner */
};
@@ -28,7 +40,7 @@ sq_create( sql_allocator *sa, sql_rel *r
sql_query *
query_create( mvc *sql)
{
- sql_query *q = SA_NEW(sql->sa, sql_query);
+ sql_query *q = SA_ZNEW(sql->sa, sql_query);
q->sql = sql;
q->outer = sql_stack_new(sql->sa, 32);
@@ -38,7 +50,18 @@ query_create( mvc *sql)
void
query_push_outer(sql_query *q, sql_rel *r, int sql_state)
{
- stacked_query *sq = sq_create(q->sql->sa, r, sql_state);
+ if (!q->last_rel)
+ q->last_rel = r;
+ if (r != q->last_rel) {
+ assert((q->last_rel->l == r && (is_groupby(q->last_rel->op) ||
is_select(q->last_rel->op))) || (r->l == q->last_rel && (is_select(r->op) ||
is_join(r->op))));
+ r->grouped = is_groupby(q->last_rel->op);
+ q->last_rel = r;
+ q->last_exp = q->prev = NULL;
+ q->last_state = 0;
+ q->last_card = 0;
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
