Changeset: 0231c401d4bc for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=0231c401d4bc
Modified Files:
sql/server/rel_rel.c
sql/server/rel_select.c
sql/test/BugTracker-2018/Tests/sqlitelogictest-having-not-null-not-in.Bug-6557.sql
sql/test/BugTracker-2018/Tests/sqlitelogictest-having-not-null-not-in.Bug-6557.stable.err
Branch: Nov2019
Log Message:
Improved error handling for having clause
diffs (148 lines):
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
@@ -313,7 +313,6 @@ rel_bind_column2( mvc *sql, sql_rel *rel
return NULL;
}
-
sql_rel *
rel_inplace_setop(sql_rel *rel, sql_rel *l, sql_rel *r, operator_type setop,
list *exps)
{
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
@@ -1029,15 +1029,18 @@ rel_column_ref(sql_query *query, sql_rel
char *name = l->h->data.sval;
sql_arg *a = sql_bind_param(sql, name);
int var = stack_find_var(sql, name);
-
+
if (!exp && rel && *rel)
exp = rel_bind_column(sql, *rel, name, f);
if (!exp && query && query_has_outer(query)) {
int i;
sql_rel *outer;
- for (i=0; !exp && (outer = query_fetch_outer(query,i));
i++)
+ for (i=0; !exp && (outer = query_fetch_outer(query,i));
i++) {
exp = rel_bind_column(sql, outer, name, f);
+ if (!exp && is_sql_having(f) &&
is_groupby(outer->op))
+ exp = rel_bind_column(sql, outer->l,
name, f);
+ }
if (exp && outer && outer->card <= CARD_AGGR &&
exp->card > CARD_AGGR && !is_sql_aggr(f))
return sql_error(sql, 05, SQLSTATE(42000)
"SELECT: cannot use non GROUP BY column '%s' in query results without an
aggregate function", name);
if (exp) {
@@ -1053,7 +1056,7 @@ rel_column_ref(sql_query *query, sql_rel
return sql_error(sql, ERR_AMBIGUOUS,
SQLSTATE(42000) "SELECT: identifier '%s' ambiguous", name);
exp = exp_param(sql->sa, a->name, &a->type, 0);
}
- if (!exp && var) {
+ if (!exp && var) {
sql_rel *r = stack_find_rel_var(sql, name);
if (r) {
*rel = r;
@@ -1074,10 +1077,8 @@ rel_column_ref(sql_query *query, sql_rel
}
return sql_error(sql, 02, SQLSTATE(42000) "SELECT:
identifier '%s' unknown", name);
}
- if (exp && rel && *rel && (*rel)->card <= CARD_AGGR &&
exp->card > CARD_AGGR && is_sql_sel(f) && !is_sql_aggr(f)) {
+ if (exp && rel && *rel && (*rel)->card <= CARD_AGGR &&
exp->card > CARD_AGGR && is_sql_sel(f) && !is_sql_aggr(f))
return sql_error(sql, 05, SQLSTATE(42000) "SELECT:
cannot use non GROUP BY column '%s' in query results without an aggregate
function", name);
- }
-
} else if (dlist_length(l) == 2) {
char *tname = l->h->data.sval;
char *cname = l->h->next->data.sval;
@@ -1088,11 +1089,14 @@ rel_column_ref(sql_query *query, sql_rel
int i;
sql_rel *outer;
- for (i=0; !exp && (outer = query_fetch_outer(query,i));
i++)
+ for (i=0; !exp && (outer = query_fetch_outer(query,i));
i++) {
exp = rel_bind_column2(sql, outer, tname,
cname, f);
+ if (!exp && is_sql_having(f) &&
is_groupby(outer->op))
+ exp = rel_bind_column2(sql, outer->l,
tname, cname, f);
+ }
if (exp && outer && outer->card <= CARD_AGGR &&
exp->card > CARD_AGGR && !is_sql_aggr(f))
return sql_error(sql, 05, SQLSTATE(42000)
"SELECT: cannot use non GROUP BY column '%s.%s' in query results without an
aggregate function", tname, cname);
- if (exp) {
+ if (exp) {
set_freevar(exp);
exp->card = CARD_ATOM;
}
@@ -1112,7 +1116,7 @@ rel_column_ref(sql_query *query, sql_rel
}
}
if (!exp) {
- if (rel && *rel && (*rel)->card == CARD_AGGR &&
!is_sql_aggr(f) && (is_sql_sel(f) || is_sql_having(f))) {
+ if (rel && *rel && (*rel)->card <= CARD_AGGR &&
!is_sql_aggr(f) && (is_sql_sel(f) || is_sql_having(f))) {
sql_rel *gb = *rel;
while (gb->l && !is_groupby(gb->op) &&
is_project(gb->op))
@@ -1124,9 +1128,8 @@ rel_column_ref(sql_query *query, sql_rel
}
return sql_error(sql, 02, SQLSTATE(42S22) "SELECT: no
such column '%s.%s'", tname, cname);
}
- if (exp && rel && *rel && (*rel)->card == CARD_AGGR &&
exp->card > CARD_AGGR && is_sql_sel(f) && !is_sql_aggr(f)) {
+ if (exp && rel && *rel && (*rel)->card <= CARD_AGGR &&
exp->card > CARD_AGGR && is_sql_sel(f) && !is_sql_aggr(f))
return sql_error(sql, 05, SQLSTATE(42000) "SELECT:
cannot use non GROUP BY column '%s.%s' in query results without an aggregate
function", tname, cname);
- }
} else if (dlist_length(l) >= 3) {
return sql_error(sql, 02, SQLSTATE(42000) "TODO: column names
of level >= 3");
}
@@ -6309,7 +6312,7 @@ rel_select_exp(sql_query *query, sql_rel
relation
*/
sql_rel *o_inner = inner;
- list *te = NULL, *pre_prj = o_inner->exps;//*pre_prj =
rel_projections(sql, o_inner, NULL, 1, 1);
+ list *te = NULL, *pre_prj = o_inner->exps;//*pre_prj =
rel_projections(sql, o_inner, NULL, 1, 1);
sql_rel *pre_rel = o_inner;
sql_exp *ce = rel_column_exp(query, &inner, n->data.sym,
sql_sel);
@@ -6335,7 +6338,7 @@ rel_select_exp(sql_query *query, sql_rel
*/
if (!is_project(inner->op)) {
if (inner != o_inner && pre_prj) {
- pre_prj = rel_projections(sql, pre_rel,
NULL, 1, 1);
+ pre_prj = rel_projections(sql, pre_rel,
NULL, 1, 1);
inner = rel_project(sql->sa, inner,
pre_prj);
reset_processed(inner);
} else
diff --git
a/sql/test/BugTracker-2018/Tests/sqlitelogictest-having-not-null-not-in.Bug-6557.sql
b/sql/test/BugTracker-2018/Tests/sqlitelogictest-having-not-null-not-in.Bug-6557.sql
---
a/sql/test/BugTracker-2018/Tests/sqlitelogictest-having-not-null-not-in.Bug-6557.sql
+++
b/sql/test/BugTracker-2018/Tests/sqlitelogictest-having-not-null-not-in.Bug-6557.sql
@@ -1,6 +1,9 @@
CREATE TABLE tab0(col0 INTEGER, col1 INTEGER, col2 INTEGER);
INSERT INTO tab0 VALUES(83,0,38), (26,0,79), (43,81,24);
SELECT DISTINCT col2 FROM tab0 GROUP BY col2, col1 HAVING NOT NULL NOT IN (
AVG ( ALL + col1 ) );
+SELECT 1 FROM tab0 HAVING tab0.col1 IN ( 1 ); --error
+SELECT 1 FROM tab0 HAVING col1 IN ( 1 ); --error
+SELECT 1 FROM tab0 HAVING NULL IN ( tab0.col2 ); --error
SELECT DISTINCT tab0.col1 AS col2 FROM tab0 GROUP BY tab0.col1 HAVING NULL NOT
IN ( col2 ); --error
SELECT DISTINCT * FROM tab0 AS cor0 GROUP BY cor0.col1, cor0.col2, cor0.col0;
SELECT CAST(SUM(col0) AS BIGINT) FROM tab0 WHERE + + col0 BETWEEN NULL AND +
col2;
diff --git
a/sql/test/BugTracker-2018/Tests/sqlitelogictest-having-not-null-not-in.Bug-6557.stable.err
b/sql/test/BugTracker-2018/Tests/sqlitelogictest-having-not-null-not-in.Bug-6557.stable.err
---
a/sql/test/BugTracker-2018/Tests/sqlitelogictest-having-not-null-not-in.Bug-6557.stable.err
+++
b/sql/test/BugTracker-2018/Tests/sqlitelogictest-having-not-null-not-in.Bug-6557.stable.err
@@ -27,7 +27,19 @@ stderr of test 'sqlitelogictest-having-n
# 12:32:58 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-25333" "--port=30615"
# 12:32:58 >
-MAPI = (monetdb) /var/tmp/mtest-5168/.s.monetdb.37758
+MAPI = (monetdb) /var/tmp/mtest-3426/.s.monetdb.34192
+QUERY = SELECT 1 FROM tab0 HAVING tab0.col1 IN ( 1 ); --error
+ERROR = !SELECT: cannot use non GROUP BY column 'tab0.col1' in query results
without an aggregate function
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-3426/.s.monetdb.34192
+QUERY = SELECT 1 FROM tab0 HAVING col1 IN ( 1 ); --error
+ERROR = !SELECT: cannot use non GROUP BY column 'col1' in query results
without an aggregate function
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-3426/.s.monetdb.34192
+QUERY = SELECT 1 FROM tab0 HAVING NULL IN ( tab0.col2 ); --error
+ERROR = !SELECT: cannot use non GROUP BY column 'tab0.col2' in query results
without an aggregate function
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-30783/.s.monetdb.35089
QUERY = SELECT DISTINCT tab0.col1 AS col2 FROM tab0 GROUP BY tab0.col1 HAVING
NULL NOT IN ( col2 ); --error
ERROR = !SELECT: cannot use non GROUP BY column 'col2' in query results
without an aggregate function
CODE = 42000
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
