Changeset: 944ebb346fe0 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=944ebb346fe0
Modified Files:
sql/test/subquery/Tests/correlated.sql
sql/test/subquery/Tests/correlated.stable.err
sql/test/subquery/Tests/correlated.stable.out
Branch: Nov2019
Log Message:
More subqueries tests
diffs (truncated from 819 to 300 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
@@ -165,145 +165,144 @@ SELECT i, (SELECT MIN(i)+i1.i FROM integ
-- 2, 5
-- 3, NULL
-SELECT (SELECT SUM(i + i1.i), 1 FROM integers) FROM integers i1; --error, the
subquery should output only one column
+SELECT (SELECT SUM(i + i1.i), 1 FROM integers) FROM integers i1; --error, the
subquery must output only one column
SELECT (SELECT SUM(i1.i) FROM integers) AS k FROM integers i1 GROUP BY i ORDER
BY i; --cardinality violation, scalar expression expected
-/*
--- exit
- // aggregate with correlation in GROUP BY
- result = con.Query("SELECT i, (SELECT MIN(i) FROM integers GROUP BY
i1.i) AS j FROM integers i1 ORDER BY i;");
- REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
- REQUIRE(CHECK_COLUMN(result, 1, {1, 1, 1, 1}));
- // aggregate with correlation in HAVING clause
- result = con.Query("SELECT i, (SELECT i FROM integers GROUP BY i HAVING
i=i1.i) AS j FROM integers i1 ORDER BY i;");
- REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
- REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2, 3}));
- // correlated subquery in HAVING
- result = con.Query("SELECT i1.i, SUM(i) FROM integers i1 GROUP BY i1.i
HAVING SUM(i)=(SELECT MIN(i) FROM integers "
- "WHERE i<>i1.i+1) ORDER BY 1;");
- REQUIRE(CHECK_COLUMN(result, 0, {1}));
- REQUIRE(CHECK_COLUMN(result, 1, {1}));
- result = con.Query("SELECT i % 2 AS j, SUM(i) FROM integers i1 GROUP BY
j HAVING SUM(i)=(SELECT SUM(i) FROM "
- "integers WHERE i<>j+1) ORDER BY 1;");
- REQUIRE(CHECK_COLUMN(result, 0, {1}));
- REQUIRE(CHECK_COLUMN(result, 1, {4}));
+SELECT i, (SELECT MIN(i) FROM integers GROUP BY i1.i) AS j FROM integers i1
ORDER BY i;
+--1 1
+--2 1
+--3 1
+--NULL 1
+
+SELECT i, (SELECT i FROM integers GROUP BY i HAVING i=i1.i) AS j FROM integers
i1 ORDER BY i;
+--1 1
+--2 2
+--3 3
+--NULL NULL
+
+SELECT i1.i, CAST(SUM(i) AS BIGINT) FROM integers i1 GROUP BY i1.i HAVING
SUM(i)=(SELECT MIN(i) FROM integers WHERE i<>i1.i+1) ORDER BY 1;
+--1 1
+
+SELECT i % 2 AS j, CAST(SUM(i) AS BIGINT) FROM integers i1 GROUP BY j HAVING
SUM(i)=(SELECT SUM(i) FROM integers WHERE i<>j+1) ORDER BY 1;
+--1 4
+
+SELECT CAST((SELECT i+SUM(i1.i) FROM integers WHERE i=1 LIMIT 1) AS BIGINT)
FROM integers i1; --error, no LIMIT on subqueries
+
+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;*/
+--10
+--14
+--18
+--NULL
- // aggregate query with non-aggregate subquery without group by
- result = con.Query("SELECT (SELECT i+SUM(i1.i) FROM integers WHERE i=1
LIMIT 1) FROM integers i1;");
- REQUIRE(CHECK_COLUMN(result, 0, {7}));
+SELECT i, CAST((SELECT i+SUM(i1.i) FROM integers WHERE i=1) AS BIGINT) FROM
integers i1 GROUP BY i ORDER BY i;
+--1 2
+--2 3
+--3 4
+--NULL NULL
- result = con.Query("SELECT (SELECT SUM(i)+SUM(i1.i) FROM integers) FROM
integers i1 ORDER BY 1;");
- REQUIRE(CHECK_COLUMN(result, 0, {12}));
- result = con.Query("SELECT (SELECT SUM(i)+SUM((CASE WHEN i IS NOT NULL
THEN i*0 ELSE 0 END)+i1.i) FROM integers) "
- "FROM integers i1 ORDER BY 1;");
- REQUIRE(CHECK_COLUMN(result, 0, {Value(), 10, 14, 18}));
+SELECT CAST(SUM((SELECT i+i1.i FROM integers WHERE i=1)) AS BIGINT) FROM
integers i1;
+--9
+
+SELECT i, CAST(SUM(i1.i) AS BIGINT), CAST((SELECT SUM(i1.i) FROM integers) AS
BIGINT) AS k FROM integers i1 GROUP BY i ORDER BY i; --error, cardinality
violation, scalar expression expected
+
+SELECT i1.i AS j, CAST((SELECT SUM(j+i) FROM integers) AS BIGINT) AS k FROM
integers i1 GROUP BY j ORDER BY j;
+--1 9
+--2 12
+--3 15
+--NULL NULL
- // aggregate query with non-aggregate subquery with group by
- result =
- con.Query("SELECT i, (SELECT i+SUM(i1.i) FROM integers WHERE i=1)
FROM integers i1 GROUP BY i ORDER BY i;");
- REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
- REQUIRE(CHECK_COLUMN(result, 1, {Value(), 2, 3, 4}));
+/*BROKEN
+SELECT CAST((SELECT SUM(i1.i*i) FROM integers) AS BIGINT) FROM integers i1
ORDER BY i;*/
+--6
+--12
+--18
+--NULL
- // subquery inside aggregate
- result = con.Query("SELECT SUM((SELECT i+i1.i FROM integers WHERE i=1))
FROM integers i1;");
- REQUIRE(CHECK_COLUMN(result, 0, {9}));
+SELECT i, CAST((SELECT SUM(i1.i)) AS BIGINT) AS k, CAST((SELECT SUM(i1.i)) AS
BIGINT) AS l FROM integers i1 GROUP BY i ORDER BY i;
+--1 1 1
+--2 2 2
+--3 3 3
+--NULL NULL NULL
- result =
- con.Query("SELECT i, SUM(i1.i), (SELECT SUM(i1.i) FROM integers) AS
k FROM integers i1 GROUP BY i ORDER BY i;");
- REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
- REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2, 3}));
- REQUIRE(CHECK_COLUMN(result, 2, {Value(), 1, 2, 3}));
+SELECT i, CAST((SELECT SUM(i1.i)*SUM(i) FROM integers) AS BIGINT) AS k FROM
integers i1 GROUP BY i ORDER BY i;
+--1 6
+--2 12
+--3 18
+--NULL NULL
- // aggregation of both entries inside subquery
- // aggregate on group inside subquery
- result =
- con.Query("SELECT i1.i AS j, (SELECT SUM(j+i) FROM integers) AS k
FROM integers i1 GROUP BY j ORDER BY j;");
- REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
- REQUIRE(CHECK_COLUMN(result, 1, {Value(), 9, 12, 15}));
- result = con.Query("SELECT (SELECT SUM(i1.i*i) FROM integers) FROM
integers i1 ORDER BY i;");
- REQUIRE(CHECK_COLUMN(result, 0, {Value(), 6, 12, 18}));
- result =
- con.Query("SELECT i, (SELECT SUM(i1.i)) AS k, (SELECT SUM(i1.i)) AS
l FROM integers i1 GROUP BY i ORDER BY i;");
- REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
- REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2, 3}));
- REQUIRE(CHECK_COLUMN(result, 2, {Value(), 1, 2, 3}));
- // refer aggregation inside subquery
- result =
- con.Query("SELECT i, (SELECT SUM(i1.i)*SUM(i) FROM integers) AS k
FROM integers i1 GROUP BY i ORDER BY i;");
- REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
- REQUIRE(CHECK_COLUMN(result, 1, {Value(), 6, 12, 18}));
- // refer to GROUP BY inside subquery
- result = con.Query("SELECT i AS j, (SELECT j*SUM(i) FROM integers) AS k
FROM integers i1 GROUP BY j ORDER BY j;");
- REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
- REQUIRE(CHECK_COLUMN(result, 1, {Value(), 6, 12, 18}));
- // refer to GROUP BY without alias but with full name
- result =
- con.Query("SELECT i AS j, (SELECT i1.i*SUM(i) FROM integers) AS k
FROM integers i1 GROUP BY j ORDER BY j;");
- REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
- REQUIRE(CHECK_COLUMN(result, 1, {Value(), 6, 12, 18}));
- // perform SUM on subquery
- result =
- con.Query("SELECT i, SUM((SELECT SUM(i)*i1.i FROM integers)) AS k
FROM integers i1 GROUP BY i ORDER BY i;");
- REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
- REQUIRE(CHECK_COLUMN(result, 1, {Value(), 6, 12, 18}));
+SELECT i AS j, CAST((SELECT j*SUM(i) FROM integers) AS BIGINT) AS k FROM
integers i1 GROUP BY j ORDER BY j;
+--1 6
+--2 12
+--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
+--NULL NULL
- // aggregate subqueries cannot be nested
- REQUIRE_FAIL(con.Query(
- "SELECT i, SUM((SELECT SUM(i)*SUM(i1.i) FROM integers)) AS k FROM
integers i1 GROUP BY i ORDER BY i;"));
+SELECT i, CAST(SUM((SELECT SUM(i)*i1.i FROM integers)) AS BIGINT) AS k FROM
integers i1 GROUP BY i ORDER BY i;
+--1 6
+--2 12
+--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*/
- // aggregation but ONLY inside subquery results in implicit aggregation
- result = con.Query("SELECT (SELECT SUM(i1.i)) FROM integers i1;");
- REQUIRE(CHECK_COLUMN(result, 0, {6}));
- result = con.Query("SELECT FIRST(i), (SELECT SUM(i1.i)) FROM integers
i1;");
- REQUIRE(CHECK_COLUMN(result, 0, {1}));
- REQUIRE(CHECK_COLUMN(result, 1, {6}));
+SELECT CAST((SELECT SUM(i1.i)) AS BIGINT) FROM integers i1;
+--6
+
+SELECT FIRST(i), CAST((SELECT SUM(i1.i)) AS BIGINT) FROM integers i1; --error,
no first aggregate available yet
+
+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;
+--1 1
+--2 2
+--3 3
+--NULL NULL
- // aggregate that uses correlated column in aggregation
- result = con.Query("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;");
- REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
- REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2, 3}));
+SELECT i, SUM(i1.i) FROM integers i1 GROUP BY i ORDER BY (SELECT SUM(i1.i)
FROM integers); --error, cardinality violation, scalar expression expected
- // ORDER BY correlated subquery
- result = con.Query("SELECT i, SUM(i1.i) FROM integers i1 GROUP BY i
ORDER BY (SELECT SUM(i1.i) FROM integers);");
- REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
- REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2, 3}));
+SELECT i, SUM((SELECT SUM(i)*i1.i FROM integers LIMIT 0)) AS k FROM integers
i1 GROUP BY i ORDER BY i; --error, no LIMIT on subqueries
- // LIMIT 0 on correlated subquery
- result = con.Query(
- "SELECT i, SUM((SELECT SUM(i)*i1.i FROM integers LIMIT 0)) AS k
FROM integers i1 GROUP BY i ORDER BY i;");
- REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
- REQUIRE(CHECK_COLUMN(result, 1, {Value(), Value(), Value(), Value()}));
+SELECT (SELECT i+i1.i FROM integers WHERE i=1) AS k, CAST(SUM(i) AS BIGINT) AS
j FROM integers i1 GROUP BY k ORDER BY 1;
+--2 1
+--3 2
+--4 3
+--NULL NULL
- // GROUP BY correlated subquery
- result = con.Query(
- "SELECT (SELECT i+i1.i FROM integers WHERE i=1) AS k, SUM(i) AS j
FROM integers i1 GROUP BY k ORDER BY 1;");
- REQUIRE(CHECK_COLUMN(result, 0, {Value(), 2, 3, 4}));
- REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2, 3}));
+SELECT CAST(SUM(i) AS BIGINT) FROM integers i1 WHERE i>(SELECT (i+i1.i)/2 FROM
integers WHERE i=1);
+--5
+
+SELECT CAST(SUM(i) AS BIGINT) FROM integers i1 WHERE i>(SELECT (SUM(i)+i1.i)/2
FROM integers WHERE i=1);
+--5
- // correlated subquery in WHERE
- result = con.Query("SELECT SUM(i) FROM integers i1 WHERE i>(SELECT
(i+i1.i)/2 FROM integers WHERE i=1);");
- REQUIRE(CHECK_COLUMN(result, 0, {5}));
- // correlated aggregate in WHERE
- result = con.Query("SELECT SUM(i) FROM integers i1 WHERE i>(SELECT
(SUM(i)+i1.i)/2 FROM integers WHERE i=1);");
- REQUIRE(CHECK_COLUMN(result, 0, {5}));
+SELECT i, (SELECT MIN(i) FROM integers WHERE i=i1.i) >= ALL(SELECT i FROM
integers WHERE i IS NOT NULL) FROM integers i1 ORDER BY i;
+--1 False
+--2 False
+--3 True
+--NULL NULL
- // use scalar subquery as argument to ALL/ANY
- result = con.Query("SELECT i, (SELECT MIN(i) FROM integers WHERE
i=i1.i) >= ALL(SELECT i FROM integers WHERE i IS "
- "NOT NULL) FROM integers i1 ORDER BY i;");
- REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
- REQUIRE(CHECK_COLUMN(result, 1, {Value(), false, false, true}));
- result = con.Query("SELECT i, (SELECT MIN(i) FROM integers WHERE
i<>i1.i) > ANY(SELECT i FROM integers WHERE i IS "
- "NOT NULL) FROM integers i1 ORDER BY i;");
- REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
- REQUIRE(CHECK_COLUMN(result, 1, {Value(), true, false, false}));
- result = con.Query("SELECT i, NOT((SELECT MIN(i) FROM integers WHERE
i<>i1.i) > ANY(SELECT i FROM integers WHERE i "
- "IS NOT NULL)) FROM integers i1 ORDER BY i;");
- REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
- REQUIRE(CHECK_COLUMN(result, 1, {Value(), false, true, true}));
-}
+SELECT i, (SELECT MIN(i) FROM integers WHERE i<>i1.i) > ANY(SELECT i FROM
integers WHERE i IS NOT NULL) FROM integers i1 ORDER BY i;
+--1 True
+--2 False
+--3 False
+--NULL NULL
+SELECT i, NOT((SELECT MIN(i) FROM integers WHERE i<>i1.i) > ANY(SELECT i FROM
integers WHERE i IS NOT NULL)) FROM integers i1 ORDER BY i;
+--1 False
+--2 True
+--3 True
+--NULL NULL
+
+/*
TEST_CASE("Test correlated EXISTS subqueries", "[subquery]") {
unique_ptr<DuckDBResult> result;
DuckDB db(nullptr);
@@ -415,78 +414,104 @@ TEST_CASE("Test correlated ANY/ALL subqu
// zero results for all
result = con.Query("SELECT i=ANY(SELECT i FROM integers WHERE i=i1.i
AND i>10) FROM integers i1 ORDER BY i;");
REQUIRE(CHECK_COLUMN(result, 0, {false, false, false, false}));
-}
-
-TEST_CASE("Test for COUNT(*) and SUM(i) IS NULL in subqueries", "[subquery]") {
- unique_ptr<DuckDBResult> result;
- DuckDB db(nullptr);
- DuckDBConnection con(db);
-
- con.EnableQueryVerification();
- con.EnableProfiling();
-
- REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
- REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3),
(NULL)"));
-
- // COUNT(*) and SUM(i) IS NULL aggregates
- result = con.Query("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;");
- REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
- REQUIRE(CHECK_COLUMN(result, 1, {Value(), Value(), 3, Value()}));
- result =
- con.Query("SELECT i, (SELECT SUM(i) IS NULL FROM integers i2 WHERE
i2.i>i1.i) FROM integers i1 ORDER BY i;");
- REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
- REQUIRE(CHECK_COLUMN(result, 1, {true, false, false, true}));
- result = con.Query("SELECT i, (SELECT COUNT(*) FROM integers i2 WHERE
i2.i>i1.i) FROM integers i1 ORDER BY i;");
- REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
- REQUIRE(CHECK_COLUMN(result, 1, {0, 2, 1, 0}));
- result = con.Query(
- "SELECT i, (SELECT COUNT(i) FROM integers i2 WHERE i2.i>i1.i OR
i2.i IS NULL) FROM integers i1 ORDER BY i;");
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
