Changeset: 2ee5ae64db78 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=2ee5ae64db78 Added Files: sql/test/subquery/Tests/all.sql sql/test/subquery/Tests/any.sql sql/test/subquery/Tests/any_all.sql sql/test/subquery/Tests/scalar.sql Branch: default Log Message:
add missing files diffs (243 lines): diff --git a/sql/test/subquery/Tests/all.sql b/sql/test/subquery/Tests/all.sql new file mode 100644 --- /dev/null +++ b/sql/test/subquery/Tests/all.sql @@ -0,0 +1,79 @@ +CREATE TABLE integers(i INTEGER); +INSERT INTO integers VALUES (1), (2), (3), (NULL); + +SELECT i FROM integers WHERE i >= ALL(SELECT i FROM integers); -- empty +SELECT i, i >= ALL(SELECT i FROM integers) FROM integers ORDER BY i; + -- NULL, NULL + -- 1, false + -- 2, false + -- 3, NULL +SELECT i FROM integers WHERE i >= ALL(SELECT i FROM integers WHERE i IS NOT NULL); -- 3 +SELECT i, i >= ALL(SELECT i FROM integers WHERE i IS NOT NULL) FROM integers ORDER BY i; + -- NULL, NULL + -- 1, false + -- 2, false + -- 3, true +SELECT i FROM integers WHERE i >= ALL(SELECT i FROM integers WHERE i IS NOT NULL); -- 3 +SELECT i FROM integers WHERE i > ALL(SELECT MIN(i) FROM integers); -- 2, 3 +SELECT i FROM integers WHERE i < ALL(SELECT MAX(i) FROM integers); -- 1, 2 +SELECT i FROM integers WHERE i <= ALL(SELECT i FROM integers); -- empty +SELECT i FROM integers WHERE i <= ALL(SELECT i FROM integers WHERE i IS NOT NULL); -- 1 +SELECT i FROM integers WHERE i = ALL(SELECT i FROM integers WHERE i=1); -- 1 +SELECT i FROM integers WHERE i <> ALL(SELECT i FROM integers WHERE i=1); -- 2, 3 +SELECT i FROM integers WHERE i = ALL(SELECT i FROM integers WHERE i IS NOT NULL); -- empty +SELECT i FROM integers WHERE i <> ALL(SELECT i FROM integers WHERE i IS NOT NULL); -- empty +-- zero results always results in TRUE for ALL, even if "i" is NULL +SELECT i FROM integers WHERE i <> ALL(SELECT i FROM integers WHERE i>10) ORDER BY i; -- null, 1, 2, 3 +SELECT i, i <> ALL(SELECT i FROM integers WHERE i>10) FROM integers ORDER BY i; + -- null, true + -- 1, true + -- 2, true + -- 3, true +-- zero results always results in FALSE for ANY +SELECT i, i > ANY(SELECT i FROM integers WHERE i>10) FROM integers ORDER BY i; + -- null, false + -- 1, false + -- 2, false + -- 3, false +SELECT i, i = ANY(SELECT i FROM integers WHERE i>10) FROM integers ORDER BY i; + -- null, false + -- 1, false + -- 2, false + -- 3, false +SELECT i, i >= ANY(SELECT i FROM integers WHERE i>10) FROM integers ORDER BY i; + -- null, false + -- 1, false + -- 2, false + -- 3, false +SELECT i, i <= ANY(SELECT i FROM integers WHERE i>10) FROM integers ORDER BY i; + -- null, false + -- 1, false + -- 2, false + -- 3, false +SELECT i, i < ANY(SELECT i FROM integers WHERE i>10) FROM integers ORDER BY i; + -- null, false + -- 1, false + -- 2, false + -- 3, false +SELECT i, i <> ANY(SELECT i FROM integers WHERE i>10) FROM integers ORDER BY i; + -- null, false + -- 1, false + -- 2, false + -- 3, false + +-- subqueries in GROUP BY clause +SELECT i >= ALL(SELECT i FROM integers WHERE i IS NOT NULL) AS k, SUM(i) FROM integers GROUP BY k ORDER BY k; + -- null, null + -- false, 3 + -- true, 3 +SELECT SUM(i) FROM integers GROUP BY (i >= ALL(SELECT i FROM integers WHERE i IS NOT NULL)) ORDER BY 1; -- NULL, 3, 3 +SELECT i >= ALL(SELECT MIN(i) FROM integers WHERE i IS NOT NULL) AS k, SUM(i) FROM integers GROUP BY k ORDER BY k; + -- NULL, NULL + -- true, 6 +SELECT i, SUM(CASE WHEN (i >= ALL(SELECT i FROM integers WHERE i=2)) THEN 1 ELSE 0 END) FROM integers GROUP BY i ORDER BY i; + -- null, 0 + -- 1, 0 + -- 2, 1 + -- 3, 1 + +DROP TABLE integers; diff --git a/sql/test/subquery/Tests/any.sql b/sql/test/subquery/Tests/any.sql new file mode 100644 --- /dev/null +++ b/sql/test/subquery/Tests/any.sql @@ -0,0 +1,69 @@ +CREATE TABLE integers(i INTEGER); +INSERT INTO integers VALUES (1), (2), (3), (NULL); + +SELECT i FROM integers WHERE i <= ANY(SELECT i FROM integers); -- single column 1,2,3 +SELECT i FROM integers WHERE i > ANY(SELECT i FROM integers); -- single column 2,3 + +SELECT i, i > ANY(SELECT i FROM integers) FROM integers ORDER BY i; + -- NULL, NULL + -- 1, NULL + -- 2, true + -- 3, true +SELECT i, i > ANY(SELECT i FROM integers WHERE i IS NOT NULL) FROM integers ORDER BY i; + -- NULL, NULL + -- 1, false + -- 2, true + -- 3, true +SELECT i, NULL > ANY(SELECT i FROM integers) FROM integers ORDER BY i; + -- NULL, NULL + -- 1, NULL + -- 2, NULL + -- 3, NULL +SELECT i, NULL > ANY(SELECT i FROM integers WHERE i IS NOT NULL) FROM integers ORDER BY i; + -- NULL, NULL + -- 1, NULL + -- 2, NULL + -- 3, NULL +SELECT i FROM integers WHERE i = ANY(SELECT i FROM integers); -- single column 1, 2, 3 +SELECT i, i = ANY(SELECT i FROM integers WHERE i>2) FROM integers ORDER BY i; + -- NULL, NULL + -- 1, false + -- 2, false + -- 3, true +SELECT i, i = ANY(SELECT i FROM integers WHERE i>2 OR i IS NULL) FROM integers ORDER BY i; + -- NULL, NULL + -- 1, NULL + -- 2, NULL + -- 3, true +SELECT i, i <> ANY(SELECT i FROM integers WHERE i>2) FROM integers ORDER BY i; + -- NULL, NULL + -- 1, true + -- 2, true + -- 3, false +SELECT i, i <> ANY(SELECT i FROM integers WHERE i>2 OR i IS NULL) FROM integers ORDER BY i; + -- NULL, NULL + -- 1, true + -- 2, true + -- 3, NULL +SELECT i, i = ANY(SELECT i1.i FROM integers i1, integers i2, integers i3, integers i4, integers i5, integers i6 WHERE i1.i IS NOT NULL) FROM integers ORDER BY i; + -- NULL, NULL + -- 1, true + -- 2, true + -- 3, true +SELECT i, i = ANY(SELECT i1.i FROM integers i1, integers i2, integers i3, integers i4, integers i5, integers i6 WHERE i1.i IS NOT NULL AND i1.i <> 2) FROM integers ORDER BY i; + -- NULL, NULL + -- 1, true + -- 2, false + -- 3, true +SELECT i, i >= ANY(SELECT i1.i FROM integers i1, integers i2, integers i3, integers i4, integers i5, integers i6 WHERE i1.i IS NOT NULL) FROM integers ORDER BY i; + -- NULL, NULL + -- 1, true + -- 2, true + -- 3, true +SELECT i, i >= ANY(SELECT i1.i FROM integers i1, integers i2, integers i3, integers i4, integers i5, integers i6 WHERE i1.i IS NOT NULL AND i1.i <> 1 LIMIT 1) FROM integers ORDER BY i; -- parse error + -- NULL, NULL + -- 1, false + -- 2, true + -- 3, true + +drop table integers; diff --git a/sql/test/subquery/Tests/any_all.sql b/sql/test/subquery/Tests/any_all.sql new file mode 100644 --- /dev/null +++ b/sql/test/subquery/Tests/any_all.sql @@ -0,0 +1,41 @@ + +SELECT 1 = ANY(SELECT 1); -- true +SELECT 1 = ANY(SELECT NULL); -- NULL +SELECT 1 = ANY(SELECT 2); -- false +SELECT NULL = ANY(SELECT 2); -- NULL + +SELECT 1 = ALL(SELECT 1); -- true +SELECT 1 = ALL(SELECT NULL); -- NULL +SELECT 1 = ALL(SELECT 2); -- false +SELECT NULL = ALL(SELECT 2); -- NULL + +CREATE TABLE integers(i INTEGER); +INSERT INTO integers VALUES (1), (2), (3); + +-- ANY is like EXISTS without NULL values +SELECT 2 > ANY(SELECT * FROM integers); -- true +SELECT 1 > ANY(SELECT * FROM integers); -- false + +SELECT 4 > ALL(SELECT * FROM integers); -- true +SELECT 1 > ALL(SELECT * FROM integers); -- false + +-- NULL input always results in NULL output +SELECT NULL > ANY(SELECT * FROM integers); -- NULL +SELECT NULL > ALL(SELECT * FROM integers); -- NULL + +-- now with a NULL value in the input +INSERT INTO integers VALUES (NULL); + +-- ANY returns either true or NULL +SELECT 2 > ANY(SELECT * FROM integers); -- true +SELECT 1 > ANY(SELECT * FROM integers); -- NULL + +-- ALL returns either NULL or false +SELECT 4 > ALL(SELECT * FROM integers); -- NULL +SELECT 1 > ALL(SELECT * FROM integers); -- false + +-- NULL input always results in NULL +SELECT NULL > ANY(SELECT * FROM integers); -- NULL +SELECT NULL > ALL(SELECT * FROM integers); -- NULL + +DROP TABLE integers; diff --git a/sql/test/subquery/Tests/scalar.sql b/sql/test/subquery/Tests/scalar.sql new file mode 100644 --- /dev/null +++ b/sql/test/subquery/Tests/scalar.sql @@ -0,0 +1,34 @@ +SELECT 1+(SELECT 1); -- 2 +SELECT 1=(SELECT 1); -- true +SELECT 1<>(SELECT 1); -- false +SELECT 1=(SELECT NULL); -- NULL +SELECT NULL=(SELECT 1); -- NULL + +SELECT EXISTS(SELECT 1); -- true + +CREATE TABLE integers(i INTEGER); +INSERT INTO integers VALUES (1), (2), (3), (NULL); + +SELECT EXISTS(SELECT 1) FROM integers; -- true, true, true, true +SELECT EXISTS(SELECT * FROM integers); -- true +SELECT EXISTS(SELECT * FROM integers WHERE i IS NULL); -- true +DROP TABLE integers; + +SELECT 1 IN (SELECT 1); -- true +SELECT NULL IN (SELECT 1); -- NULL +SELECT 1 IN (SELECT NULL); -- NULL +SELECT 1 IN (SELECT 2); -- false + +CREATE TABLE integers(i INTEGER); +INSERT INTO integers VALUES (1), (2), (3); + +SELECT 4 IN (SELECT * FROM integers); -- false +SELECT 1 IN (SELECT * FROM integers); -- true +SELECT 1 IN (SELECT * FROM integers) FROM integers; -- true, true, true +INSERT INTO integers VALUES (NULL); +SELECT 4 IN (SELECT * FROM integers); -- NULL +SELECT 1 IN (SELECT * FROM integers); -- true +SELECT * FROM integers WHERE (4 IN (SELECT * FROM integers)) IS NULL ORDER BY 1; -- NULL, 1, 2, 3 +SELECT * FROM integers WHERE (i IN (SELECT * FROM integers)) IS NULL ORDER BY 1; -- NULL + +DROP TABLE integers; _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list
