1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test conjunction statements", "[conjunction]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11 con.EnableQueryVerification();
12
13 // create table
14 REQUIRE_NO_FAIL(con.Query("CREATE TABLE a (i integer, j integer);"));
15 REQUIRE_NO_FAIL(con.Query("INSERT INTO a VALUES (3, 4), (4, 5), (5, 6)"));
16
17 result = con.Query("SELECT * FROM a WHERE (i > 3 AND j < 5) OR (i > 3 AND j > 5)");
18 REQUIRE(CHECK_COLUMN(result, 0, {5}));
19 REQUIRE(CHECK_COLUMN(result, 1, {6}));
20
21 // test boolean logic in conjunctions
22 result = con.Query("SELECT true AND true");
23 REQUIRE(CHECK_COLUMN(result, 0, {true}));
24 result = con.Query("SELECT true AND false");
25 REQUIRE(CHECK_COLUMN(result, 0, {false}));
26 result = con.Query("SELECT false AND true");
27 REQUIRE(CHECK_COLUMN(result, 0, {false}));
28 result = con.Query("SELECT false AND false");
29 REQUIRE(CHECK_COLUMN(result, 0, {false}));
30 result = con.Query("SELECT false AND NULL");
31 REQUIRE(CHECK_COLUMN(result, 0, {false}));
32 result = con.Query("SELECT NULL AND false");
33 REQUIRE(CHECK_COLUMN(result, 0, {false}));
34 result = con.Query("SELECT NULL AND true");
35 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
36 result = con.Query("SELECT true AND NULL");
37 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
38
39 result = con.Query("SELECT true OR true");
40 REQUIRE(CHECK_COLUMN(result, 0, {true}));
41 result = con.Query("SELECT true OR NULL");
42 REQUIRE(CHECK_COLUMN(result, 0, {true}));
43 result = con.Query("SELECT NULL OR true");
44 REQUIRE(CHECK_COLUMN(result, 0, {true}));
45 result = con.Query("SELECT false OR NULL");
46 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
47 result = con.Query("SELECT NULL OR false");
48 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
49 result = con.Query("SELECT true OR false");
50 REQUIRE(CHECK_COLUMN(result, 0, {true}));
51 result = con.Query("SELECT false OR true");
52 REQUIRE(CHECK_COLUMN(result, 0, {true}));
53 result = con.Query("SELECT false OR false");
54 REQUIRE(CHECK_COLUMN(result, 0, {false}));
55
56 // test single constant in conjunctions
57 result = con.Query("SELECT true AND i>3 FROM a ORDER BY i");
58 REQUIRE(CHECK_COLUMN(result, 0, {false, true, true}));
59 result = con.Query("SELECT i>3 AND true FROM a ORDER BY i");
60 REQUIRE(CHECK_COLUMN(result, 0, {false, true, true}));
61
62 result = con.Query("SELECT 2>3 AND i>3 FROM a ORDER BY i");
63 REQUIRE(CHECK_COLUMN(result, 0, {false, false, false}));
64 result = con.Query("SELECT false AND i>3 FROM a ORDER BY i");
65 REQUIRE(CHECK_COLUMN(result, 0, {false, false, false}));
66 result = con.Query("SELECT i>3 AND false FROM a ORDER BY i");
67 REQUIRE(CHECK_COLUMN(result, 0, {false, false, false}));
68
69 result = con.Query("SELECT false OR i>3 FROM a ORDER BY i");
70 REQUIRE(CHECK_COLUMN(result, 0, {false, true, true}));
71 result = con.Query("SELECT i>3 OR false FROM a ORDER BY i");
72 REQUIRE(CHECK_COLUMN(result, 0, {false, true, true}));
73
74 result = con.Query("SELECT true OR i>3 FROM a ORDER BY i");
75 REQUIRE(CHECK_COLUMN(result, 0, {true, true, true}));
76 result = con.Query("SELECT i>3 OR true FROM a ORDER BY i");
77 REQUIRE(CHECK_COLUMN(result, 0, {true, true, true}));
78
79 result = con.Query("SELECT NULL OR i>3 FROM a ORDER BY i");
80 REQUIRE(CHECK_COLUMN(result, 0, {Value(), true, true}));
81 result = con.Query("SELECT i>3 OR NULL FROM a ORDER BY i");
82 REQUIRE(CHECK_COLUMN(result, 0, {Value(), true, true}));
83
84 result = con.Query("SELECT NULL AND i>3 FROM a ORDER BY i");
85 REQUIRE(CHECK_COLUMN(result, 0, {false, Value(), Value()}));
86 result = con.Query("SELECT i>3 AND NULL FROM a ORDER BY i");
87 REQUIRE(CHECK_COLUMN(result, 0, {false, Value(), Value()}));
88}
89
90TEST_CASE("Test conjunction statements that can be simplified", "[conjunction]") {
91 unique_ptr<QueryResult> result;
92 DuckDB db(nullptr);
93 Connection con(db);
94 con.EnableQueryVerification();
95
96 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER);"));
97 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3), (NULL)"));
98
99 // test conjunctions in FILTER clause
100 result = con.Query("SELECT i FROM integers WHERE (i=1 AND i>0) OR (i=1 AND i<3) ORDER BY i");
101 REQUIRE(CHECK_COLUMN(result, 0, {1}));
102
103 result = con.Query("SELECT i FROM integers WHERE (i=1) OR (i=1) ORDER BY i");
104 REQUIRE(CHECK_COLUMN(result, 0, {1}));
105
106 result = con.Query("SELECT i FROM integers WHERE (i=1) OR (i=1) OR (i=1) OR (i=1) OR (i=1) ORDER BY i");
107 REQUIRE(CHECK_COLUMN(result, 0, {true}));
108
109 result = con.Query("SELECT i FROM integers WHERE (i IS NULL AND i=1) OR (i IS NULL AND i<10) ORDER BY i");
110 REQUIRE(CHECK_COLUMN(result, 0, {}));
111
112 result = con.Query("SELECT i FROM integers WHERE (i IS NOT NULL AND i>1) OR (i IS NOT NULL AND i<10) ORDER BY i");
113 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
114
115 result = con.Query(
116 "SELECT i FROM integers WHERE (i IS NULL AND (i+1) IS NULL) OR (i IS NULL AND (i+2) IS NULL) ORDER BY i");
117 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
118
119 result = con.Query("SELECT i FROM integers WHERE i=1 OR 1=1 ORDER BY i");
120 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
121
122 result = con.Query("SELECT i FROM integers WHERE i=1 OR 1=0 OR 1=1 ORDER BY i");
123 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
124
125 result = con.Query("SELECT i FROM integers WHERE (i=1 OR 1=0 OR i=1) AND (0=1 OR 1=0 OR 1=1) ORDER BY i");
126 REQUIRE(CHECK_COLUMN(result, 0, {1}));
127
128 // test conjunctions in SELECT clause
129 result = con.Query("SELECT (i=1 AND i>0) OR (i=1 AND i<3) FROM integers ORDER BY i");
130 REQUIRE(CHECK_COLUMN(result, 0, {Value(), true, false, false}));
131
132 result = con.Query("SELECT (i=1) OR (i=1) FROM integers ORDER BY i");
133 REQUIRE(CHECK_COLUMN(result, 0, {Value(), true, false, false}));
134
135 result = con.Query("SELECT (i=1) OR (i=1) OR (i=1) OR (i=1) OR (i=1) FROM integers ORDER BY i");
136 REQUIRE(CHECK_COLUMN(result, 0, {Value(), true, false, false}));
137
138 result = con.Query("SELECT (i IS NULL AND i=1) OR (i IS NULL AND i<10) FROM integers ORDER BY i");
139 REQUIRE(CHECK_COLUMN(result, 0, {Value(), false, false, false}));
140
141 result = con.Query("SELECT (i IS NOT NULL AND i>1) OR (i IS NOT NULL AND i<10) FROM integers ORDER BY i");
142 REQUIRE(CHECK_COLUMN(result, 0, {false, true, true, true}));
143
144 result =
145 con.Query("SELECT (i IS NULL AND (i+1) IS NULL) OR (i IS NULL AND (i+2) IS NULL) FROM integers ORDER BY i");
146 REQUIRE(CHECK_COLUMN(result, 0, {true, false, false, false}));
147}
148