1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test insert into and updates of constant values", "[simpleinserts]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11
12 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
13 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3), (4), (5)"));
14
15 // insert a constant 1 for every uneven value in "integers"
16 REQUIRE_NO_FAIL(con.Query("CREATE TABLE i2 AS SELECT 1 AS i FROM integers WHERE i % 2 <> 0"));
17
18 result = con.Query("SELECT * FROM i2 ORDER BY 1");
19 REQUIRE(CHECK_COLUMN(result, 0, {1, 1, 1}));
20
21 // now update the table with a constant
22 REQUIRE_NO_FAIL(con.Query("UPDATE i2 SET i=NULL"));
23
24 result = con.Query("SELECT * FROM i2 ORDER BY 1");
25 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value()}));
26}
27
28TEST_CASE("Test insert into statements", "[simpleinserts]") {
29 unique_ptr<QueryResult> result;
30 DuckDB db(nullptr);
31 Connection con(db);
32
33 // big insert
34 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
35 result = con.Query("INSERT INTO integers VALUES (0), (1), (2), (3), (4), (5), (6), (7), "
36 "(8), (9), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (0), (1), "
37 "(2), (3), (4), (5), (6), (7), (8), (9), (0), (1), (2), (3), (4), (5), "
38 "(6), (7), (8), (9), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), "
39 "(0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (0), (1), (2), (3), "
40 "(4), (5), (6), (7), (8), (9), (0), (1), (2), (3), (4), (5), (6), (7), "
41 "(8), (9), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (0), (1), "
42 "(2), (3), (4), (5), (6), (7), (8), (9), (0), (1), (2), (3), (4), (5), "
43 "(6), (7), (8), (9), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), "
44 "(0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (0), (1), (2), (3), "
45 "(4), (5), (6), (7), (8), (9), (0), (1), (2), (3), (4), (5), (6), (7), "
46 "(8), (9), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (0), (1), "
47 "(2), (3), (4), (5), (6), (7), (8), (9), (0), (1), (2), (3), (4), (5), "
48 "(6), (7), (8), (9), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), "
49 "(0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (0), (1), (2), (3), "
50 "(4), (5), (6), (7), (8), (9), (0), (1), (2), (3), (4), (5), (6), (7), "
51 "(8), (9), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (0), (1), "
52 "(2), (3), (4), (5), (6), (7), (8), (9), (0), (1), (2), (3), (4), (5), "
53 "(6), (7), (8), (9), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), "
54 "(0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (0), (1), (2), (3), "
55 "(4), (5), (6), (7), (8), (9), (0), (1), (2), (3), (4), (5), (6), (7), "
56 "(8), (9), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (0), (1), "
57 "(2), (3), (4), (5), (6), (7), (8), (9), (0), (1), (2), (3), (4), (5), "
58 "(6), (7), (8), (9), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), "
59 "(0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (0), (1), (2), (3), "
60 "(4), (5), (6), (7), (8), (9), (0), (1), (2), (3), (4), (5), (6), (7), "
61 "(8), (9), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (0), (1), "
62 "(2), (3), (4), (5), (6), (7), (8), (9), (0), (1), (2), (3), (4), (5), "
63 "(6), (7), (8), (9), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), "
64 "(0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (0), (1), (2), (3), "
65 "(4), (5), (6), (7), (8), (9), (0), (1), (2), (3), (4), (5), (6), (7), "
66 "(8), (9), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (0), (1), "
67 "(2), (3), (4), (5), (6), (7), (8), (9), (0), (1), (2), (3), (4), (5), "
68 "(6), (7), (8), (9), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), "
69 "(0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (0), (1), (2), (3), "
70 "(4), (5), (6), (7), (8), (9), (0), (1), (2), (3), (4), (5), (6), (7), "
71 "(8), (9), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (0), (1), "
72 "(2), (3), (4), (5), (6), (7), (8), (9), (0), (1), (2), (3), (4), (5), "
73 "(6), (7), (8), (9), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), "
74 "(0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (0), (1), (2), (3), "
75 "(4), (5), (6), (7), (8), (9), (0), (1), (2), (3), (4), (5), (6), (7), "
76 "(8), (9), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (0), (1), "
77 "(2), (3), (4), (5), (6), (7), (8), (9), (0), (1), (2), (3), (4), (5), "
78 "(6), (7), (8), (9), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), "
79 "(0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (0), (1), (2), (3), "
80 "(4), (5), (6), (7), (8), (9), (0), (1), (2), (3), (4), (5), (6), (7), "
81 "(8), (9), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (0), (1), "
82 "(2), (3), (4), (5), (6), (7), (8), (9), (0), (1), (2), (3), (4), (5), "
83 "(6), (7), (8), (9), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), "
84 "(0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (0), (1), (2), (3), "
85 "(4), (5), (6), (7), (8), (9), (0), (1), (2), (3), (4), (5), (6), (7), "
86 "(8), (9), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (0), (1), "
87 "(2), (3), (4), (5), (6), (7), (8), (9), (0), (1), (2), (3), (4), (5), "
88 "(6), (7), (8), (9), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), "
89 "(0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (0), (1), (2), (3), "
90 "(4), (5), (6), (7), (8), (9), (0), (1), (2), (3), (4), (5), (6), (7), "
91 "(8), (9), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (0), (1), "
92 "(2), (3), (4), (5), (6), (7), (8), (9), (0), (1), (2), (3), (4), (5), "
93 "(6), (7), (8), (9), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), "
94 "(0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (0), (1), (2), (3), "
95 "(4), (5), (6), (7), (8), (9), (0), (1), (2), (3), (4), (5), (6), (7), "
96 "(8), (9), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (0), (1), "
97 "(2), (3), (4), (5), (6), (7), (8), (9), (0), (1), (2), (3), (4), (5), "
98 "(6), (7), (8), (9), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), "
99 "(0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (0), (1), (2), (3), "
100 "(4), (5), (6), (7), (8), (9), (0), (1), (2), (3), (4), (5), (6), (7), "
101 "(8), (9), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (0), (1), "
102 "(2), (3), (4), (5), (6), (7), (8), (9), (0), (1), (2), (3), (4), (5), "
103 "(6), (7), (8), (9), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), "
104 "(0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (0), (1), (2), (3), "
105 "(4), (5), (6), (7), (8), (9), (0), (1), (2), (3), (4), (5), (6), (7), "
106 "(8), (9), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (0), (1), "
107 "(2), (3), (4), (5), (6), (7), (8), (9), (0), (1), (2), (3), (4), (5), "
108 "(6), (7), (8), (9), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), "
109 "(0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (0), (1), (2), (3), "
110 "(4), (5), (6), (7), (8), (9)");
111 REQUIRE(CHECK_COLUMN(result, 0, {1050}));
112
113 result = con.Query("SELECT COUNT(*) FROM integers");
114 REQUIRE(CHECK_COLUMN(result, 0, {1050}));
115
116 // insert into from SELECT
117 result = con.Query("INSERT INTO integers SELECT * FROM integers;");
118 REQUIRE(CHECK_COLUMN(result, 0, {1050}));
119
120 result = con.Query("SELECT COUNT(*) FROM integers");
121 REQUIRE(CHECK_COLUMN(result, 0, {2100}));
122
123 REQUIRE_NO_FAIL(con.Query("DROP TABLE integers;"));
124
125 // insert into from query with column predicates
126 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER, j INTEGER);"));
127
128 result = con.Query("INSERT INTO integers VALUES (3, 4), (4, 3);");
129 REQUIRE(CHECK_COLUMN(result, 0, {2}));
130 // insert into with default
131 result = con.Query("INSERT INTO integers VALUES (DEFAULT, 4);");
132 REQUIRE(CHECK_COLUMN(result, 0, {1}));
133 // operations on default not supported
134 REQUIRE_FAIL(con.Query("INSERT INTO integers VALUES (DEFAULT+1, 4);"));
135
136 result = con.Query("INSERT INTO integers (i) SELECT j FROM integers;");
137 REQUIRE(CHECK_COLUMN(result, 0, {3}));
138
139 result = con.Query("SELECT * FROM integers");
140 REQUIRE(CHECK_COLUMN(result, 0, {3, 4, Value(), 4, 3, 4}));
141 REQUIRE(CHECK_COLUMN(result, 1, {4, 3, 4, Value(), Value(), Value()}));
142}
143
144TEST_CASE("Test insert into from wrong type", "[simpleinserts]") {
145 unique_ptr<QueryResult> result;
146 DuckDB db(nullptr);
147 Connection con(db);
148
149 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(a VARCHAR)"));
150 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
151
152 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (3), (4), (NULL)"));
153 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings SELECT * FROM integers"));
154
155 result = con.Query("SELECT * FROM strings");
156 REQUIRE(CHECK_COLUMN(result, 0, {Value("3"), Value("4"), Value()}));
157
158 REQUIRE_NO_FAIL(con.Query("UPDATE strings SET a=13 WHERE a=3"));
159
160 result = con.Query("SELECT * FROM strings ORDER BY cast(a AS INTEGER)");
161 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value("4"), Value("13")}));
162}
163
164TEST_CASE("Test insert from constant query", "[simpleinserts]") {
165 unique_ptr<QueryResult> result;
166 DuckDB db(nullptr);
167 Connection con(db);
168
169 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
170 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers SELECT 42"));
171 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers SELECT CAST(NULL AS VARCHAR)"));
172
173 result = con.Query("SELECT * FROM integers");
174 REQUIRE(CHECK_COLUMN(result, 0, {42, Value()}));
175}
176
177TEST_CASE("Test insert with invalid UTF8", "[simpleinserts]") {
178 unique_ptr<QueryResult> result;
179 DuckDB db(nullptr);
180 Connection con(db);
181
182 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(i STRING)"));
183 REQUIRE_FAIL(con.Query("INSERT INTO strings VALUES ('\xe2\x82\x28')"));
184 REQUIRE_FAIL(con.Query("SELECT * FROM strings WHERE i = '\xe2\x82\x28'"));
185}
186
187TEST_CASE("Test insert with too few or too many cols", "[simpleinserts]") {
188 unique_ptr<QueryResult> result;
189 DuckDB db(nullptr);
190 Connection con(db);
191
192 REQUIRE_NO_FAIL(con.Query("CREATE TABLE a(i integer, j integer)"));
193 REQUIRE_NO_FAIL(con.Query("INSERT INTO a VALUES (1, 2)"));
194 // scalar inserts
195 REQUIRE_FAIL(con.Query("INSERT INTO a VALUES (1)"));
196 REQUIRE_FAIL(con.Query("INSERT INTO a VALUES (1,2,3)"));
197 REQUIRE_FAIL(con.Query("INSERT INTO a VALUES (1,2),(3)"));
198 REQUIRE_FAIL(con.Query("INSERT INTO a VALUES (1,2),(3,4,5)"));
199 // also with queries
200 REQUIRE_FAIL(con.Query("INSERT INTO a SELECT 42"));
201}
202
203TEST_CASE("Test insert with long string constant", "[simpleinserts]") {
204 unique_ptr<QueryResult> result;
205 DuckDB db(nullptr);
206 Connection con(db);
207
208 // found by Pedro Holanda
209 REQUIRE_NO_FAIL(con.Query("CREATE TABLE IF NOT EXISTS presentations(presentation_date Date NOT NULL UNIQUE, author VARCHAR NOT NULL, title VARCHAR NOT NULL, bio VARCHAR, abstract VARCHAR, zoom_link VARCHAR);"));
210 REQUIRE_NO_FAIL(con.Query("insert into presentations values ('2020-05-29', 'Eduardo Pena', 'Analytical Query Processing Based on Continuous Compression of Intermediates', NULL, 'Modern in-memory column-stores are widely accepted as the adequate database architecture for the efficient processing of complex analytical queries over large relational data volumes. These systems keep their entire data in main memory and typically employ lightweight compression to address the bottleneck between main memory and CPU. Numerous lightweight compression algorithms have been proposed in the past years, but none of them is suitable in all cases. While lightweight compression is already well established for base data, the efficient representation of intermediate results generated during query processing has attracted insufficient attention so far, although in in-memory systems, accessing intermeFdiates is as expensive as accessing base data. Thus, our vision is a continuous use of lightweight compression for all intermediates in a query execution plan, whereby a suitable compression algorithm should be selected for each intermediate. In this talk, I will provide an overview of our research in the context of this vision, including an experimental survey of lightweight compression algorithms, our compression-enabled processing model, and our compression-aware query optimization strategies.', 'https://zoom.us/j/7845983526');"));
211}
212