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