1#include "catch.hpp"
2#include "duckdb/common/file_system.hpp"
3#include "duckdb/common/types/date.hpp"
4#include "test_helpers.hpp"
5
6using namespace duckdb;
7using namespace std;
8
9TEST_CASE("Basic prepared statements", "[prepared]") {
10 unique_ptr<QueryResult> result;
11 DuckDB db(nullptr);
12 Connection con(db);
13
14 REQUIRE_NO_FAIL(con.Query("PREPARE s1 AS SELECT CAST($1 AS INTEGER), CAST($2 AS STRING)"));
15 result = con.Query("EXECUTE s1(42, 'dpfkg')");
16
17 REQUIRE(CHECK_COLUMN(result, 0, {42}));
18 REQUIRE(CHECK_COLUMN(result, 1, {"dpfkg"}));
19
20 result = con.Query("EXECUTE s1(43, 'asdf')");
21 REQUIRE(CHECK_COLUMN(result, 0, {43}));
22 REQUIRE(CHECK_COLUMN(result, 1, {"asdf"}));
23
24 // not enough params
25 REQUIRE_FAIL(con.Query("EXECUTE s1(43)"));
26 // too many
27 REQUIRE_FAIL(con.Query("EXECUTE s1(43, 'asdf', 42)"));
28 // wrong non-castable types
29 REQUIRE_FAIL(con.Query("EXECUTE s1('asdf', 'asdf')"));
30
31 REQUIRE_NO_FAIL(con.Query("DEALLOCATE s1"));
32
33 // we can deallocate non-existing statements
34 REQUIRE_NO_FAIL(con.Query("DEALLOCATE s2"));
35
36 // now its gone
37 REQUIRE_FAIL(con.Query("EXECUTE s1(42, 'dpfkg')"));
38
39 // prepare a statement that cannot be prepared
40 REQUIRE_FAIL(con.Query("PREPARE EXPLAIN SELECT 42"));
41
42 REQUIRE_FAIL(con.Query("PREPARE CREATE TABLE a(i INTEGER)"));
43 REQUIRE_FAIL(con.Query("SELECT * FROM a;"));
44
45 // type will be resolved to "double"
46 REQUIRE_NO_FAIL(con.Query("PREPARE s1 AS SELECT $1+$2"));
47 REQUIRE_NO_FAIL(con.Query("DEALLOCATE s1"));
48
49 // but this works
50 REQUIRE_NO_FAIL(con.Query("PREPARE s1 AS SELECT NOT($1), 10+$2, $3+20, 4 IN (2, 3, $4), $5 IN (2, 3, 4)"));
51
52 result = con.Query("EXECUTE s1(1, 2, 3, 4, 2)");
53 REQUIRE(CHECK_COLUMN(result, 0, {false}));
54 REQUIRE(CHECK_COLUMN(result, 1, {12}));
55 REQUIRE(CHECK_COLUMN(result, 2, {23}));
56 REQUIRE(CHECK_COLUMN(result, 3, {true}));
57 REQUIRE(CHECK_COLUMN(result, 4, {true}));
58
59 // cannot resolve these types
60 REQUIRE_FAIL(con.Query("PREPARE s1 AS SELECT $1"));
61 REQUIRE_FAIL(con.Query("PREPARE s1 AS SELECT (SELECT $1)"));
62 REQUIRE_FAIL(con.Query("PREPARE s1 AS SELECT $1=$2"));
63}
64
65TEST_CASE("Prepared statements and subqueries", "[prepared]") {
66 unique_ptr<QueryResult> result;
67 DuckDB db(nullptr);
68 Connection con(db);
69
70 // simple subquery
71 REQUIRE_NO_FAIL(con.Query("PREPARE v1 AS SELECT * FROM (SELECT $1::INTEGER) sq1;"));
72
73 result = con.Query("EXECUTE v1(42)");
74 REQUIRE(CHECK_COLUMN(result, 0, {42}));
75
76 // subquery with non-fulfillable predicate
77 REQUIRE_NO_FAIL(con.Query("PREPARE v2 AS SELECT * FROM (SELECT $1::INTEGER WHERE 1=0) sq1;"));
78
79 result = con.Query("EXECUTE v2(42)");
80 REQUIRE(CHECK_COLUMN(result, 0, {}));
81
82 // prepared statement in correlated scalar subquery
83 REQUIRE_NO_FAIL(con.Query("PREPARE v3 AS SELECT (SELECT $1::INT+sq1.i) FROM (SELECT 42 AS i) sq1;"));
84
85 result = con.Query("EXECUTE v3(42)");
86 REQUIRE(CHECK_COLUMN(result, 0, {84}));
87
88 // prepared statement in nested correlated scalar subquery
89 REQUIRE_NO_FAIL(
90 con.Query("PREPARE v4 AS SELECT (SELECT (SELECT $1::INT+sq1.i)+$2::INT+sq1.i) FROM (SELECT 42 AS i) sq1;"));
91
92 result = con.Query("EXECUTE v4(20, 20)");
93 REQUIRE(CHECK_COLUMN(result, 0, {124}));
94}
95
96TEST_CASE("PREPARE for SELECT clause", "[prepared]") {
97 unique_ptr<QueryResult> result;
98 DuckDB db(nullptr);
99 Connection con(db);
100
101 REQUIRE_NO_FAIL(con.Query("CREATE TABLE a (i TINYINT)"));
102 REQUIRE_NO_FAIL(con.Query("INSERT INTO a VALUES (42)"));
103 REQUIRE_NO_FAIL(con.Query("PREPARE s3 AS SELECT * FROM a WHERE i=$1"));
104
105 REQUIRE_FAIL(con.Query("EXECUTE s3(10000)"));
106
107 result = con.Query("EXECUTE s3(42)");
108 REQUIRE(result->success);
109 REQUIRE(CHECK_COLUMN(result, 0, {42}));
110
111 result = con.Query("EXECUTE s3(84)");
112 REQUIRE(result->success);
113 REQUIRE(CHECK_COLUMN(result, 0, {}));
114
115 REQUIRE_NO_FAIL(con.Query("DEALLOCATE s3"));
116
117 // can't run a query with a param without PREPARE
118 REQUIRE_FAIL(con.Query("SELECT * FROM a WHERE i=$1"));
119 // also can't run a query with a param when casting
120 REQUIRE_FAIL(con.Query("SELECT * FROM a WHERE i=CAST($1 AS VARCHAR)"));
121}
122
123TEST_CASE("PREPARE for INSERT", "[prepared]") {
124 unique_ptr<QueryResult> result;
125 DuckDB db(nullptr);
126 Connection con(db);
127
128 REQUIRE_NO_FAIL(con.Query("CREATE TABLE b (i TINYINT)"));
129 REQUIRE_NO_FAIL(con.Query("PREPARE s1 AS INSERT INTO b VALUES (cast($1 as tinyint)), ($2 + 1), ($3)"));
130 REQUIRE_NO_FAIL(con.Query("EXECUTE s1 (42, 41, 42)"));
131
132 result = con.Query("SELECT * FROM b");
133 REQUIRE(CHECK_COLUMN(result, 0, {42, 42, 42}));
134 REQUIRE_FAIL(con.Query("EXECUTE s1 (42, 41, 10000)"));
135
136 REQUIRE_NO_FAIL(con.Query("CREATE TABLE c (i INTEGER)"));
137 REQUIRE_NO_FAIL(con.Query("PREPARE s2 AS INSERT INTO c VALUES ($1)"));
138
139 for (size_t i = 0; i < 1000; i++) {
140 REQUIRE_NO_FAIL(con.Query("EXECUTE s2(" + to_string(i) + ")"));
141 }
142
143 result = con.Query("SELECT COUNT(*), MIN(i), MAX(i) FROM c");
144 REQUIRE(CHECK_COLUMN(result, 0, {1000}));
145 REQUIRE(CHECK_COLUMN(result, 1, {0}));
146 REQUIRE(CHECK_COLUMN(result, 2, {999}));
147
148 // can't drop table because we still have a prepared statement on it
149 REQUIRE_FAIL(con.Query("DROP TABLE b"));
150 REQUIRE_FAIL(con.Query("DROP TABLE c"));
151
152 REQUIRE_NO_FAIL(con.Query("DEALLOCATE s2"));
153 REQUIRE_NO_FAIL(con.Query("DEALLOCATE s1"));
154
155 // now we can
156 REQUIRE_NO_FAIL(con.Query("DROP TABLE b"));
157 REQUIRE_NO_FAIL(con.Query("DROP TABLE c"));
158}
159
160TEST_CASE("PREPARE for INSERT with dates", "[prepared]") {
161 unique_ptr<QueryResult> result;
162 DuckDB db(nullptr);
163 Connection con(db);
164
165 // prepared DATE insert
166 REQUIRE_NO_FAIL(con.Query("CREATE TABLE dates(d DATE)"));
167 REQUIRE_NO_FAIL(con.Query("PREPARE s1 AS INSERT INTO dates VALUES ($1)"));
168 REQUIRE_NO_FAIL(con.Query("EXECUTE s1 (DATE '1992-01-01')"));
169
170 result = con.Query("SELECT * FROM dates");
171 REQUIRE(CHECK_COLUMN(result, 0, {Value::DATE(1992, 1, 1)}));
172
173 REQUIRE_NO_FAIL(con.Query("DELETE FROM dates"));
174
175 auto prepared = con.Prepare("INSERT INTO dates VALUES ($1)");
176 REQUIRE_NO_FAIL(prepared->Execute(Value::DATE(1992, 1, 3)));
177
178 result = con.Query("SELECT * FROM dates");
179 REQUIRE(CHECK_COLUMN(result, 0, {Value::DATE(1992, 1, 3)}));
180}
181
182TEST_CASE("PREPARE for DELETE/UPDATE", "[prepared]") {
183 unique_ptr<QueryResult> result;
184 DuckDB db(nullptr);
185 Connection con(db);
186
187 // DELETE
188 REQUIRE_NO_FAIL(con.Query("CREATE TABLE b (i TINYINT)"));
189 REQUIRE_NO_FAIL(con.Query("INSERT INTO b VALUES (1), (2), (3), (4), (5)"));
190 REQUIRE_NO_FAIL(con.Query("PREPARE s1 AS DELETE FROM b WHERE i=$1"));
191
192 result = con.Query("SELECT * FROM b ORDER BY 1");
193 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 4, 5}));
194 REQUIRE_NO_FAIL(con.Query("EXECUTE s1(3)"));
195
196 result = con.Query("SELECT * FROM b ORDER BY 1");
197 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 4, 5}));
198
199 // cannot drop table now
200 REQUIRE_FAIL(con.Query("DROP TABLE b"));
201 // but we can with cascade
202 REQUIRE_NO_FAIL(con.Query("DROP TABLE b CASCADE"));
203
204 // UPDATE
205 REQUIRE_NO_FAIL(con.Query("CREATE TABLE b (i TINYINT)"));
206 REQUIRE_NO_FAIL(con.Query("INSERT INTO b VALUES (1), (2), (3), (4), (5)"));
207 REQUIRE_NO_FAIL(con.Query("PREPARE s1 AS UPDATE b SET i=$1 WHERE i=$2"));
208
209 result = con.Query("SELECT * FROM b ORDER BY 1");
210 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 4, 5}));
211 REQUIRE_NO_FAIL(con.Query("EXECUTE s1(6, 3)"));
212
213 result = con.Query("SELECT * FROM b ORDER BY 1");
214 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 4, 5, 6}));
215
216 // cannot drop table now
217 REQUIRE_FAIL(con.Query("DROP TABLE b"));
218 // but we can with cascade
219 REQUIRE_NO_FAIL(con.Query("DROP TABLE b CASCADE"));
220}
221
222TEST_CASE("PREPARE for UPDATE", "[prepared]") {
223 unique_ptr<QueryResult> result;
224 DuckDB db(nullptr);
225 Connection con(db);
226
227 REQUIRE_NO_FAIL(con.Query("CREATE TABLE b (i TINYINT)"));
228 REQUIRE_NO_FAIL(con.Query("INSERT INTO b VALUES (1), (2), (3), (4), (5)"));
229 REQUIRE_NO_FAIL(con.Query("PREPARE s1 AS DELETE FROM b WHERE i=$1"));
230
231 result = con.Query("SELECT * FROM b ORDER BY 1");
232 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 4, 5}));
233 REQUIRE_NO_FAIL(con.Query("EXECUTE s1(3)"));
234
235 result = con.Query("SELECT * FROM b ORDER BY 1");
236 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 4, 5}));
237
238 // cannot drop table now
239 REQUIRE_FAIL(con.Query("DROP TABLE b"));
240 // but we can with cascade
241 REQUIRE_NO_FAIL(con.Query("DROP TABLE b CASCADE"));
242}
243
244TEST_CASE("PREPARE many types for INSERT", "[prepared]") {
245 unique_ptr<QueryResult> result;
246 DuckDB db(nullptr);
247 Connection con(db);
248
249 // prepare different types in insert
250 REQUIRE_NO_FAIL(con.Query(
251 "CREATE TABLE test(a TINYINT, b SMALLINT, c INTEGER, d BIGINT, e REAL, f DOUBLE, g DATE, h VARCHAR)"));
252 REQUIRE_NO_FAIL(con.Query("PREPARE s1 AS INSERT INTO test VALUES ($1,$2,$3,$4,$5,$6,$7,$8);"));
253 REQUIRE_NO_FAIL(con.Query("EXECUTE s1(1,2,3,4,1.5,2.5,'1992-10-20', 'hello world');"));
254 result = con.Query("SELECT * FROM test");
255 REQUIRE(CHECK_COLUMN(result, 0, {1}));
256 REQUIRE(CHECK_COLUMN(result, 1, {2}));
257 REQUIRE(CHECK_COLUMN(result, 2, {3}));
258 REQUIRE(CHECK_COLUMN(result, 3, {4}));
259 REQUIRE(CHECK_COLUMN(result, 4, {(float)1.5}));
260 REQUIRE(CHECK_COLUMN(result, 5, {2.5}));
261 REQUIRE(CHECK_COLUMN(result, 6, {Value::DATE(1992, 10, 20)}));
262 REQUIRE(CHECK_COLUMN(result, 7, {"hello world"}));
263}
264
265TEST_CASE("PREPARE and DROPping tables", "[prepared]") {
266 unique_ptr<QueryResult> result;
267 DuckDB db(nullptr);
268 Connection con1(db);
269 Connection con2(db);
270
271 REQUIRE_NO_FAIL(con1.Query("CREATE TABLE a (i TINYINT)"));
272 REQUIRE_NO_FAIL(con2.Query("PREPARE p1 AS SELECT * FROM a"));
273
274 REQUIRE_NO_FAIL(con2.Query("EXECUTE p1"));
275
276 // only the conn which did the prepare can execute
277 REQUIRE_FAIL(con1.Query("EXECUTE p1"));
278
279 // but someone else cannot drop the table
280 REQUIRE_FAIL(con1.Query("DROP TABLE a"));
281
282 // but when we take the statement away
283 REQUIRE_NO_FAIL(con2.Query("DEALLOCATE p1"));
284
285 // we can drop
286 REQUIRE_NO_FAIL(con1.Query("DROP TABLE a"));
287}
288
289TEST_CASE("PREPARE and WAL", "[prepared][.]") {
290 unique_ptr<QueryResult> result;
291 auto prepare_database = TestCreatePath("prepare_test");
292
293 // make sure the database does not exist
294 DeleteDatabase(prepare_database);
295 {
296 // create a database and insert values
297 DuckDB db(prepare_database);
298 Connection con(db);
299 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t (a INTEGER)"));
300 REQUIRE_NO_FAIL(con.Query("PREPARE p1 AS INSERT INTO t VALUES ($1)"));
301 REQUIRE_NO_FAIL(con.Query("EXECUTE p1(42)"));
302 REQUIRE_NO_FAIL(con.Query("EXECUTE p1(43)"));
303 REQUIRE_NO_FAIL(con.Query("DEALLOCATE p1"));
304 result = con.Query("SELECT a FROM t");
305 REQUIRE(CHECK_COLUMN(result, 0, {42, 43}));
306 }
307 {
308 DuckDB db(prepare_database);
309 Connection con(db);
310
311 result = con.Query("SELECT a FROM t");
312 REQUIRE(CHECK_COLUMN(result, 0, {42, 43}));
313
314 // unhelpfully use the same statement name again, it should be available, but do nothing with it
315 REQUIRE_NO_FAIL(con.Query("PREPARE p1 AS DELETE FROM t WHERE a=$1"));
316 }
317 // reload the database from disk
318 {
319 DuckDB db(prepare_database);
320 Connection con(db);
321 REQUIRE_NO_FAIL(con.Query("PREPARE p1 AS DELETE FROM t WHERE a=$1"));
322 REQUIRE_NO_FAIL(con.Query("EXECUTE p1(43)"));
323
324 result = con.Query("SELECT a FROM t");
325 REQUIRE(CHECK_COLUMN(result, 0, {42}));
326 }
327 // reload again
328
329 {
330 DuckDB db(prepare_database);
331 Connection con(db);
332
333 result = con.Query("SELECT a FROM t");
334 REQUIRE(CHECK_COLUMN(result, 0, {42}));
335 }
336
337 {
338 DuckDB db(prepare_database);
339 Connection con(db);
340
341 result = con.Query("SELECT a FROM t");
342 REQUIRE(CHECK_COLUMN(result, 0, {42}));
343
344 REQUIRE_NO_FAIL(con.Query("PREPARE p1 AS UPDATE t SET a = $1"));
345 REQUIRE_NO_FAIL(con.Query("EXECUTE p1(43)"));
346
347 result = con.Query("SELECT a FROM t");
348 REQUIRE(CHECK_COLUMN(result, 0, {43}));
349 }
350 for (idx_t i = 0; i < 2; i++) {
351 DuckDB db(prepare_database);
352 Connection con(db);
353
354 result = con.Query("SELECT a FROM t");
355 REQUIRE(CHECK_COLUMN(result, 0, {43}));
356 }
357 DeleteDatabase(prepare_database);
358}
359
360TEST_CASE("PREPARE with NULL", "[prepared]") {
361 unique_ptr<QueryResult> result;
362 DuckDB db(nullptr);
363 Connection con(db);
364
365 REQUIRE_NO_FAIL(con.Query("CREATE TABLE b (i TINYINT)"));
366 REQUIRE_NO_FAIL(con.Query("PREPARE s1 AS INSERT INTO b VALUES ($1)"));
367 REQUIRE_NO_FAIL(con.Query("EXECUTE s1 (NULL)"));
368
369 result = con.Query("SELECT i FROM b");
370 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
371
372 REQUIRE_NO_FAIL(con.Query("PREPARE s2 AS UPDATE b SET i=$1"));
373 REQUIRE_NO_FAIL(con.Query("EXECUTE s2 (NULL)"));
374
375 result = con.Query("SELECT i FROM b");
376 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
377
378 REQUIRE_NO_FAIL(con.Query("PREPARE s3 AS DELETE FROM b WHERE i=$1"));
379 REQUIRE_NO_FAIL(con.Query("EXECUTE s3 (NULL)"));
380
381 result = con.Query("SELECT i FROM b");
382 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
383}
384
385TEST_CASE("PREPARE multiple statements", "[prepared]") {
386 unique_ptr<QueryResult> result;
387 DuckDB db(nullptr);
388 Connection con(db);
389
390 string query = "SELECT $1::INTEGER; SELECT $1::INTEGER;";
391 // cannot prepare multiple statements like this
392 auto prepared = con.Prepare(query);
393 REQUIRE(!prepared->success);
394 // we can use ExtractStatements to execute the individual statements though
395 auto statements = con.ExtractStatements(query);
396 for (auto &statement : statements) {
397 string stmt = query.substr(statement->stmt_location, statement->stmt_length);
398 prepared = con.Prepare(stmt);
399 REQUIRE(prepared->success);
400
401 result = prepared->Execute(1);
402 REQUIRE(CHECK_COLUMN(result, 0, {1}));
403 }
404}
405
406static unique_ptr<QueryResult> TestExecutePrepared(Connection &con, string query) {
407 auto prepared = con.Prepare(query);
408 return prepared->Execute();
409}
410
411TEST_CASE("Prepare all types of statements", "[prepared]") {
412 unique_ptr<QueryResult> result;
413 DuckDB db(nullptr);
414 Connection con(db);
415
416 string csv_path = TestCreatePath("prepared_files");
417 if (db.file_system->DirectoryExists(csv_path)) {
418 db.file_system->RemoveDirectory(csv_path);
419 }
420
421 // TRANSACTION
422 REQUIRE_NO_FAIL(TestExecutePrepared(con, "BEGIN TRANSACTION"));
423 // SELECT
424 result = TestExecutePrepared(con, "SELECT 42");
425 REQUIRE(CHECK_COLUMN(result, 0, {42}));
426 // CREATE_SCHEMA
427 REQUIRE_NO_FAIL(TestExecutePrepared(con, "CREATE SCHEMA test"));
428 // CREATE_TABLE
429 REQUIRE_NO_FAIL(TestExecutePrepared(con, "CREATE TABLE test.a(i INTEGER)"));
430 // CREATE_TABLE
431 REQUIRE_NO_FAIL(TestExecutePrepared(con, "CREATE TABLE b(i INTEGER)"));
432 // CREATE_INDEX
433 REQUIRE_NO_FAIL(TestExecutePrepared(con, "CREATE INDEX i_index ON test.a(i)"));
434 // CREATE_VIEW
435 REQUIRE_NO_FAIL(TestExecutePrepared(con, "CREATE VIEW v1 AS SELECT * FROM test.a WHERE i=2"));
436 // CREATE_SEQUENCE
437 REQUIRE_NO_FAIL(TestExecutePrepared(con, "CREATE SEQUENCE seq"));
438 // PRAGMA
439 REQUIRE_NO_FAIL(TestExecutePrepared(con, "PRAGMA table_info('b')"));
440 // EXPLAIN
441 REQUIRE_NO_FAIL(TestExecutePrepared(con, "EXPLAIN SELECT 42"));
442 // COPY
443 REQUIRE_NO_FAIL(TestExecutePrepared(con, "COPY test.a TO '" + csv_path + "'"));
444 // INSERT
445 REQUIRE_NO_FAIL(TestExecutePrepared(con, "INSERT INTO test.a VALUES (1), (2), (3)"));
446 // UPDATE
447 REQUIRE_NO_FAIL(TestExecutePrepared(con, "UPDATE test.a SET i=i+1"));
448 // DELETE
449 REQUIRE_NO_FAIL(TestExecutePrepared(con, "DELETE FROM test.a WHERE i<4"));
450 // PREPARE
451 REQUIRE_NO_FAIL(TestExecutePrepared(con, "PREPARE p1 AS SELECT * FROM test.a"));
452 // EXECUTE
453 result = TestExecutePrepared(con, "EXECUTE p1");
454 REQUIRE(CHECK_COLUMN(result, 0, {4}));
455 // DROP
456 REQUIRE_NO_FAIL(TestExecutePrepared(con, "DROP SEQUENCE seq"));
457 REQUIRE_NO_FAIL(TestExecutePrepared(con, "DROP VIEW v1"));
458 REQUIRE_NO_FAIL(TestExecutePrepared(con, "DROP TABLE test.a CASCADE"));
459 REQUIRE_NO_FAIL(TestExecutePrepared(con, "DROP SCHEMA test CASCADE"));
460
461 // TRANSACTION
462 REQUIRE_NO_FAIL(TestExecutePrepared(con, "COMMIT"));
463}
464