1#include "catch.hpp"
2#include "duckdb/common/file_system.hpp"
3#include "dbgen.hpp"
4#include "test_helpers.hpp"
5
6using namespace duckdb;
7using namespace std;
8
9TEST_CASE("Test subqueries in update", "[subquery]") {
10 unique_ptr<QueryResult> result;
11 DuckDB db(nullptr);
12 Connection con(db);
13
14 con.EnableQueryVerification();
15 con.EnableProfiling();
16
17 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(id INTEGER, i INTEGER)"));
18 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1, 1), (2, 2), (3, 3), (4, NULL)"));
19
20 // correlated subquery in update
21 REQUIRE_NO_FAIL(con.Query("UPDATE integers i1 SET i=(SELECT MAX(i) FROM integers WHERE i1.i<>i)"));
22
23 result = con.Query("SELECT id, i FROM integers ORDER BY id");
24 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 4}));
25 REQUIRE(CHECK_COLUMN(result, 1, {3, 3, 2, Value()}));
26
27 // uncorrelated subquery in update
28 result = con.Query("UPDATE integers i1 SET i=(SELECT MAX(i) FROM integers) WHERE i=(SELECT MIN(i) FROM integers)");
29 REQUIRE(CHECK_COLUMN(result, 0, {1}));
30
31 result = con.Query("SELECT id, i FROM integers ORDER BY id");
32 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 4}));
33 REQUIRE(CHECK_COLUMN(result, 1, {3, 3, 3, Value()}));
34
35 // use different correlated column in subquery
36 REQUIRE_NO_FAIL(con.Query("UPDATE integers i1 SET i=(SELECT MAX(id) FROM integers WHERE id<i1.id)"));
37
38 result = con.Query("SELECT id, i FROM integers ORDER BY id");
39 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 4}));
40 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2, 3}));
41
42 // correlated subquery in WHERE
43 result = con.Query("UPDATE integers i1 SET i=2 WHERE i<(SELECT MAX(id) FROM integers WHERE i1.id<id);");
44 REQUIRE(CHECK_COLUMN(result, 0, {2}));
45
46 result = con.Query("SELECT id, i FROM integers ORDER BY id");
47 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 4}));
48 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 2, 2, 3}));
49
50 // use DEFAULT with correlated subquery in WHERE
51 result = con.Query("UPDATE integers i1 SET i=DEFAULT WHERE i=(SELECT MIN(i) FROM integers WHERE i1.id<id);");
52 REQUIRE(CHECK_COLUMN(result, 0, {1}));
53
54 result = con.Query("SELECT id, i FROM integers ORDER BY id");
55 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 4}));
56 REQUIRE(CHECK_COLUMN(result, 1, {Value(), Value(), 2, 3}));
57}
58
59TEST_CASE("Test subqueries in delete", "[subquery]") {
60 unique_ptr<QueryResult> result;
61 DuckDB db(nullptr);
62 Connection con(db);
63
64 con.EnableQueryVerification();
65 con.EnableProfiling();
66
67 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(id INTEGER, i INTEGER)"));
68 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1, 1), (2, 2), (3, 3), (4, NULL)"));
69
70 // correlated subquery in delete
71 result = con.Query("DELETE FROM integers i1 WHERE i>(SELECT MAX(i) FROM integers WHERE i1.i<>i)");
72 REQUIRE(CHECK_COLUMN(result, 0, {1}));
73
74 result = con.Query("SELECT id, i FROM integers ORDER BY id");
75 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 4}));
76 REQUIRE(CHECK_COLUMN(result, 1, {1, 2, Value()}));
77
78 // uncorrelated subquery in delete
79 result = con.Query("DELETE FROM integers i1 WHERE i=(SELECT MAX(i) FROM integers)");
80 REQUIRE(CHECK_COLUMN(result, 0, {1}));
81
82 result = con.Query("SELECT id, i FROM integers ORDER BY id");
83 REQUIRE(CHECK_COLUMN(result, 0, {1, 4}));
84 REQUIRE(CHECK_COLUMN(result, 1, {1, Value()}));
85}
86