Changeset: b5fe1b901de5 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=b5fe1b901de5 Added Files: sql/test/subquery/Tests/all.test sql/test/subquery/Tests/any.test sql/test/subquery/Tests/any_all.test sql/test/subquery/Tests/correlated.test sql/test/subquery/Tests/exists.test sql/test/subquery/Tests/scalar.test sql/test/subquery/Tests/subquery.test sql/test/subquery/Tests/subquery2.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: mtest Log Message:
Converted sql/test/subquery. diffs (truncated from 5337 to 300 lines): diff --git a/sql/test/subquery/Tests/all.test b/sql/test/subquery/Tests/all.test new file mode 100644 --- /dev/null +++ b/sql/test/subquery/Tests/all.test @@ -0,0 +1,217 @@ +statement ok +CREATE TABLE integers(i INTEGER) + +statement ok +INSERT INTO integers VALUES (1), (2), (3), (NULL) + +query I rowsort +SELECT i FROM integers WHERE i >= ALL(SELECT i FROM integers) +---- + +query IT rowsort +SELECT i, i >= ALL(SELECT i FROM integers) FROM integers ORDER BY i +---- +1 +False +2 +False +3 +NULL +NULL +NULL + +query I rowsort +SELECT i FROM integers WHERE i >= ALL(SELECT i FROM integers WHERE i IS NOT NULL) +---- +3 + +query IT rowsort +SELECT i, i >= ALL(SELECT i FROM integers WHERE i IS NOT NULL) FROM integers ORDER BY i +---- +1 +False +2 +False +3 +True +NULL +NULL + +query I rowsort +SELECT i FROM integers WHERE i >= ALL(SELECT i FROM integers WHERE i IS NOT NULL) +---- +3 + +query I rowsort +SELECT i FROM integers WHERE i > ALL(SELECT MIN(i) FROM integers) +---- +2 +3 + +query I rowsort +SELECT i FROM integers WHERE i < ALL(SELECT MAX(i) FROM integers) +---- +1 +2 + +query I rowsort +SELECT i FROM integers WHERE i <= ALL(SELECT i FROM integers) +---- + +query I rowsort +SELECT i FROM integers WHERE i <= ALL(SELECT i FROM integers WHERE i IS NOT NULL) +---- +1 + +query I rowsort +SELECT i FROM integers WHERE i = ALL(SELECT i FROM integers WHERE i=1) +---- +1 + +query I rowsort +SELECT i FROM integers WHERE i <> ALL(SELECT i FROM integers WHERE i=1) +---- +2 +3 + +query I rowsort +SELECT i FROM integers WHERE i = ALL(SELECT i FROM integers WHERE i IS NOT NULL) +---- + +query I rowsort +SELECT i FROM integers WHERE i <> ALL(SELECT i FROM integers WHERE i IS NOT NULL) +---- + +query I rowsort +SELECT i FROM integers WHERE i <> ALL(SELECT i FROM integers WHERE i>10) ORDER BY i +---- +1 +2 +3 +NULL + +query IT rowsort +SELECT i, i <> ALL(SELECT i FROM integers WHERE i>10) FROM integers ORDER BY i +---- +1 +True +2 +True +3 +True +NULL +True + +query IT rowsort +SELECT i, i > ANY(SELECT i FROM integers WHERE i>10) FROM integers ORDER BY i +---- +1 +False +2 +False +3 +False +NULL +False + +query IT rowsort +SELECT i, i = ANY(SELECT i FROM integers WHERE i>10) FROM integers ORDER BY i +---- +1 +False +2 +False +3 +False +NULL +False + +query IT rowsort +SELECT i, i >= ANY(SELECT i FROM integers WHERE i>10) FROM integers ORDER BY i +---- +1 +False +2 +False +3 +False +NULL +False + +query IT rowsort +SELECT i, i <= ANY(SELECT i FROM integers WHERE i>10) FROM integers ORDER BY i +---- +1 +False +2 +False +3 +False +NULL +False + +query IT rowsort +SELECT i, i < ANY(SELECT i FROM integers WHERE i>10) FROM integers ORDER BY i +---- +1 +False +2 +False +3 +False +NULL +False + +query IT rowsort +SELECT i, i <> ANY(SELECT i FROM integers WHERE i>10) FROM integers ORDER BY i +---- +1 +False +2 +False +3 +False +NULL +False + +query TI rowsort +SELECT i >= ALL(SELECT i FROM integers WHERE i IS NOT NULL) AS k, CAST(SUM(i) AS BIGINT) FROM integers GROUP BY k ORDER BY k +---- +False +3 +NULL +NULL +True +3 + +query I rowsort +SELECT CAST(SUM(i) AS BIGINT) FROM integers GROUP BY (i >= ALL(SELECT i FROM integers WHERE i IS NOT NULL)) ORDER BY 1 +---- +3 +3 +NULL + +query TI rowsort +SELECT i >= ALL(SELECT MIN(i) FROM integers WHERE i IS NOT NULL) AS k, CAST(SUM(i) AS BIGINT) FROM integers GROUP BY k ORDER BY k +---- +NULL +NULL +True +6 + +query II rowsort +SELECT i, CAST(SUM(CASE WHEN (i >= ALL(SELECT i FROM integers WHERE i=2)) THEN 1 ELSE 0 END) AS BIGINT) FROM integers GROUP BY i ORDER BY i +---- +1 +0 +2 +1 +3 +1 +NULL +0 + +statement ok +DROP TABLE integers + + diff --git a/sql/test/subquery/Tests/any.test b/sql/test/subquery/Tests/any.test new file mode 100644 --- /dev/null +++ b/sql/test/subquery/Tests/any.test @@ -0,0 +1,168 @@ +statement ok +CREATE TABLE integers(i INTEGER) + +statement ok +INSERT INTO integers VALUES (1), (2), (3), (NULL) + +query I rowsort +SELECT i FROM integers WHERE i <= ANY(SELECT i FROM integers) +---- +1 +2 +3 + +query I rowsort +SELECT i FROM integers WHERE i > ANY(SELECT i FROM integers) +---- +2 +3 + +query IT rowsort +SELECT i, i > ANY(SELECT i FROM integers) FROM integers ORDER BY i +---- +1 +NULL +2 +True +3 +True +NULL +NULL + +query IT rowsort +SELECT i, i > ANY(SELECT i FROM integers WHERE i IS NOT NULL) FROM integers ORDER BY i +---- +1 +False +2 +True +3 +True +NULL +NULL + +query IT rowsort +SELECT i, NULL > ANY(SELECT i FROM integers) FROM integers ORDER BY i +---- +1 +NULL +2 +NULL +3 +NULL +NULL +NULL + +query IT rowsort +SELECT i, NULL > ANY(SELECT i FROM integers WHERE i IS NOT NULL) FROM integers ORDER BY i +---- +1 +NULL +2 +NULL +3 +NULL +NULL +NULL + +query I rowsort +SELECT i FROM integers WHERE i = ANY(SELECT i FROM integers) +---- +1 +2 +3 _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list
