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 ANY/ALL queries", "[subquery]") {
10 unique_ptr<QueryResult> result;
11 DuckDB db(nullptr);
12 Connection con(db);
13 con.EnableQueryVerification();
14
15 // scalar ANY
16 result = con.Query("SELECT 1 = ANY(SELECT 1)");
17 REQUIRE(CHECK_COLUMN(result, 0, {true}));
18 result = con.Query("SELECT 1 = ANY(SELECT NULL)");
19 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
20 result = con.Query("SELECT 1 = ANY(SELECT 2)");
21 REQUIRE(CHECK_COLUMN(result, 0, {false}));
22 result = con.Query("SELECT NULL = ANY(SELECT 2)");
23 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
24
25 // scalar ALL
26 result = con.Query("SELECT 1 = ALL(SELECT 1)");
27 REQUIRE(CHECK_COLUMN(result, 0, {true}));
28 result = con.Query("SELECT 1 = ALL(SELECT NULL)");
29 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
30 result = con.Query("SELECT 1 = ALL(SELECT 2)");
31 REQUIRE(CHECK_COLUMN(result, 0, {false}));
32 result = con.Query("SELECT NULL = ALL(SELECT 2)");
33 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
34}
35
36TEST_CASE("Test ANY/ALL queries", "[subquery]") {
37 unique_ptr<QueryResult> result;
38 DuckDB db(nullptr);
39 Connection con(db);
40 con.EnableQueryVerification();
41
42 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
43 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3)"));
44
45 // ANY is like EXISTS without NULL values
46 result = con.Query("SELECT 2 > ANY(SELECT * FROM integers)");
47 REQUIRE(CHECK_COLUMN(result, 0, {true}));
48 result = con.Query("SELECT 1 > ANY(SELECT * FROM integers)");
49 REQUIRE(CHECK_COLUMN(result, 0, {false}));
50
51 result = con.Query("SELECT 4 > ALL(SELECT * FROM integers)");
52 REQUIRE(CHECK_COLUMN(result, 0, {true}));
53 result = con.Query("SELECT 1 > ALL(SELECT * FROM integers)");
54 REQUIRE(CHECK_COLUMN(result, 0, {false}));
55
56 // NULL input always results in NULL output
57 result = con.Query("SELECT NULL > ANY(SELECT * FROM integers)");
58 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
59 result = con.Query("SELECT NULL > ALL(SELECT * FROM integers)");
60 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
61
62 // now with a NULL value in the input
63 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (NULL)"));
64
65 // ANY returns either true or NULL
66 result = con.Query("SELECT 2 > ANY(SELECT * FROM integers)");
67 REQUIRE(CHECK_COLUMN(result, 0, {true}));
68 result = con.Query("SELECT 1 > ANY(SELECT * FROM integers)");
69 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
70
71 // ALL returns either NULL or false
72 result = con.Query("SELECT 4 > ALL(SELECT * FROM integers)");
73 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
74 result = con.Query("SELECT 1 > ALL(SELECT * FROM integers)");
75 REQUIRE(CHECK_COLUMN(result, 0, {false}));
76
77 // NULL input always results in NULL
78 result = con.Query("SELECT NULL > ANY(SELECT * FROM integers)");
79 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
80 result = con.Query("SELECT NULL > ALL(SELECT * FROM integers)");
81 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
82}
83