Changeset: 3e747dc6b715 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=3e747dc6b715
Modified Files:
sql/test/subquery/Tests/any_all.sql
sql/test/subquery/Tests/correlated.sql
sql/test/subquery/Tests/exists.sql
Branch: sq2default
Log Message:
enable all tests..
diffs (truncated from 456 to 300 lines):
diff --git a/sql/test/subquery/Tests/any_all.sql
b/sql/test/subquery/Tests/any_all.sql
--- a/sql/test/subquery/Tests/any_all.sql
+++ b/sql/test/subquery/Tests/any_all.sql
@@ -44,27 +44,24 @@ SELECT i=ANY(SELECT i FROM integers WHER
--True
--False
-/*Wrong results
-SELECT i>ALL(SELECT (i+i1.i-1)/2 FROM integers WHERE i IS NOT NULL) FROM
integers i1 ORDER BY i;*/
+SELECT i>ALL(SELECT (i+i1.i-1)/2 FROM integers WHERE i IS NOT NULL) FROM
integers i1 ORDER BY i;
+--NULL
--False
--False
--True
---NULL
-/*Wrong results
-SELECT i=ALL(SELECT i FROM integers WHERE i<>i1.i) FROM integers i1 ORDER BY
i;*/
+SELECT i=ALL(SELECT i FROM integers WHERE i<>i1.i) FROM integers i1 ORDER BY i;
+--True
--False
--False
--False
---True
SELECT i FROM integers i1 WHERE i=ANY(SELECT i FROM integers WHERE i=i1.i)
ORDER BY i;
--1
--2
--3
-/*BROKEN
-SELECT i FROM integers i1 WHERE i<>ANY(SELECT i FROM integers WHERE i=i1.i)
ORDER BY i;*/
+SELECT i FROM integers i1 WHERE i<>ANY(SELECT i FROM integers WHERE i=i1.i)
ORDER BY i;
-- (Empty result set)
SELECT i FROM integers i1 WHERE i=ANY(SELECT i FROM integers WHERE i<>i1.i)
ORDER BY i;
@@ -113,19 +110,17 @@ SELECT i>ANY(SELECT i FROM integers WHER
--True
--False
-/*Wrong results
-SELECT i>ALL(SELECT (i+i1.i-1)/2 FROM integers) FROM integers i1 ORDER BY i;*/
+SELECT i>ALL(SELECT (i+i1.i-1)/2 FROM integers) FROM integers i1 ORDER BY i;
+--NULL
--False
--False
--NULL
+
+SELECT i>ALL(SELECT (i+i1.i-1)/2 FROM integers WHERE i IS NOT NULL) FROM
integers i1 ORDER BY i;
--NULL
-
-/*Wrong results
-SELECT i>ALL(SELECT (i+i1.i-1)/2 FROM integers WHERE i IS NOT NULL) FROM
integers i1 ORDER BY i;*/
--False
--False
--True
---NULL
SELECT i=ANY(SELECT i FROM integers WHERE i=i1.i OR i IS NULL) FROM integers
i1 ORDER BY i;
--True
@@ -133,8 +128,7 @@ SELECT i=ANY(SELECT i FROM integers WHER
--True
--NULL
-/*Wrong results
-SELECT i=ALL(SELECT i FROM integers WHERE i=i1.i OR i IS NULL) FROM integers
i1 ORDER BY i;*/
+SELECT i=ALL(SELECT i FROM integers WHERE i=i1.i OR i IS NULL) FROM integers
i1 ORDER BY i;
--NULL
--NULL
--NULL
@@ -146,8 +140,7 @@ SELECT MIN(i)>ANY(SELECT i FROM integers
SELECT SUM(i)>ANY(SELECT i FROM integers WHERE i>MIN(i1.i)) FROM integers i1;
--True
-/*BROKEN
-SELECT (SELECT SUM(i)+SUM(i1.i) FROM integers)>ANY(SELECT i FROM integers
WHERE i>MIN(i1.i)) FROM integers i1;*/
+SELECT (SELECT SUM(i)+SUM(i1.i) FROM integers)>ANY(SELECT i FROM integers
WHERE i>MIN(i1.i)) FROM integers i1;
--True
SELECT i=ANY(SELECT i FROM integers WHERE i=i1.i AND i>10) FROM integers i1
ORDER BY i;
diff --git a/sql/test/subquery/Tests/correlated.sql
b/sql/test/subquery/Tests/correlated.sql
--- a/sql/test/subquery/Tests/correlated.sql
+++ b/sql/test/subquery/Tests/correlated.sql
@@ -71,7 +71,7 @@ SELECT i, (SELECT 42 WHERE i1.i>2) AS j
-- 3, 42
-- correlated filter with matching entry on NULL
SELECT i, (SELECT 42 WHERE i1.i IS NULL) AS j FROM integers i1 ORDER BY i;
- -- NULL, 42 -- current bug in left outerjoin with only a
select!
+ -- NULL, 42
-- 1, NULL
-- 2, NULL
-- 3, NULL
@@ -196,8 +196,7 @@ SELECT CAST((SELECT i+SUM(i1.i) FROM int
SELECT CAST((SELECT SUM(i)+SUM(i1.i) FROM integers) AS BIGINT) FROM integers
i1 ORDER BY 1;
--12
-/*Wrong results
-SELECT CAST((SELECT SUM(i)+SUM((CASE WHEN i IS NOT NULL THEN i*0 ELSE 0
END)+i1.i) FROM integers) AS BIGINT) FROM integers i1 ORDER BY 1;*/
+SELECT CAST((SELECT SUM(i)+SUM((CASE WHEN i IS NOT NULL THEN i*0 ELSE 0
END)+i1.i) FROM integers) AS BIGINT) FROM integers i1 ORDER BY 1;
--10
--14
--18
@@ -220,8 +219,7 @@ SELECT i1.i AS j, CAST((SELECT SUM(j+i)
--3 15
--NULL NULL
-/*BROKEN
-SELECT CAST((SELECT SUM(i1.i*i) FROM integers) AS BIGINT) FROM integers i1
ORDER BY i;*/
+SELECT CAST((SELECT SUM(i1.i*i) FROM integers) AS BIGINT) FROM integers i1
ORDER BY i;
--6
--12
--18
@@ -245,8 +243,7 @@ SELECT i AS j, CAST((SELECT j*SUM(i) FRO
--3 18
--NULL NULL
-/*Wrong result, cannot find column
-SELECT i AS j, CAST((SELECT i1.i*SUM(i) FROM integers) AS BIGINT) AS k FROM
integers i1 GROUP BY j ORDER BY j;*/
+SELECT i AS j, CAST((SELECT i1.i*SUM(i) FROM integers) AS BIGINT) AS k FROM
integers i1 GROUP BY j ORDER BY j;
--1 6
--2 12
--3 18
@@ -258,8 +255,7 @@ SELECT i, CAST(SUM((SELECT SUM(i)*i1.i F
--3 18
--NULL NULL
-/*Wrong results, aggregation functions cannot be nested
-SELECT i, SUM((SELECT SUM(i)*SUM(i1.i) FROM integers)) AS k FROM integers i1
GROUP BY i ORDER BY i; --error*/
+SELECT i, SUM((SELECT SUM(i)*SUM(i1.i) FROM integers)) AS k FROM integers i1
GROUP BY i ORDER BY i; --error
SELECT CAST((SELECT SUM(i1.i)) AS BIGINT) FROM integers i1;
--6
@@ -306,18 +302,17 @@ SELECT i, NOT((SELECT MIN(i) FROM intege
--3 True
--NULL NULL
-/* Wrong results
-SELECT i, (SELECT i FROM integers i2 WHERE i=(SELECT SUM(i) FROM integers i2
WHERE i2.i>i1.i)) FROM integers i1 ORDER BY 1;*/
+SELECT i, (SELECT i FROM integers i2 WHERE i=(SELECT SUM(i) FROM integers i2
WHERE i2.i>i1.i)) FROM integers i1 ORDER BY 1;
--1 NULL
--2 3
--3 NULL
--NULL NULL
SELECT i, CAST((SELECT SUM(i) IS NULL FROM integers i2 WHERE i2.i>i1.i) AS
BIGINT) FROM integers i1 ORDER BY i;
---1 False
---2 False
---3 True
---NULL True
+--1 0
+--2 0
+--3 1
+--NULL 1
SELECT i, (SELECT COUNT(*) FROM integers i2 WHERE i2.i>i1.i) FROM integers i1
ORDER BY i;
--1 2
@@ -343,20 +338,17 @@ SELECT i, (SELECT COUNT(*) FROM integers
--3 0
--NULL 1
-/*Wrong results
-SELECT i FROM integers i1 WHERE (SELECT COUNT(*) FROM integers i2 WHERE
i2.i>i1.i)=0 ORDER BY i;*/
+SELECT i FROM integers i1 WHERE (SELECT COUNT(*) FROM integers i2 WHERE
i2.i>i1.i)=0 ORDER BY i;
--3
--NULL
-/*Wrong results
-SELECT i, (SELECT i FROM integers i2 WHERE i-2=(SELECT COUNT(*) FROM integers
i2 WHERE i2.i>i1.i)) FROM integers i1 ORDER BY 1;*/
+SELECT i, (SELECT i FROM integers i2 WHERE i-2=(SELECT COUNT(*) FROM integers
i2 WHERE i2.i>i1.i)) FROM integers i1 ORDER BY 1;
--1 NULL
--2 3
--3 2
--NULL 2
-/*Wrong results
-SELECT i, (SELECT COUNT(*) FROM integers i2 WHERE i2.i>i1.i GROUP BY i1.i)
FROM integers i1 ORDER BY i;*/
+SELECT i, (SELECT COUNT(*) FROM integers i2 WHERE i2.i>i1.i GROUP BY i1.i)
FROM integers i1 ORDER BY i;
--1 2
--2 1
--3 NULL
@@ -409,27 +401,25 @@ SELECT i, (SELECT s1.i FROM (SELECT * FR
--3 3
--NULL NULL
-/*Wrong results
-SELECT i, (SELECT s1.i FROM (SELECT i FROM integers WHERE i=i1.i) s1 INNER
JOIN (SELECT i FROM integers WHERE i=4-i1.i) s2 ON s1.i>s2.i) AS j FROM
integers i1 ORDER BY i;*/
+SELECT i, (SELECT s1.i FROM (SELECT i FROM integers WHERE i=i1.i) s1 INNER
JOIN (SELECT i FROM integers WHERE i=4-i1.i) s2 ON s1.i>s2.i) AS j FROM
integers i1 ORDER BY i;
--1 NULL
--2 NULL
--3 3
--NULL NULL
-/*Mitosis error I think (it runs fine without it)
-SELECT i, (SELECT s1.i FROM integers s1, integers s2 WHERE s1.i=s2.i AND
s1.i=4-i1.i) AS j FROM integers i1 ORDER BY i;*/
+SELECT i, (SELECT s1.i FROM integers s1, integers s2 WHERE s1.i=s2.i AND
s1.i=4-i1.i) AS j FROM integers i1 ORDER BY i;
+--NULL, NULL
--1 3
--2 2
--3 1
-/*Mitosis error I think (it runs fine without it)
-SELECT i, (SELECT s1.i FROM integers s1 INNER JOIN integers s2 ON s1.i=s2.i
AND s1.i=4-i1.i) AS j FROM integers i1 ORDER BY i;*/
+SELECT i, (SELECT s1.i FROM integers s1 INNER JOIN integers s2 ON s1.i=s2.i
AND s1.i=4-i1.i) AS j FROM integers i1 ORDER BY i;
+--NULL, NULL
--1 3
--2 2
--3 1
-/*Extra projection
-SELECT * FROM integers s1 INNER JOIN integers s2 ON (SELECT 2*SUM(i)*s1.i FROM
integers)=(SELECT SUM(i)*s2.i FROM integers) ORDER BY s1.i;*/
+SELECT * FROM integers s1 INNER JOIN integers s2 ON (SELECT 2*SUM(i)*s1.i FROM
integers)=(SELECT SUM(i)*s2.i FROM integers) ORDER BY s1.i;
--1 2
SELECT * FROM integers s1 INNER JOIN integers s2 ON (SELECT s1.i=s2.i) ORDER
BY s1.i;
@@ -437,14 +427,12 @@ SELECT * FROM integers s1 INNER JOIN int
--2 2
--3 3
-/*Extra projection
-SELECT * FROM integers s1 INNER JOIN integers s2 ON (SELECT s1.i=i FROM
integers WHERE s2.i=i) ORDER BY s1.i;*/
+SELECT * FROM integers s1 INNER JOIN integers s2 ON (SELECT s1.i=i FROM
integers WHERE s2.i=i) ORDER BY s1.i;
--1 1
--2 2
--3 3
-/*Wrong results
-SELECT * FROM integers s1 LEFT OUTER JOIN integers s2 ON (SELECT 2*SUM(i)*s1.i
FROM integers)=(SELECT SUM(i)*s2.i FROM integers) ORDER BY s1.i;*/
+SELECT * FROM integers s1 LEFT OUTER JOIN integers s2 ON (SELECT 2*SUM(i)*s1.i
FROM integers)=(SELECT SUM(i)*s2.i FROM integers) ORDER BY s1.i;
--1 2
--2 NULL
--3 NULL
@@ -462,30 +450,31 @@ SELECT i, CAST((SELECT SUM(s1.i) FROM in
--3 12
--NULL 6
---SELECT i, (SELECT row_number() OVER (ORDER BY i)) FROM integers i1 ORDER BY
i; --Should we support correlated expressions inside PARTITION BY and ORDER BY
on Window functions?
+--
+SELECT i, (SELECT row_number() OVER (ORDER BY i)) FROM integers i1 ORDER BY i;
--Should we support correlated expressions inside PARTITION BY and ORDER BY on
Window functions?
+--1 1
+--2 1
+--3 1
+--NULL 1
-/* Extra projection
-SELECT i, (SELECT i FROM integers WHERE i=i1.i UNION SELECT i FROM integers
WHERE i=i1.i) AS j FROM integers i1 ORDER BY i;*/
+SELECT i, (SELECT i FROM integers WHERE i=i1.i UNION SELECT i FROM integers
WHERE i=i1.i) AS j FROM integers i1 ORDER BY i;
--1 1
--2 2
--3 3
--NULL NULL
-/* Extra projection
-SELECT i, (SELECT i FROM integers WHERE i IS NOT NULL EXCEPT SELECT i FROM
integers WHERE i<>i1.i) AS j FROM integers i1 WHERE i IS NOT NULL ORDER BY i;*/
+SELECT i, (SELECT i FROM integers WHERE i IS NOT NULL EXCEPT SELECT i FROM
integers WHERE i<>i1.i) AS j FROM integers i1 WHERE i IS NOT NULL ORDER BY i;
--1 1
--2 2
--3 3
-/* Extra projection
-SELECT i, (SELECT i FROM integers WHERE i=i1.i INTERSECT SELECT i FROM
integers WHERE i=i1.i) AS j FROM integers i1 ORDER BY i;*/
+SELECT i, (SELECT i FROM integers WHERE i=i1.i INTERSECT SELECT i FROM
integers WHERE i=i1.i) AS j FROM integers i1 ORDER BY i;
--1 1
--2 2
--3 3
--NULL NULL
-/* Extra projection
-SELECT i, (SELECT i FROM integers WHERE i=i1.i UNION SELECT i FROM integers
WHERE i<>i1.i EXCEPT SELECT i FROM integers WHERE i<>i1.i) AS j FROM integers
i1 ORDER BY i;*/
+SELECT i, (SELECT i FROM integers WHERE i=i1.i UNION SELECT i FROM integers
WHERE i<>i1.i EXCEPT SELECT i FROM integers WHERE i<>i1.i) AS j FROM integers
i1 ORDER BY i;
--1 1
--2 2
--3 3
@@ -498,13 +487,12 @@ SELECT i, CAST((SELECT (SELECT SUM(i) FR
--NULL NULL
SELECT i, (SELECT row_number() OVER (ORDER BY i) FROM integers WHERE i1.i=i)
FROM integers i1 ORDER BY i;
+--NULL NULL
--1 1
--2 1
--3 1
---NULL NULL
-/*Wrong results
-SELECT i1.i, (SELECT rank() OVER (ORDER BY i) FROM integers WHERE i1.i=i) FROM
integers i1, integers i2 ORDER BY i1.i;*/
+SELECT i1.i, (SELECT rank() OVER (ORDER BY i) FROM integers WHERE i1.i=i) FROM
integers i1, integers i2 ORDER BY i1.i;
--NULL, NULL
--NULL, NULL
--NULL, NULL
@@ -522,8 +510,7 @@ SELECT i1.i, (SELECT rank() OVER (ORDER
--3, 1
--3, 1
-/*Wrong results
-SELECT i1.i, (SELECT row_number() OVER (ORDER BY i) FROM integers WHERE
i1.i=i) FROM integers i1, integers i2 ORDER BY i1.i;*/
+SELECT i1.i, (SELECT row_number() OVER (ORDER BY i) FROM integers WHERE
i1.i=i) FROM integers i1, integers i2 ORDER BY i1.i;
--1 1
--1 1
--1 1
@@ -541,8 +528,7 @@ SELECT i1.i, (SELECT row_number() OVER (
--NULL NULL
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
