Changeset: 2195726074ae for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=2195726074ae
Modified Files:
sql/test/subquery/Tests/subquery4.sql
sql/test/subquery/Tests/subquery4.stable.err
sql/test/subquery/Tests/subquery4.stable.out
Branch: default
Log Message:
Added subquery tests on cross-products
diffs (88 lines):
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
@@ -120,6 +120,15 @@ SELECT (SELECT NTILE(i1.i) OVER (PARTITI
-- 1
-- NULL
+/* On joined tables, the correlation happens in the outer query */
+SELECT CAST((SELECT SUM(i2.i + i1.i)) AS BIGINT) FROM integers i1, integers i2;
+
+SELECT CAST((SELECT SUM(i2.i + i1.i)) AS BIGINT) FROM integers i1 INNER JOIN
integers i2 ON i1.i = i2.i;
+
+SELECT i1.i, i2.i FROM integers i1, integers i2 WHERE (SELECT SUM(i2.i +
i1.i)) > 0;
+
+SELECT i1.i, i2.i FROM integers i1, integers i2 HAVING (SELECT SUM(i2.i +
i1.i)) > 0; --error, cannot use non GROUP BY column 'i1.i' in query results
without an aggregate function
+
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
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
@@ -80,7 +80,11 @@ MAPI = (monetdb) /var/tmp/mtest-675878/
QUERY = SELECT i FROM integers WHERE (SELECT true, false UNION ALL SELECT
false, true); --error, subquery must return only one column
ERROR = !SELECT: subquery must return only one column
CODE = 42000
-MAPI = (monetdb) /var/tmp/mtest-6744/.s.monetdb.35025
+MAPI = (monetdb) /var/tmp/mtest-201361/.s.monetdb.35931
+QUERY = SELECT i1.i, i2.i FROM integers i1, integers i2 HAVING (SELECT
SUM(i2.i + i1.i)) > 0; --error, cannot use non GROUP BY column 'i1.i' in query
results without an aggregate function
+ERROR = !SELECT: cannot use non GROUP BY column 'i1.i' in query results
without an aggregate function
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-201361/.s.monetdb.35931
QUERY = UPDATE another_T SET col1 = MIN(col1); --error, aggregates not allowed
in update set clause
ERROR = !MIN: aggregate functions not allowed in SET clause (use subquery)
CODE = 42000
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
@@ -170,6 +170,49 @@ stdout of test 'subquery4` in directory
[ 1 ]
[ 1 ]
[ NULL ]
+#SELECT (SELECT SUM(i2.i + i1.i)) FROM integers i1, integers i2;
+% .%1 # table_name
+% %1 # name
+% bigint # type
+% 1 # length
+[ 2 ]
+[ 3 ]
+[ 4 ]
+[ NULL ]
+[ 3 ]
+[ 4 ]
+[ 5 ]
+[ NULL ]
+[ 4 ]
+[ 5 ]
+[ 6 ]
+[ NULL ]
+[ NULL ]
+[ NULL ]
+[ NULL ]
+[ NULL ]
+#SELECT (SELECT SUM(i2.i + i1.i)) FROM integers i1 INNER JOIN integers i2 ON
i1.i = i2.i;
+% .%3 # table_name
+% %3 # name
+% bigint # type
+% 1 # length
+[ 2 ]
+[ 4 ]
+[ 6 ]
+#SELECT i1.i, i2.i FROM integers i1, integers i2 WHERE (SELECT SUM(i2.i +
i1.i)) > 0;
+% sys.i1, sys.i2 # table_name
+% i, i # name
+% int, int # type
+% 1, 1 # length
+[ 1, 1 ]
+[ 1, 2 ]
+[ 1, 3 ]
+[ 2, 1 ]
+[ 2, 2 ]
+[ 2, 3 ]
+[ 3, 1 ]
+[ 3, 2 ]
+[ 3, 3 ]
#UPDATE another_T SET col4 = (SELECT SUM(col4 + ColID) FROM tbl_ProductSales);
--4 rows affected
[ 4 ]
#SELECT col4 FROM another_T;
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
