Changeset: b53a91b48df3 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=b53a91b48df3
Modified Files:
sql/test/subquery/Tests/correlated.sql
sql/test/subquery/Tests/correlated.stable.out
Branch: sq2
Log Message:
more correlated tests
diffs (163 lines):
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
@@ -64,7 +64,6 @@ SELECT i, i=ANY(SELECT i FROM integers W
-- subquery with OFFSET is not supported
SELECT i, (SELECT i+i1.i FROM integers LIMIT 1 OFFSET 1) AS j FROM integers i1
ORDER BY i; -- errror
-- correlated filter without FROM clause
-/* Wrong error */
SELECT i, (SELECT 42 WHERE i1.i>2) AS j FROM integers i1 ORDER BY i;
-- NULL, NULL
-- 1, NULL
@@ -244,9 +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;
- */
--1 6
--2 12
--3 18
@@ -357,9 +354,7 @@ SELECT i, (SELECT i FROM integers i2 WHE
--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;
- */
--1 2
--2 1
--3 NULL
@@ -430,9 +425,7 @@ SELECT i, (SELECT s1.i FROM integers s1
--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;
- */
--1 2
SELECT * FROM integers s1 INNER JOIN integers s2 ON (SELECT s1.i=s2.i) ORDER
BY s1.i;
@@ -440,9 +433,7 @@ 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;
- */
--1 1
--2 2
--3 3
@@ -471,6 +462,10 @@ SELECT i, CAST((SELECT SUM(s1.i) FROM in
/*Wrong results
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
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
diff --git a/sql/test/subquery/Tests/correlated.stable.out
b/sql/test/subquery/Tests/correlated.stable.out
--- a/sql/test/subquery/Tests/correlated.stable.out
+++ b/sql/test/subquery/Tests/correlated.stable.out
@@ -248,7 +248,7 @@ stdout of test 'correlated` in directory
[ 2, 5 ]
[ 3, NULL ]
#SELECT i, (SELECT MIN(i) FROM integers GROUP BY i1.i) AS j FROM integers i1
ORDER BY i;
-% .i1, .L1 # table_name
+% .i1, .L2 # table_name
% i, j # name
% int, int # type
% 1, 1 # length
@@ -257,7 +257,7 @@ stdout of test 'correlated` in directory
[ 2, 1 ]
[ 3, 1 ]
#SELECT i, (SELECT i FROM integers GROUP BY i HAVING i=i1.i) AS j FROM
integers i1 ORDER BY i;
-% .i1, .integers # table_name
+% .i1, .L1 # table_name
% i, j # name
% int, int # type
% 1, 1 # length
@@ -352,6 +352,15 @@ stdout of test 'correlated` in directory
[ 1, 6 ]
[ 2, 12 ]
[ 3, 18 ]
+#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;
+% .i1, . # table_name
+% j, k # name
+% int, bigint # type
+% 1, 2 # length
+[ NULL, NULL ]
+[ 1, 6 ]
+[ 2, 12 ]
+[ 3, 18 ]
#SELECT i, CAST(SUM((SELECT SUM(i)*i1.i FROM integers)) AS BIGINT) AS k FROM
integers i1 GROUP BY i ORDER BY i;
% .i1, . # table_name
% i, k # name
@@ -368,8 +377,8 @@ stdout of test 'correlated` in directory
% 1 # length
[ 6 ]
#SELECT i AS j, (SELECT MIN(i1.i) FROM integers GROUP BY i HAVING i=j) FROM
integers i1 GROUP BY j ORDER BY j;
-% .i1, .L3 # table_name
-% j, L3 # name
+% .i1, .L2 # table_name
+% j, L2 # name
% int, int # type
% 1, 1 # length
[ NULL, NULL ]
@@ -478,6 +487,15 @@ stdout of test 'correlated` in directory
[ 1, 2 ]
[ 2, 1 ]
[ 3, 0 ]
+#SELECT i, (SELECT COUNT(*) FROM integers i2 WHERE i2.i>i1.i GROUP BY i1.i)
FROM integers i1 ORDER BY i;
+% .i1, .L2 # table_name
+% i, L2 # name
+% int, bigint # type
+% 1, 1 # length
+[ NULL, NULL ]
+[ 1, 2 ]
+[ 2, 1 ]
+[ 3, NULL ]
#SELECT i, (SELECT CASE WHEN (SELECT COUNT(*) FROM integers i2 WHERE
i2.i>i1.i)=0 THEN 1 ELSE 0 END) FROM integers i1 ORDER BY i;
% .i1, .L3 # table_name
% i, L3 # name
@@ -579,6 +597,12 @@ stdout of test 'correlated` in directory
[ 1, 3 ]
[ 2, 2 ]
[ 3, 1 ]
+#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;
+% sys.s1, sys.s2 # table_name
+% i, i # name
+% int, int # type
+% 1, 1 # length
+[ 1, 2 ]
#SELECT * FROM integers s1 INNER JOIN integers s2 ON (SELECT s1.i=s2.i) ORDER
BY s1.i;
% sys.s1, sys.s2 # table_name
% i, i # name
@@ -587,6 +611,14 @@ stdout of test 'correlated` in directory
[ 1, 1 ]
[ 2, 2 ]
[ 3, 3 ]
+#SELECT * FROM integers s1 INNER JOIN integers s2 ON (SELECT s1.i=i FROM
integers WHERE s2.i=i) ORDER BY s1.i;
+% sys.s1, sys.s2 # table_name
+% i, i # name
+% int, int # type
+% 1, 1 # length
+[ 1, 1 ]
+[ 2, 2 ]
+[ 3, 3 ]
#SELECT i, CAST((SELECT SUM(s1.i) FROM integers s1 LEFT OUTER JOIN integers s2
ON s1.i=s2.i OR s1.i=i1.i-1) AS BIGINT) AS j FROM integers i1 ORDER BY i;
% .i1, . # table_name
% i, j # name
@@ -863,7 +895,7 @@ stdout of test 'correlated` in directory
[ 2, 6 ]
[ 3, 6 ]
#SELECT i, (SELECT i=ANY(SELECT i FROM integers WHERE i=s1.i) FROM integers s1
WHERE i=i1.i) AS j FROM integers i1 ORDER BY i;
-% .i1, .L6 # table_name
+% .i1, .L7 # table_name
% i, j # name
% int, boolean # type
% 1, 5 # length
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
