1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test expressions with obsolete filters", "[filter]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11 con.EnableQueryVerification();
12
13 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(a INTEGER, b INTEGER)"));
14 REQUIRE_NO_FAIL(
15 con.Query("INSERT INTO integers VALUES (1, 10), (2, 12), (3, 14), (4, 16), (5, NULL), (NULL, NULL)"));
16
17 // Obsolete filters that can be pruned
18 result = con.Query("SELECT * FROM integers WHERE TRUE ORDER BY 1");
19 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3, 4, 5}));
20 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 10, 12, 14, 16, Value()}));
21 result = con.Query("SELECT * FROM integers WHERE FALSE ORDER BY 1");
22 REQUIRE(CHECK_COLUMN(result, 0, {}));
23 REQUIRE(CHECK_COLUMN(result, 1, {}));
24 result = con.Query("SELECT * FROM integers WHERE NULL ORDER BY 1");
25 REQUIRE(CHECK_COLUMN(result, 0, {}));
26 REQUIRE(CHECK_COLUMN(result, 1, {}));
27 // involving equality
28 result = con.Query("SELECT * FROM integers WHERE a=2 AND a=2");
29 REQUIRE(CHECK_COLUMN(result, 0, {2}));
30 REQUIRE(CHECK_COLUMN(result, 1, {12}));
31 result = con.Query("SELECT * FROM integers WHERE a=2 AND a>0");
32 REQUIRE(CHECK_COLUMN(result, 0, {2}));
33 REQUIRE(CHECK_COLUMN(result, 1, {12}));
34 result = con.Query("SELECT * FROM integers WHERE a>0 AND a=2");
35 REQUIRE(CHECK_COLUMN(result, 0, {2}));
36 REQUIRE(CHECK_COLUMN(result, 1, {12}));
37 result = con.Query("SELECT * FROM integers WHERE a=2 AND a<4");
38 REQUIRE(CHECK_COLUMN(result, 0, {2}));
39 REQUIRE(CHECK_COLUMN(result, 1, {12}));
40 result = con.Query("SELECT * FROM integers WHERE a<4 AND a=2");
41 REQUIRE(CHECK_COLUMN(result, 0, {2}));
42 REQUIRE(CHECK_COLUMN(result, 1, {12}));
43 result = con.Query("SELECT * FROM integers WHERE a=2 AND a<=2");
44 REQUIRE(CHECK_COLUMN(result, 0, {2}));
45 REQUIRE(CHECK_COLUMN(result, 1, {12}));
46 result = con.Query("SELECT * FROM integers WHERE a=2 AND a>=2");
47 REQUIRE(CHECK_COLUMN(result, 0, {2}));
48 REQUIRE(CHECK_COLUMN(result, 1, {12}));
49 // involving multiple GREATER THAN expressions
50 result = con.Query("SELECT * FROM integers WHERE a>2 AND a>4");
51 REQUIRE(CHECK_COLUMN(result, 0, {5}));
52 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
53 result = con.Query("SELECT * FROM integers WHERE a>4 AND a>2");
54 REQUIRE(CHECK_COLUMN(result, 0, {5}));
55 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
56 result = con.Query("SELECT * FROM integers WHERE a>4 AND a>=4");
57 REQUIRE(CHECK_COLUMN(result, 0, {5}));
58 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
59 result = con.Query("SELECT * FROM integers WHERE a>=4 AND a>4");
60 REQUIRE(CHECK_COLUMN(result, 0, {5}));
61 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
62 // involving multiple LESS THAN expressions
63 result = con.Query("SELECT * FROM integers WHERE a<2 AND a<4");
64 REQUIRE(CHECK_COLUMN(result, 0, {1}));
65 REQUIRE(CHECK_COLUMN(result, 1, {10}));
66 result = con.Query("SELECT * FROM integers WHERE a<4 AND a<2");
67 REQUIRE(CHECK_COLUMN(result, 0, {1}));
68 REQUIRE(CHECK_COLUMN(result, 1, {10}));
69 result = con.Query("SELECT * FROM integers WHERE a<2 AND a<=2");
70 REQUIRE(CHECK_COLUMN(result, 0, {1}));
71 REQUIRE(CHECK_COLUMN(result, 1, {10}));
72 result = con.Query("SELECT * FROM integers WHERE a<=2 AND a<2");
73 REQUIRE(CHECK_COLUMN(result, 0, {1}));
74 REQUIRE(CHECK_COLUMN(result, 1, {10}));
75 // involving inequality expression
76 result = con.Query("SELECT * FROM integers WHERE a<2 AND a<>3");
77 REQUIRE(CHECK_COLUMN(result, 0, {1}));
78 REQUIRE(CHECK_COLUMN(result, 1, {10}));
79 result = con.Query("SELECT * FROM integers WHERE a<=1 AND a<>3");
80 REQUIRE(CHECK_COLUMN(result, 0, {1}));
81 REQUIRE(CHECK_COLUMN(result, 1, {10}));
82 result = con.Query("SELECT * FROM integers WHERE a>4 AND a<>2");
83 REQUIRE(CHECK_COLUMN(result, 0, {5}));
84 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
85 result = con.Query("SELECT * FROM integers WHERE a>=5 AND a<>2");
86 REQUIRE(CHECK_COLUMN(result, 0, {5}));
87 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
88 result = con.Query("SELECT * FROM integers WHERE a>=4 AND a<>4 AND a<>4");
89 REQUIRE(CHECK_COLUMN(result, 0, {5}));
90 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
91
92 // many conditions
93 result = con.Query("SELECT * FROM integers WHERE a<3 AND a<4 AND a<5 AND a<10 AND a<2 AND a<20");
94 REQUIRE(CHECK_COLUMN(result, 0, {1}));
95 REQUIRE(CHECK_COLUMN(result, 1, {10}));
96
97 /////////////////////////////////////////////////////////
98 // Obsolete filters that always result in zero results //
99 /////////////////////////////////////////////////////////
100 // (i.e. entire tree can be pruned)
101 // involving equality
102 result = con.Query("SELECT * FROM integers WHERE a=2 AND a=4");
103 REQUIRE(CHECK_COLUMN(result, 0, {}));
104 // greater than and equality
105 result = con.Query("SELECT * FROM integers WHERE a=2 AND a>4");
106 REQUIRE(CHECK_COLUMN(result, 0, {}));
107 result = con.Query("SELECT * FROM integers WHERE a>4 AND a=2");
108 REQUIRE(CHECK_COLUMN(result, 0, {}));
109 result = con.Query("SELECT * FROM integers WHERE a=2 AND a>2");
110 REQUIRE(CHECK_COLUMN(result, 0, {}));
111 result = con.Query("SELECT * FROM integers WHERE a>=4 AND a=2");
112 REQUIRE(CHECK_COLUMN(result, 0, {}));
113 // less than and equality
114 result = con.Query("SELECT * FROM integers WHERE a=4 AND a<2");
115 REQUIRE(CHECK_COLUMN(result, 0, {}));
116 result = con.Query("SELECT * FROM integers WHERE a<2 AND a=4");
117 REQUIRE(CHECK_COLUMN(result, 0, {}));
118 result = con.Query("SELECT * FROM integers WHERE a=2 AND a<2");
119 REQUIRE(CHECK_COLUMN(result, 0, {}));
120 result = con.Query("SELECT * FROM integers WHERE a<=2 AND a=4");
121 REQUIRE(CHECK_COLUMN(result, 0, {}));
122 // less than and greater than
123 result = con.Query("SELECT * FROM integers WHERE a<2 AND a>4");
124 REQUIRE(CHECK_COLUMN(result, 0, {}));
125 // inequality
126 result = con.Query("SELECT * FROM integers WHERE a=2 AND a<>2");
127 REQUIRE(CHECK_COLUMN(result, 0, {}));
128 result = con.Query("SELECT * FROM integers WHERE a<>2 AND a=2");
129 REQUIRE(CHECK_COLUMN(result, 0, {}));
130 // WHERE clause with explicit FALSE
131 result = con.Query("SELECT * FROM integers WHERE 0");
132 REQUIRE(CHECK_COLUMN(result, 0, {}));
133 result = con.Query("SELECT * FROM integers WHERE a<2 AND 0");
134 REQUIRE(CHECK_COLUMN(result, 0, {}));
135}
136
137TEST_CASE("Test string expressions with obsolete filters", "[filter]") {
138 unique_ptr<QueryResult> result;
139 DuckDB db(nullptr);
140 Connection con(db);
141 con.EnableQueryVerification();
142 con.EnableProfiling();
143
144 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(s VARCHAR)"));
145 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES ('hello'), ('world'), (NULL)"));
146
147 // equality
148 result = con.Query("SELECT * FROM strings WHERE s='hello' AND s='hello'");
149 REQUIRE(CHECK_COLUMN(result, 0, {"hello"}));
150 result = con.Query("SELECT * FROM strings WHERE s='hello' AND s='world'");
151 REQUIRE(CHECK_COLUMN(result, 0, {}));
152 // inequality
153 result = con.Query("SELECT * FROM strings WHERE s='hello' AND s<>'hello'");
154 REQUIRE(CHECK_COLUMN(result, 0, {}));
155 result = con.Query("SELECT * FROM strings WHERE s='hello' AND s<>'world'");
156 REQUIRE(CHECK_COLUMN(result, 0, {"hello"}));
157 // range queries
158 result = con.Query("SELECT * FROM strings WHERE s='hello' AND s>'a'");
159 REQUIRE(CHECK_COLUMN(result, 0, {"hello"}));
160 result = con.Query("SELECT * FROM strings WHERE s='hello' AND s>='hello'");
161 REQUIRE(CHECK_COLUMN(result, 0, {"hello"}));
162 result = con.Query("SELECT * FROM strings WHERE s='hello' AND s<='hello'");
163 REQUIRE(CHECK_COLUMN(result, 0, {"hello"}));
164 result = con.Query("SELECT * FROM strings WHERE s='hello' AND s<'z'");
165 REQUIRE(CHECK_COLUMN(result, 0, {"hello"}));
166 // range queries with ezro results
167 result = con.Query("SELECT * FROM strings WHERE s='hello' AND s<='a'");
168 REQUIRE(CHECK_COLUMN(result, 0, {}));
169 result = con.Query("SELECT * FROM strings WHERE s='hello' AND s<'hello'");
170 REQUIRE(CHECK_COLUMN(result, 0, {}));
171 result = con.Query("SELECT * FROM strings WHERE s='hello' AND s>'hello'");
172 REQUIRE(CHECK_COLUMN(result, 0, {}));
173 result = con.Query("SELECT * FROM strings WHERE s='hello' AND s>='z'");
174 REQUIRE(CHECK_COLUMN(result, 0, {}));
175 // range queries with inequality
176 result = con.Query("SELECT * FROM strings WHERE s<>'hello' AND s<='a'");
177 REQUIRE(CHECK_COLUMN(result, 0, {}));
178 result = con.Query("SELECT * FROM strings WHERE s<>'hello' AND s<'hello'");
179 REQUIRE(CHECK_COLUMN(result, 0, {}));
180 result = con.Query("SELECT * FROM strings WHERE s<>'hello' AND s>'hello'");
181 REQUIRE(CHECK_COLUMN(result, 0, {"world"}));
182 result = con.Query("SELECT * FROM strings WHERE s<>'world' AND s>='hello'");
183 REQUIRE(CHECK_COLUMN(result, 0, {"hello"}));
184}
185