1#include "catch.hpp"
2#include "duckdb/common/helper.hpp"
3#include "duckdb/planner/binder.hpp"
4#include "duckdb/planner/expression/bound_comparison_expression.hpp"
5#include "duckdb/planner/expression/bound_operator_expression.hpp"
6#include "duckdb/planner/expression/common_subexpression.hpp"
7#include "expression_helper.hpp"
8#include "test_helpers.hpp"
9#include "duckdb/planner/operator/logical_get.hpp"
10#include <duckdb/optimizer/filter_pushdown.hpp>
11#include <duckdb/optimizer/optimizer.hpp>
12#include <vector>
13using namespace duckdb;
14using namespace std;
15
16TEST_CASE("Test Table Filter Push Down", "[filterpushdown-optimizer]") {
17 ExpressionHelper helper;
18 auto &con = helper.con;
19 Binder binder(*con.context);
20 Optimizer opt(binder, *con.context);
21 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i integer, j integer, k integer )"));
22 //! Checking if Optimizer push predicates down
23 auto tree = helper.ParseLogicalTree("SELECT k FROM integers where i+j > 10 and j = 5 and i = k+1 ");
24 FilterPushdown predicatePushdown(opt);
25 //! The generated plan should be Projection -> Filter (2) ->Get (1)
26 auto plan = predicatePushdown.Rewrite(move(tree));
27 REQUIRE(plan->children[0]->type == LogicalOperatorType::FILTER);
28 REQUIRE(plan->children[0]->expressions.size() == 2);
29
30 REQUIRE(plan->children[0]->children[0]->type == LogicalOperatorType::GET);
31 REQUIRE(plan->children[0]->children[0]->expressions.size() == 1);
32
33 tree = helper.ParseLogicalTree("SELECT k FROM integers where j = 5 ");
34 //! The generated plan should be Projection -> Get (1)
35 plan = predicatePushdown.Rewrite(move(tree));
36 REQUIRE(plan->children[0]->type == LogicalOperatorType::GET);
37 REQUIRE(plan->children[0]->expressions.size() == 1);
38}
39
40TEST_CASE("Test Table Filter Push Down Multiple Filters", "[filterpushdown-optimizer]") {
41 ExpressionHelper helper;
42 auto &con = helper.con;
43 Binder binder(*con.context);
44 Optimizer opt(binder, *con.context);
45 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i integer, j integer, k integer )"));
46 //! Checking if Optimizer push predicates down
47 auto tree = helper.ParseLogicalTree("SELECT k FROM integers where j = 5 and i = 10 ");
48 FilterPushdown predicatePushdown(opt);
49 //! The generated plan should be Projection ->Get (2)
50 auto plan = predicatePushdown.Rewrite(move(tree));
51 REQUIRE(plan->children[0]->type == LogicalOperatorType::GET);
52 REQUIRE(plan->children[0]->expressions.size() == 2);
53}
54
55TEST_CASE("Test Table Filter All Numeric Data Types", "[filterpushdown-optimizer]") {
56 vector<string> data_types{"tinyint", "smallint", "integer", "bigint", "numeric", "real"};
57 ExpressionHelper helper;
58 auto &con = helper.con;
59 Binder binder(*con.context);
60 Optimizer opt(binder, *con.context);
61 for (auto &data_type : data_types) {
62 REQUIRE_NO_FAIL(
63 con.Query("CREATE TABLE tablinho(i " + data_type + " , j " + data_type + ", k " + data_type + " )"));
64 //! Checking if Optimizer push predicates down
65 auto tree = helper.ParseLogicalTree("SELECT k FROM tablinho where j = CAST( 1 AS " + data_type + ")");
66 FilterPushdown predicatePushdown(opt);
67 //! The generated plan should be Projection ->Get (2)
68 auto plan = predicatePushdown.Rewrite(move(tree));
69 REQUIRE(plan->children[0]->type == LogicalOperatorType::GET);
70 REQUIRE(plan->children[0]->expressions.size() == 1);
71
72 tree = helper.ParseLogicalTree("SELECT k FROM tablinho where j > CAST( 1 AS " + data_type + ")");
73 //! The generated plan should be Projection ->Get (2)
74 plan = predicatePushdown.Rewrite(move(tree));
75 REQUIRE(plan->children[0]->type == LogicalOperatorType::GET);
76 REQUIRE(plan->children[0]->expressions.size() == 1);
77
78 tree = helper.ParseLogicalTree("SELECT k FROM tablinho where j >= CAST( 1 AS " + data_type + ")");
79 //! The generated plan should be Projection ->Get (2)
80 plan = predicatePushdown.Rewrite(move(tree));
81 REQUIRE(plan->children[0]->type == LogicalOperatorType::GET);
82 REQUIRE(plan->children[0]->expressions.size() == 1);
83
84 tree = helper.ParseLogicalTree("SELECT k FROM tablinho where j < CAST( 1 AS " + data_type + ")");
85 //! The generated plan should be Projection ->Get (2)
86 plan = predicatePushdown.Rewrite(move(tree));
87 REQUIRE(plan->children[0]->type == LogicalOperatorType::GET);
88 REQUIRE(plan->children[0]->expressions.size() == 1);
89
90 tree = helper.ParseLogicalTree("SELECT k FROM tablinho where j <= CAST( 1 AS " + data_type + ")");
91 //! The generated plan should be Projection ->Get (2)
92 plan = predicatePushdown.Rewrite(move(tree));
93 REQUIRE(plan->children[0]->type == LogicalOperatorType::GET);
94 REQUIRE(plan->children[0]->expressions.size() == 1);
95 REQUIRE_NO_FAIL(con.Query("DROP TABLE tablinho"));
96 }
97}
98
99TEST_CASE("Test Index vs Pushdown", "[filterpushdown-optimizer]") {
100 ExpressionHelper helper;
101 auto &con = helper.con;
102 Binder binder(*con.context);
103 Optimizer opt(binder, *con.context);
104 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i integer, j integer, k integer )"));
105 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON integers using art(i)"));
106 //! Checking if Optimizer push predicates down
107 auto tree = helper.ParseLogicalTree("SELECT k FROM integers where i = 10 ");
108 FilterPushdown predicatePushdown(opt);
109 //! The generated plan should be Projection ->Filter -> Get
110 auto plan = predicatePushdown.Rewrite(move(tree));
111 REQUIRE(plan->children[0]->type == LogicalOperatorType::FILTER);
112 REQUIRE(plan->children[0]->children[0]->type == LogicalOperatorType::GET);
113}
114
115TEST_CASE("Test Table Filter Push Down String", "[filterpushdown-optimizer]") {
116 ExpressionHelper helper;
117 auto &con = helper.con;
118 Binder binder(*con.context);
119 Optimizer opt(binder, *con.context);
120 REQUIRE_NO_FAIL(con.Query("CREATE TABLE tablinho(i varchar )"));
121 //! Checking if Optimizer push predicates down
122 auto tree = helper.ParseLogicalTree("SELECT i FROM tablinho where i = 'bla' ");
123 //! The generated plan should be Projection ->Get (1)
124 auto plan = opt.Optimize(move(tree));
125 REQUIRE(plan->children[0]->type == LogicalOperatorType::GET);
126 REQUIRE(plan->children[0]->expressions.size() == 1);
127
128 tree = helper.ParseLogicalTree("SELECT i FROM tablinho where i = 'bla' ");
129 //! The generated plan should be Projection ->Get (1)
130 plan = opt.Optimize(move(tree));
131 REQUIRE(plan->children[0]->type == LogicalOperatorType::GET);
132 REQUIRE(plan->children[0]->expressions.size() == 1);
133
134 tree = helper.ParseLogicalTree("SELECT i FROM tablinho where i > 'bla' ");
135 //! The generated plan should be Projection ->Get (1)
136 plan = opt.Optimize(move(tree));
137 REQUIRE(plan->children[0]->type == LogicalOperatorType::GET);
138 REQUIRE(plan->children[0]->expressions.size() == 1);
139
140 tree = helper.ParseLogicalTree("SELECT i FROM tablinho where i >= 'bla' ");
141 //! The generated plan should be Projection ->Get (1)
142 plan = opt.Optimize(move(tree));
143 REQUIRE(plan->children[0]->type == LogicalOperatorType::GET);
144 REQUIRE(plan->children[0]->expressions.size() == 1);
145
146 tree = helper.ParseLogicalTree("SELECT i FROM tablinho where i < 'bla' ");
147 //! The generated plan should be Projection ->Get (1)
148 plan = opt.Optimize(move(tree));
149 REQUIRE(plan->children[0]->type == LogicalOperatorType::GET);
150 REQUIRE(plan->children[0]->expressions.size() == 1);
151
152 tree = helper.ParseLogicalTree("SELECT i FROM tablinho where i <= 'bla' ");
153 //! The generated plan should be Projection ->Get (1)
154 plan = opt.Optimize(move(tree));
155 REQUIRE(plan->children[0]->type == LogicalOperatorType::GET);
156 REQUIRE(plan->children[0]->expressions.size() == 1);
157
158 tree = helper.ParseLogicalTree("SELECT i FROM tablinho where i like 'bla%' ");
159
160 //! The generated plan should be Projection ->filter(1)->get(0,2)
161 plan = opt.Optimize(move(tree));
162 REQUIRE(plan->children[0]->type == LogicalOperatorType::FILTER);
163 REQUIRE(plan->children[0]->expressions.size() == 1);
164 REQUIRE(plan->children[0]->children[0]->type == LogicalOperatorType::GET);
165 REQUIRE(plan->children[0]->children[0]->expressions.size() == 0);
166 REQUIRE(((LogicalGet *)plan->children[0]->children[0].get())->tableFilters.size() == 2);
167
168 tree = helper.ParseLogicalTree("SELECT i FROM tablinho where i like 'bla_bla' ");
169
170 //! The generated plan should be Projection ->filter(1)->get(0,2)
171 plan = opt.Optimize(move(tree));
172 REQUIRE(plan->children[0]->type == LogicalOperatorType::FILTER);
173 REQUIRE(plan->children[0]->expressions.size() == 1);
174 REQUIRE(plan->children[0]->children[0]->type == LogicalOperatorType::GET);
175 REQUIRE(plan->children[0]->children[0]->expressions.size() == 0);
176 REQUIRE(((LogicalGet *)plan->children[0]->children[0].get())->tableFilters.size() == 2);
177
178 //! The generated plan should be Projection ->filter(1)->get(0,1)
179 tree = helper.ParseLogicalTree("SELECT i FROM tablinho where i like 'bla' ");
180 plan = opt.Optimize(move(tree));
181 REQUIRE(plan->children[0]->type == LogicalOperatorType::FILTER);
182 REQUIRE(plan->children[0]->expressions.size() == 1);
183 REQUIRE(plan->children[0]->children[0]->type == LogicalOperatorType::GET);
184 REQUIRE(plan->children[0]->children[0]->expressions.size() == 0);
185 REQUIRE(((LogicalGet *)plan->children[0]->children[0].get())->tableFilters.size() == 1);
186
187 //! The generated plan should be Projection ->filter(1)->get(0,0)
188 tree = helper.ParseLogicalTree("SELECT i FROM tablinho where i like '%bla' ");
189 plan = opt.Optimize(move(tree));
190 REQUIRE(plan->children[0]->type == LogicalOperatorType::FILTER);
191 REQUIRE(plan->children[0]->expressions.size() == 1);
192 REQUIRE(plan->children[0]->children[0]->type == LogicalOperatorType::GET);
193 REQUIRE(plan->children[0]->children[0]->expressions.size() == 0);
194 REQUIRE(((LogicalGet *)plan->children[0]->children[0].get())->tableFilters.size() == 0);
195
196 //! The generated plan should be Projection ->filter(1)->get(0,0)
197 tree = helper.ParseLogicalTree("SELECT i FROM tablinho where i like '_bla' ");
198 plan = opt.Optimize(move(tree));
199 REQUIRE(plan->children[0]->type == LogicalOperatorType::FILTER);
200 REQUIRE(plan->children[0]->expressions.size() == 1);
201 REQUIRE(plan->children[0]->children[0]->type == LogicalOperatorType::GET);
202 REQUIRE(plan->children[0]->children[0]->expressions.size() == 0);
203 REQUIRE(((LogicalGet *)plan->children[0]->children[0].get())->tableFilters.size() == 0);
204}
205
206TEST_CASE("Test Table Filter Push Down Scan", "[filterpushdown-optimizer][.]") {
207 unique_ptr<QueryResult> result;
208 DuckDB db(nullptr);
209 Connection con(db);
210
211 vector<int> input;
212 idx_t input_size = 100;
213 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i integer, j integer)"));
214 for (idx_t i = 0; i < input_size; ++i) {
215 input.push_back(i);
216 }
217 for (idx_t i = 0; i < input_size; ++i) {
218 REQUIRE_NO_FAIL(
219 con.Query("INSERT INTO integers VALUES(" + to_string(input[i]) + "," + to_string(input[i]) + ")"));
220 }
221
222 result = con.Query("SELECT j FROM integers where j = 99 ");
223 REQUIRE(CHECK_COLUMN(result, 0, {99}));
224
225 result = con.Query("SELECT i FROM integers where j = 99 and i = 99 ");
226 REQUIRE(CHECK_COLUMN(result, 0, {99}));
227
228 result = con.Query("SELECT i FROM integers where j = 99 and i = 90 ");
229 REQUIRE(CHECK_COLUMN(result, 0, {}));
230
231 result = con.Query("SELECT count(i) FROM integers where j > 90 and i < 95 ");
232 REQUIRE(CHECK_COLUMN(result, 0, {4}));
233
234 result = con.Query("SELECT count(i) FROM integers where j > 90 and j < 95 ");
235 REQUIRE(CHECK_COLUMN(result, 0, {4}));
236}
237
238TEST_CASE("Test Table Filter Push Down Scan TPCQ6", "[filterpushdown-optimizer]") {
239 ExpressionHelper helper;
240 auto &con = helper.con;
241 Binder binder(*con.context);
242 Optimizer opt(binder, *con.context);
243 REQUIRE_NO_FAIL(
244 con.Query("CREATE TABLE LINEITEM(L_ORDERKEY INTEGER NOT NULL, L_PARTKEY INTEGER NOT NULL,L_SUPPKEY INTEGER "
245 "NOT NULL, L_LINENUMBER INTEGER NOT NULL,L_QUANTITY DECIMAL(15,2) NOT NULL,L_EXTENDEDPRICE "
246 "DECIMAL(15,2) NOT NULL, L_DISCOUNT DECIMAL(15,2) NOT NULL, L_TAX DECIMAL(15,2) NOT "
247 "NULL,L_RETURNFLAG CHAR(1) NOT NULL, L_LINESTATUS CHAR(1) NOT NULL, L_SHIPDATE DATE NOT NULL, "
248 "L_COMMITDATE DATE NOT NULL,L_RECEIPTDATE DATE NOT NULL, L_SHIPINSTRUCT CHAR(25) NOT NULL, "
249 "L_SHIPMODE CHAR(10) NOT NULL, L_COMMENT VARCHAR(44) NOT NULL)"));
250
251 //! Checking if Optimizer push predicates down
252 auto tree = helper.ParseLogicalTree(
253 "select sum(l_extendedprice * l_discount) as revenue from lineitem where l_shipdate >= '1994-01-01' and "
254 "l_shipdate < '1995-01-01' and l_discount between 0.05 and 0.07 and l_quantity < 24 ");
255
256 //! The generated plan should be Projection ->Aggregate_and_group_by->Get (5)
257 auto plan = opt.Optimize(move(tree));
258 REQUIRE(plan->type == LogicalOperatorType::PROJECTION);
259 REQUIRE(plan->children[0]->type == LogicalOperatorType::AGGREGATE_AND_GROUP_BY);
260 REQUIRE(plan->children[0]->children[0]->type == LogicalOperatorType::GET);
261 REQUIRE(plan->children[0]->children[0]->expressions.size() == 5);
262}
263
264TEST_CASE("Test Table Filter Push Down Scan String", "[filterpushdown-optimizer][.]") {
265 unique_ptr<QueryResult> result;
266 DuckDB db(nullptr);
267 Connection con(db);
268
269 vector<string> input{"pedro", "peter", "mark"};
270 idx_t input_size = 100000;
271 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(i varchar)"));
272 for (auto &value : input) {
273 for (size_t i = 0; i < input_size; i++) {
274 con.Query("INSERT INTO strings VALUES('" + value + "')");
275 }
276 }
277 result = con.Query("SELECT count(i) FROM strings where i = 'pedro' ");
278 REQUIRE(CHECK_COLUMN(result, 0, {100000}));
279 con.Query("INSERT INTO strings VALUES('po')");
280 con.Query("INSERT INTO strings VALUES('stefan manegold')");
281 con.Query("INSERT INTO strings VALUES('tim k')");
282 con.Query("INSERT INTO strings VALUES('tim k')");
283 con.Query("update strings set i = 'zorro' where i = 'pedro'");
284 result = con.Query("SELECT count(i) FROM strings where i >= 'tim k' ");
285 REQUIRE(CHECK_COLUMN(result, 0, {100002}));
286}
287