Changeset: 01234c17413d for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/01234c17413d
Modified Files:
sql/server/rel_select.c
sql/test/subquery/Tests/correlated.test
sql/test/subquery/Tests/subquery3.test
sql/test/subquery/Tests/subquery4.test
sql/test/subquery/Tests/subquery5.test
sql/test/subquery/Tests/subquery6.test
Branch: default
Log Message:
give proper errors on use of ungrouped columns (before or after finding an
aggregate)
diffs (178 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
@@ -3395,12 +3395,12 @@ exp_valid(visitor *v, sql_rel *rel, sql_
ai->err = SQLSTATE(42000) "SELECT: subquery
uses ungrouped column from outer query";
}
}
- } else if (!v->changes && vf && vf == ai->groupby) {
+ } else if (!v->changes && vf && vf == ai->groupby) { /* check if input
is allready aggregated */
sql_rel *sq = query_fetch_outer(ai->query, vf-1);
-
- /* problem freevar have cardinality CARD_ATOM */
+ sql_exp *a = NULL;
+
if (sq->card <= CARD_AGGR && is_alias(e->type)) {
- if (exps_bind_column(sq->exps, e->l, e->r, NULL, 0)) {
/* aggregate */
+ if ((a = exps_bind_column(sq->exps, e->l, e->r, NULL,
0)) && is_aggr(a->type)) { /* aggregate */
v->changes = 1;
ai->err = SQLSTATE(42000) "SELECT: aggregate
function calls cannot be nested";
}
@@ -3492,10 +3492,6 @@ 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));
@@ -3578,6 +3574,16 @@ static sql_exp *
card = query_outer_used_card(query, all_freevar-1);
/* given groupby validate all input expressions */
char *err;
+ if (groupby && !is_groupby(groupby->op)) {
+ sql_exp *p = query_outer_last_used(query,
all_freevar-1);
+ if (p && !is_aggr(p->type) &&
!is_groupby_col(groupby, p)) {
+ if (p->type == e_column)
+ return sql_error(sql,
ERR_GROUPBY, SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s.%s' in
query results without an aggregate function", (char*)p->l, (char*)p->r);
+ if (exp_name(p) && exp_relname(p) &&
!has_label(p))
+ 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(p), exp_name(p));
+ return sql_error(sql, ERR_GROUPBY,
SQLSTATE(42000) "SELECT: cannot use non GROUP BY column in query results
without an aggregate function");
+ }
+ }
if ((err = exps_valid(query, exps, all_freevar)) !=
NULL) {
strcpy(sql->errstr, err);
sql->session->status = -ERR_GROUPBY;
diff --git a/sql/test/subquery/Tests/correlated.test
b/sql/test/subquery/Tests/correlated.test
--- a/sql/test/subquery/Tests/correlated.test
+++ b/sql/test/subquery/Tests/correlated.test
@@ -258,10 +258,10 @@ SELECT MIN(i), CAST((SELECT SUM(i1.i)) A
1
6
-statement error 42000!SELECT: cannot use non GROUP BY column 'i' in query
results without an aggregate function
+statement error 42000!SELECT: cannot use non GROUP BY column 'i1.i' in query
results without an aggregate function
SELECT i, (SELECT SUM(i1.i)) FROM integers i1
-statement error 42000!SELECT: cannot use non GROUP BY column in query results
without an aggregate function
+statement error 42000!SELECT: cannot use non GROUP BY column 'i1.i' in query
results without an aggregate function
SELECT i+1, (SELECT SUM(i1.i)) FROM integers i1
query II rowsort
diff --git a/sql/test/subquery/Tests/subquery3.test
b/sql/test/subquery/Tests/subquery3.test
--- a/sql/test/subquery/Tests/subquery3.test
+++ b/sql/test/subquery/Tests/subquery3.test
@@ -522,7 +522,7 @@ SELECT
(SELECT 1 FROM integers i2 GROUP BY SUM(i2.i))
FROM integers i1
-statement error 42000!SELECT: aggregate functions not allowed in WHERE clause
+statement error 42000!SELECT: cannot use non GROUP BY column 'another_t.col5'
in query results without an aggregate function
SELECT
1
FROM another_T
@@ -669,7 +669,7 @@ SELECT i FROM integers i1 ORDER BY SUM(i
statement error 42000!SELECT: cannot use non GROUP BY column 'i' in query
results without an aggregate function
SELECT * FROM integers i1 ORDER BY (SELECT SUM(i1.i) FROM integers i2)
-statement error 42000!SELECT: cannot use non GROUP BY column 'i' in query
results without an aggregate function
+statement error 42000!SELECT: cannot use non GROUP BY column 'i1.i' in query
results without an aggregate function
SELECT i FROM integers i1 ORDER BY (SELECT SUM(i1.i) FROM integers i2)
statement ok
diff --git a/sql/test/subquery/Tests/subquery4.test
b/sql/test/subquery/Tests/subquery4.test
--- a/sql/test/subquery/Tests/subquery4.test
+++ b/sql/test/subquery/Tests/subquery4.test
@@ -368,7 +368,7 @@ 1786
186
26
-statement error 42000!SELECT: aggregate functions not allowed in WHERE clause
+statement error 42000!SELECT: cannot use non GROUP BY column 'another_t.col5'
in query results without an aggregate function
UPDATE another_T SET col5 = 1 WHERE col5 = (SELECT AVG(col2))
statement ok rowcount 0
@@ -383,7 +383,7 @@ UPDATE another_T SET col7 = 1 WHERE col5
statement error 42000!COUNT: aggregate functions not allowed in WHERE clause
DELETE FROM another_T WHERE col1 = COUNT(col2)
-statement error 42000!SELECT: aggregate functions not allowed in WHERE clause
+statement error 42000!SELECT: cannot use non GROUP BY column 'another_t.col7'
in query results without an aggregate function
DELETE FROM another_T WHERE col7 = (SELECT MIN(col3))
statement ok rowcount 0
diff --git a/sql/test/subquery/Tests/subquery5.test
b/sql/test/subquery/Tests/subquery5.test
--- a/sql/test/subquery/Tests/subquery5.test
+++ b/sql/test/subquery/Tests/subquery5.test
@@ -16,7 +16,7 @@ CREATE TABLE integers(i INTEGER)
statement ok rowcount 4
INSERT INTO integers VALUES (1), (2), (3), (NULL)
-statement error 42000!SELECT: subquery uses ungrouped column "another_t.col2"
from outer query
+statement error 42000!SELECT: cannot use non GROUP BY column 'another_t.col2'
in query results without an aggregate function
SELECT
(SELECT MIN(col1) GROUP BY col2)
FROM another_T
@@ -125,7 +125,7 @@ 1
1
NULL
-statement error 42000!SELECT: subquery uses ungrouped column "i1.i" from outer
query
+statement error 42000!SELECT: cannot use non GROUP BY column 'i1.i' in query
results without an aggregate function
SELECT (SELECT i1.i IN (SELECT SUM(i1.i))) FROM integers i1
query R rowsort
@@ -181,7 +181,7 @@ SELECT (SELECT 1 WHERE SUM(col2 + 1) > 0
----
1
-statement error 42000!SELECT: subquery uses ungrouped column "another_t.col1"
from outer query
+statement error 42000!SELECT: cannot use non GROUP BY column 'another_t.col1'
in query results without an aggregate function
SELECT (SELECT col1 HAVING SUM(col2 + col1) > 0) FROM another_t
statement error 42000!SELECT: identifier 'col1' ambiguous
@@ -341,13 +341,13 @@ 1
1
1
-statement error 42000!SELECT: subquery uses ungrouped column "i1.i" from outer
query
+statement error 42000!SELECT: cannot use non GROUP BY column 'i1.i' in query
results without an aggregate function
SELECT (SELECT 1 FROM integers i2 INNER JOIN integers i3 ON i1.i = 1) =
(SELECT 1 FROM integers i2 INNER JOIN integers i3 ON MIN(i1.i) = 1) FROM
integers i1
-statement error 42000!SELECT: subquery uses ungrouped column "i1.i" from outer
query
+statement error 42000!SELECT: cannot use non GROUP BY column 'i1.i' in query
results without an aggregate function
SELECT (SELECT i1.i) = (SELECT SUM(i1.i)) FROM integers i1
-statement error 42000!SELECT: subquery uses ungrouped column "another_t.col1"
from outer query
+statement error 42000!SELECT: cannot use non GROUP BY column 'another_t.col1'
in query results without an aggregate function
SELECT (VALUES(col1)), (VALUES(MAX(col2))) FROM another_t
statement error 42000!SELECT: aggregate function calls cannot be nested
@@ -447,7 +447,7 @@ SELECT (VALUES(SUM(i1.i)) UNION VALUES(A
statement error GDK reported error: mergejoin: more than one match
SELECT ((SELECT SUM(i1.i)) UNION ALL (SELECT AVG(i1.i))) FROM integers i1
-statement error 42000!SELECT: subquery uses ungrouped column "i1.i" from outer
query
+statement error 42000!SELECT: cannot use non GROUP BY column 'i1.i' in query
results without an aggregate function
SELECT ((SELECT i1.i NOT IN (SELECT i1.i)) UNION (SELECT SUM(i1.i) IN (SELECT
i1.i))) FROM integers i1
query I rowsort
diff --git a/sql/test/subquery/Tests/subquery6.test
b/sql/test/subquery/Tests/subquery6.test
--- a/sql/test/subquery/Tests/subquery6.test
+++ b/sql/test/subquery/Tests/subquery6.test
@@ -419,10 +419,10 @@ select min(i) as myx from integers group
statement error 42000!SELECT: cannot use non GROUP BY column 'integers.i' in
query results without an aggregate function
select ntile(i) over (), count(*) from integers
-statement error 42000!SELECT: cannot use non GROUP BY column in query results
without an aggregate function
+statement error 42000!SELECT: cannot use non GROUP BY column 'i2.i' in query
results without an aggregate function
select (select i2.i in (select sum(i1.i + i2.i)) from integers i2) from
integers i1
-statement error 42000!SELECT: cannot use non GROUP BY column in query results
without an aggregate function
+statement error 42000!SELECT: cannot use non GROUP BY column 'i2.i' in query
results without an aggregate function
select (select i2.i in (select sum(i1.i + i2.i)) from integers i2) from
integers i1 group by i1.i
statement error GDK reported error: mergejoin: more than one match
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
