1#include "catch.hpp"
2#include "duckdb/common/file_system.hpp"
3#include "dbgen.hpp"
4#include "test_helpers.hpp"
5
6using namespace duckdb;
7using namespace std;
8
9TEST_CASE("Test scalar subqueries", "[subquery]") {
10 unique_ptr<QueryResult> result;
11 DuckDB db(nullptr);
12 Connection con(db);
13 con.EnableQueryVerification();
14
15 result = con.Query("SELECT 1+(SELECT 1)");
16 REQUIRE(CHECK_COLUMN(result, 0, {2}));
17
18 result = con.Query("SELECT 1=(SELECT 1)");
19 REQUIRE(CHECK_COLUMN(result, 0, {true}));
20 result = con.Query("SELECT 1<>(SELECT 1)");
21 REQUIRE(CHECK_COLUMN(result, 0, {false}));
22 result = con.Query("SELECT 1=(SELECT NULL)");
23 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
24 result = con.Query("SELECT NULL=(SELECT 1)");
25 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
26}
27
28TEST_CASE("Test scalar EXISTS query", "[subquery]") {
29 unique_ptr<QueryResult> result;
30 DuckDB db(nullptr);
31 Connection con(db);
32 con.EnableQueryVerification();
33
34 result = con.Query("SELECT EXISTS(SELECT 1)");
35 REQUIRE(CHECK_COLUMN(result, 0, {true}));
36
37 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
38 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3), (NULL)"));
39
40 result = con.Query("SELECT EXISTS(SELECT 1) FROM integers");
41 REQUIRE(CHECK_COLUMN(result, 0, {true, true, true, true}));
42
43 result = con.Query("SELECT EXISTS(SELECT * FROM integers)");
44 REQUIRE(CHECK_COLUMN(result, 0, {true}));
45
46 result = con.Query("SELECT EXISTS(SELECT * FROM integers WHERE i IS NULL)");
47 REQUIRE(CHECK_COLUMN(result, 0, {true}));
48}
49
50TEST_CASE("Test scalar IN query", "[subquery]") {
51 unique_ptr<QueryResult> result;
52 DuckDB db(nullptr);
53 Connection con(db);
54 con.EnableQueryVerification();
55
56 result = con.Query("SELECT 1 IN (SELECT 1)");
57 REQUIRE(CHECK_COLUMN(result, 0, {true}));
58
59 result = con.Query("SELECT NULL IN (SELECT 1)");
60 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
61
62 result = con.Query("SELECT 1 IN (SELECT NULL)");
63 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
64
65 result = con.Query("SELECT 1 IN (SELECT 2)");
66 REQUIRE(CHECK_COLUMN(result, 0, {false}));
67
68 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
69 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3)"));
70
71 result = con.Query("SELECT 4 IN (SELECT * FROM integers)");
72 REQUIRE(CHECK_COLUMN(result, 0, {false}));
73
74 result = con.Query("SELECT 1 IN (SELECT * FROM integers)");
75 REQUIRE(CHECK_COLUMN(result, 0, {true}));
76
77 result = con.Query("SELECT 1 IN (SELECT * FROM integers) FROM integers");
78 REQUIRE(CHECK_COLUMN(result, 0, {true, true, true}));
79
80 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (NULL)"));
81
82 result = con.Query("SELECT 4 IN (SELECT * FROM integers)");
83 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
84
85 result = con.Query("SELECT 1 IN (SELECT * FROM integers)");
86 REQUIRE(CHECK_COLUMN(result, 0, {true}));
87
88 result = con.Query("SELECT * FROM integers WHERE (4 IN (SELECT * FROM integers)) IS NULL ORDER BY 1");
89 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
90
91 result = con.Query("SELECT * FROM integers WHERE (i IN (SELECT * FROM integers)) IS NULL ORDER BY 1");
92 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
93}
94