Changeset: 377f135a234e for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=377f135a234e
Modified Files:
sql/server/rel_select.c
sql/server/rel_unnest.c
sql/test/subquery/Tests/subquery5.sql
sql/test/subquery/Tests/subquery5.stable.err
Branch: Jun2020
Log Message:
Some changes.
Allow binding of outer query colums at more possible locations of a subquery.
Corrected identations.
We have to fix unnesting of VALUES clauses.
Added queries with with wrong results.
diffs (99 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
@@ -1173,21 +1173,18 @@ rel_column_ref(sql_query *query, sql_rel
exp = rel_bind_column(sql, inner, name, f, 0);
if (!exp && inner && is_sql_having(f) && inner->op == op_select)
inner = inner->l;
- if (!exp && inner && (is_sql_having(f) || is_sql_aggr(f)) &&
is_groupby(inner->op)) {
+ if (!exp && inner && (is_sql_having(f) || is_sql_aggr(f)) &&
is_groupby(inner->op))
exp = rel_bind_column(sql, inner->l, name, f, 0);
- }
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--) {
exp = rel_bind_column(sql, outer, name, f, 0);
- if (!exp && (is_sql_having(f) ||
is_sql_aggr(f)) && is_groupby(outer->op)) {
+ if (!exp && is_groupby(outer->op))
exp = rel_bind_column(sql, outer->l,
name, f, 0);
- }
- if (exp && is_simple_project(outer->op) &&
!rel_find_exp(outer, exp)) {
+ if (exp && is_simple_project(outer->op) &&
!rel_find_exp(outer, exp))
exp = rel_project_add_exp(sql, outer,
exp);
- }
if (exp)
break;
}
@@ -1196,7 +1193,7 @@ rel_column_ref(sql_query *query, sql_rel
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));
+ return sql_error(sql, ERR_GROUPBY,
SQLSTATE(42000) "SELECT: subquery uses ungrouped column \"%s.%s\" from outer
query", exp_relname(lu), exp_name(lu));
}
}
if (exp) {
diff --git a/sql/server/rel_unnest.c b/sql/server/rel_unnest.c
--- a/sql/server/rel_unnest.c
+++ b/sql/server/rel_unnest.c
@@ -775,7 +775,7 @@ push_up_project(mvc *sql, sql_rel *rel,
for (node *nn = atoms->h ; nn ; nn =
nn->next) {
sql_exp *ee = (sql_exp *)
nn->data;
- exp_setname(sql->sa, ee,
exp_relname(e), exp_name(e));
+ exp_setname(sql->sa, ee,
exp_relname(e), exp_name(e));
append(n->exps, ee);
}
} else
@@ -797,7 +797,7 @@ push_up_project(mvc *sql, sql_rel *rel,
/* remove old project */
rel->r = r->l;
r->l = NULL;
- rel_destroy(r);
+ rel_destroy(r);
return n;
}
}
diff --git a/sql/test/subquery/Tests/subquery5.sql
b/sql/test/subquery/Tests/subquery5.sql
--- a/sql/test/subquery/Tests/subquery5.sql
+++ b/sql/test/subquery/Tests/subquery5.sql
@@ -59,6 +59,19 @@ SELECT evilfunction(1);
SELECT evilfunction(1), 1;
--error, more than one row returned by a subquery used as an expression
+SELECT 1 FROM another_t t1 HAVING 1 = ANY (SELECT col1); --error, subquery
uses ungrouped column "col1" from outer query
+
+SELECT 1 FROM another_t t1 HAVING 1 = ANY (SELECT 1 WHERE col1); --error,
subquery uses ungrouped column "col1" from outer query
+
+SELECT col1 FROM another_t t1 GROUP BY col1 HAVING 1 = ANY (SELECT col1);
+ -- 1
+
+SELECT (SELECT i = ANY(VALUES(1), (i))) FROM integers;
+ -- True
+ -- True
+ -- True
+ -- NULL
+
DROP FUNCTION evilfunction(INT);
DROP TABLE tbl_ProductSales;
DROP TABLE another_T;
diff --git a/sql/test/subquery/Tests/subquery5.stable.err
b/sql/test/subquery/Tests/subquery5.stable.err
--- a/sql/test/subquery/Tests/subquery5.stable.err
+++ b/sql/test/subquery/Tests/subquery5.stable.err
@@ -42,6 +42,14 @@ MAPI = (monetdb) /var/tmp/mtest-232134/
QUERY = SELECT evilfunction(1), 1;
ERROR = !Cardinality violation, scalar value expected
CODE = 21000
+MAPI = (monetdb) /var/tmp/mtest-488866/.s.monetdb.39586
+QUERY = SELECT 1 FROM another_t t1 HAVING 1 = ANY (SELECT col1); --error,
subquery uses ungrouped column "col1" from outer query
+ERROR = !SELECT: cannot use non GROUP BY column 'col1' in query results
without an aggregate function
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-488866/.s.monetdb.39586
+QUERY = SELECT 1 FROM another_t t1 HAVING 1 = ANY (SELECT 1 WHERE col1);
--error, subquery uses ungrouped column "col1" from outer query
+ERROR = !SELECT: cannot use non GROUP BY column 'col1' in query results
without an aggregate function
+CODE = 42000
# 08:41:39 >
# 08:41:39 > "Done."
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
