Changeset: 57d790bf2817 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=57d790bf2817
Modified Files:
sql/server/rel_select.c
sql/test/subquery/Tests/subquery6.sql
sql/test/subquery/Tests/subquery6.stable.err
Branch: Jun2020
Log Message:
Verify cardinality of expression list at the right side of IN operator
diffs (66 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
@@ -2126,6 +2126,18 @@ rel_in_value_exp(sql_query *query, sql_r
append(vals, re);
}
+ if (rel && *rel)
+ for (node *n = vals->h ; n ; n = n->next) {
+ sql_exp *e = n->data;
+
+ if (!exp_is_rel(e) && e->card > (*rel)->card) {
+ if (exp_name(e))
+ return sql_error(sql,
ERR_GROUPBY, SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s' in
query results without an aggregate function", exp_name(e));
+ else
+ return sql_error(sql,
ERR_GROUPBY, SQLSTATE(42000) "SELECT: cannot use non GROUP BY column in query
results without an aggregate function");
+ }
+ }
+
values = exp_values(sql->sa, vals);
exp_label(sql->sa, values, ++sql->label);
if (is_tuple) {
diff --git a/sql/test/subquery/Tests/subquery6.sql
b/sql/test/subquery/Tests/subquery6.sql
--- a/sql/test/subquery/Tests/subquery6.sql
+++ b/sql/test/subquery/Tests/subquery6.sql
@@ -42,22 +42,22 @@ SELECT i FROM integers i1 WHERE (SELECT
SELECT (SELECT (SELECT SUM(col1)) IN (MAX(col2))) FROM another_t;
-- False
+SELECT 1 IN (col4, MIN(col2)) FROM another_t;
+ --error, column "another_t.col4" must appear in the GROUP BY clause or
be used in an aggregate function
+
SELECT (SELECT col1) IN ('not a number') FROM another_t;
-- error, cannot cast string into number
SELECT (SELECT (SELECT SUM(col1)) IN (MAX(col2), '12')) FROM another_t;
-- False
-SELECT 1 IN (col4, MIN(col2)) FROM another_t;
- --error, column "another_t.col4" must appear in the GROUP BY clause or
be used in an aggregate function
-
SELECT CASE WHEN ColID IS NULL THEN CAST(Product_Category AS INT) ELSE
TotalSales END FROM tbl_ProductSales;
-- 200
-- 400
-- 500
-- 100
-SELECT ColID FROM tbl_ProductSales WHERE CASE WHEN ColID IS NULL THEN
CAST(Product_Category AS INT) < 0 ELSE TotalSales > 0 END;
+SELECT ColID FROM tbl_ProductSales WHERE CASE WHEN ColID IS NULL THEN
CAST(Product_Category AS INT) ELSE TotalSales END;
-- 1
-- 2
-- 3
diff --git a/sql/test/subquery/Tests/subquery6.stable.err
b/sql/test/subquery/Tests/subquery6.stable.err
--- a/sql/test/subquery/Tests/subquery6.stable.err
+++ b/sql/test/subquery/Tests/subquery6.stable.err
@@ -23,6 +23,10 @@ ERROR = !GDK reported error: BATsubcross
MAPI = (monetdb) /var/tmp/mtest-3499083/.s.monetdb.30277
QUERY = SELECT i FROM integers i1 WHERE (SELECT CASE WHEN i1.i IS NULL THEN
(SELECT FALSE FROM integers i2) ELSE TRUE END);
ERROR = !GDK reported error: BATsubcross: more than one match
+MAPI = (monetdb) /var/tmp/mtest-18680/.s.monetdb.31144
+QUERY = SELECT 1 IN (col4, MIN(col2)) FROM another_t;
+ERROR = !SELECT: cannot use non GROUP BY column 'col4' in query results
without an aggregate function
+CODE = 42000
# 11:45:43 >
# 11:45:43 > "Done."
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
