Changeset: b8154e9020f3 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=b8154e9020f3
Modified Files:
sql/server/rel_select.c
sql/test/subquery/Tests/subquery4.sql
sql/test/subquery/Tests/subquery4.stable.err
sql/test/subquery/Tests/subquery4.stable.out
Branch: default
Log Message:
merged
diffs (169 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
@@ -2354,14 +2354,20 @@ rel_logical_value_exp(sql_query *query,
case SQL_UNION:
case SQL_EXCEPT:
case SQL_INTERSECT: {
+ sql_rel *sq;
+
+ if (is_psm_call(f))
+ return sql_error(sql, 02, SQLSTATE(42000) "CALL:
subqueries not allowed inside CALL statements");
if (rel && *rel)
query_push_outer(query, *rel, f);
- sql_rel *sq = rel_setquery(query, sc);
+ sq = rel_setquery(query, sc);
if (rel && *rel)
*rel = query_pop_outer(query);
- if (sq)
- return exp_rel(sql, sq);
- return NULL;
+ if (!sq)
+ return NULL;
+ 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");
+ return exp_rel(sql, sq);
}
case SQL_DEFAULT:
return sql_error(sql, 02, SQLSTATE(42000) "DEFAULT keyword not
allowed outside insert and update statements");
@@ -5049,6 +5055,8 @@ rel_value_exp2(sql_query *query, sql_rel
case SQL_SELECT: {
sql_rel *r = NULL;
+ if (is_psm_call(f))
+ return sql_error(sql, 02, SQLSTATE(42000) "CALL:
subqueries not allowed inside CALL statements");
if (se->token == SQL_WITH) {
r = rel_with_query(query, se);
} else if (se->token == SQL_VALUES) {
diff --git a/sql/test/subquery/Tests/subquery4.sql
b/sql/test/subquery/Tests/subquery4.sql
--- a/sql/test/subquery/Tests/subquery4.sql
+++ b/sql/test/subquery/Tests/subquery4.sql
@@ -33,6 +33,14 @@ FROM integers i1;
-- 1
SELECT
+ (SELECT 1,1 UNION ALL SELECT 2,2)
+FROM integers i1; --error, subquery must return only one column
+
+SELECT
+ (SELECT 1 UNION ALL SELECT 2)
+FROM integers i1; --error, more than one row returned by a subquery used as an
expression
+
+SELECT
(SELECT i2.i FROM evilfunction(MIN(1)) as i2(i))
FROM integers i1; -- error, aggregate functions are not allowed in functions
in FROM
@@ -51,7 +59,7 @@ FROM integers i1;
SELECT 1 FROM evilfunction((SELECT MAX(1) OVER ()));
-- 1
-SELECT 1 FROM evilfunction((SELECT MAX(1) OVER () UNION ALL SELECT 1));
--error, more than one row returned by a subquery used as an expression
+SELECT 1 FROM evilfunction((SELECT MAX(1) OVER () UNION ALL SELECT 1));
SELECT
(SELECT 1 FROM evilfunction((SELECT MAX(1) OVER () UNION ALL SELECT 1)))
@@ -68,6 +76,25 @@ FROM integers i1;
-- 3
-- NULL
+SELECT i FROM integers WHERE (SELECT 1 UNION ALL SELECT 2); --error, more than
one row returned by a subquery used as an expression
+
+SELECT i FROM integers WHERE (SELECT true UNION ALL SELECT false); --error,
more than one row returned by a subquery used as an expression
+
+SELECT i FROM integers WHERE (SELECT true, false); --error, subquery must
return only one column
+
+SELECT i FROM integers WHERE (SELECT true, false UNION ALL SELECT false,
true); --error, subquery must return only one column
+
+SELECT i FROM integers WHERE (SELECT COUNT(1) OVER ()) = 1;
+ -- 1
+ -- 2
+ -- 3
+ -- NULL
+
+SELECT i FROM integers WHERE (SELECT COUNT(i) OVER ()) = 1;
+ -- 1
+ -- 2
+ -- 3
+
UPDATE another_T SET col1 = MIN(col1); --error, aggregates not allowed in
update set clause
UPDATE another_T SET col2 = 1 WHERE col1 = SUM(col2); --error, aggregates not
allowed in update set clause
UPDATE another_T SET col3 = (SELECT MAX(col5)); --error, aggregates not
allowed in update set clause
@@ -106,15 +133,21 @@ CREATE PROCEDURE crashme(a int) BEGIN DE
CALL crashme(COUNT(1)); --error, not allowed
CALL crashme(COUNT(1) OVER ()); --error, not allowed
-CALL crashme((SELECT COUNT(1)));
-CALL crashme((SELECT COUNT(1) OVER ())); --should be allowed, it returns
exactly one row
-CALL crashme((SELECT 1 UNION ALL SELECT 2)); --error, returns more than one row
+CALL crashme((SELECT COUNT(1))); --error, subquery at CALL
+CALL crashme((SELECT COUNT(1) OVER ())); --error, subquery at CALL
+CALL crashme((SELECT 1 UNION ALL SELECT 2)); --error, subquery at CALL
create sequence "debugme" as integer start with 1;
alter sequence "debugme" restart with (select MAX(1));
alter sequence "debugme" restart with (select MIN(1) OVER ());
drop sequence "debugme";
+CREATE FUNCTION upsme(input INT) RETURNS INT BEGIN RETURN SELECT MIN(input)
OVER (); END;
+
+SELECT upsme(1);
+SELECT upsme(1);
+
+DROP FUNCTION upsme(INT);
DROP FUNCTION evilfunction(INT);
DROP FUNCTION evilfunction(INT, INT);
DROP PROCEDURE crashme(INT);
diff --git a/sql/test/subquery/Tests/subquery4.stable.err
b/sql/test/subquery/Tests/subquery4.stable.err
--- a/sql/test/subquery/Tests/subquery4.stable.err
+++ b/sql/test/subquery/Tests/subquery4.stable.err
@@ -30,7 +30,13 @@ QUERY = PREPARE SELECT
FROM another_T;
ERROR = !Could not determine type for argument number 1
CODE = 42000
-MAPI = (monetdb) /var/tmp/mtest-120241/.s.monetdb.31512
+MAPI = (monetdb) /var/tmp/mtest-99553/.s.monetdb.37320
+QUERY = SELECT
+ (SELECT 1,1 UNION ALL SELECT 2,2)
+ FROM integers i1; --error, subquery must return only one column
+ERROR = !SELECT: subquery must return only one column
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-99553/.s.monetdb.37320
QUERY = SELECT
(SELECT i2.i FROM evilfunction(MIN(1)) as i2(i))
FROM integers i1; -- error, aggregate functions are not allowed in
functions in FROM
@@ -132,6 +138,18 @@ MAPI = (monetdb) /var/tmp/mtest-60261/.
QUERY = CALL crashme(COUNT(1) OVER ()); --error, not allowed
ERROR = !COUNT: window function 'count' not allowed in CALL
CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-92133/.s.monetdb.34612
+QUERY = CALL crashme((SELECT COUNT(1))); --error, subquery at CALL
+ERROR = !CALL: subqueries not allowed inside CALL statements
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-92133/.s.monetdb.34612
+QUERY = CALL crashme((SELECT COUNT(1) OVER ())); --error, subquery at CALL
+ERROR = !CALL: subqueries not allowed inside CALL statements
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-92133/.s.monetdb.34612
+QUERY = CALL crashme((SELECT 1 UNION ALL SELECT 2)); --error, subquery at CALL
+ERROR = !CALL: subqueries not allowed inside CALL statements
+CODE = 42000
# 15:41:18 >
# 15:41:18 > "Done."
diff --git a/sql/test/subquery/Tests/subquery4.stable.out
b/sql/test/subquery/Tests/subquery4.stable.out
--- a/sql/test/subquery/Tests/subquery4.stable.out
+++ b/sql/test/subquery/Tests/subquery4.stable.out
@@ -83,6 +83,13 @@ stdout of test 'subquery4` in directory
% tinyint # type
% 1 # length
[ 1 ]
+#SELECT 1 FROM evilfunction((SELECT MAX(1) OVER () UNION ALL SELECT 1));
+% . # table_name
+% single_value # name
+% tinyint # type
+% 1 # length
+[ 1 ]
+[ 1 ]
#SELECT i2.i FROM evilfunction((SELECT MAX(1) OVER ())) as i2(i);
% .i2 # table_name
% i # name
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
