1#include "catch.hpp"
2#include "test_helpers.hpp"
3#include "duckdb/main/prepared_statement.hpp"
4
5#include <fstream>
6
7using namespace duckdb;
8using namespace std;
9
10TEST_CASE("BLOB null and empty values", "[blob]") {
11 unique_ptr<QueryResult> result;
12 DuckDB db(nullptr);
13 Connection con(db);
14 result = con.Query("SELECT ''::BLOB");
15 REQUIRE(CHECK_COLUMN(result, 0, {""}));
16
17 result = con.Query("SELECT NULL::BLOB");
18 REQUIRE(CHECK_COLUMN(result, 0, {Value(nullptr)}));
19
20 REQUIRE_NO_FAIL(con.Query("CREATE TABLE blobs (b BYTEA);"));
21 REQUIRE_NO_FAIL(con.Query("INSERT INTO blobs VALUES(''), (''::BLOB)"));
22 REQUIRE_NO_FAIL(con.Query("INSERT INTO blobs VALUES(NULL), (NULL::BLOB)"));
23
24 result = con.Query("SELECT * FROM blobs");
25 REQUIRE(CHECK_COLUMN(result, 0, {"", "", Value(nullptr), Value(nullptr)}));
26}
27
28TEST_CASE("Test BLOBs with persistent storage", "[blob]") {
29 auto config = GetTestConfig();
30 unique_ptr<QueryResult> result;
31 auto storage_database = TestCreatePath("blob_storage_test");
32
33 // make sure the database does not exist
34 DeleteDatabase(storage_database);
35 {
36 // create a database and insert values
37 DuckDB db(storage_database, config.get());
38 Connection con(db);
39 REQUIRE_NO_FAIL(con.Query("CREATE TABLE blobs (b BLOB);"));
40 REQUIRE_NO_FAIL(con.Query("INSERT INTO blobs VALUES('a'), ('\\xAA'), ('\\xAAFFAA'), (''),"
41 "(NULL), ('55AAFF55AAFF55AAFF01'), ('\\x55AAFF55AAFF55AAFF01'),"
42 "('abc \153\154\155 \052\251\124'::BLOB)"));
43 }
44 // reload the database from disk a few times
45 for (idx_t i = 0; i < 2; i++) {
46 DuckDB db(storage_database, config.get());
47 Connection con(db);
48 result = con.Query("SELECT * FROM blobs");
49 REQUIRE(CHECK_COLUMN(result, 0, {"a", Value::BLOB("\\xAA"), Value::BLOB("\\xAAFFAA"), (""),
50 Value(nullptr), ("55AAFF55AAFF55AAFF01"), Value::BLOB("\\x55AAFF55AAFF55AAFF01"),
51 Value::BLOB("abc \153\154\155 \052\251\124") }));
52 }
53 DeleteDatabase(storage_database);
54}
55
56TEST_CASE("Cast BLOB values", "[blob]") {
57 unique_ptr<QueryResult> result;
58 DuckDB db(nullptr);
59 Connection con(db);
60
61 // BLOB to VARCHAR -> CastFromBlob, it always results in a hex representation
62 result = con.Query("SELECT 'a'::BYTEA::VARCHAR");
63 REQUIRE(CHECK_COLUMN(result, 0, {Value("\\x61")}));
64
65 // VARCHAR to BLOB -> CastToBlob
66 result = con.Query("SELECT 'a'::VARCHAR::BYTEA");
67 REQUIRE(CHECK_COLUMN(result, 0, {"a"}));
68
69 // Hex string with BLOB
70 result = con.Query("SELECT '\\xAAFFAAAAFFAAAAFFAA'::BYTEA");
71 REQUIRE(CHECK_COLUMN(result, 0, {Value::BLOB("\\xAAFFAAAAFFAAAAFFAA")}));
72
73 // CastFromBlob with hex string
74 result = con.Query("SELECT '\\xAAFFAAAAFFAAAAFFAA'::BLOB::VARCHAR");
75 REQUIRE(CHECK_COLUMN(result, 0, {Value("\\xAAFFAAAAFFAAAAFFAA")}));
76
77 // CastFromBlob and after CastToBlob with hex string
78 result = con.Query("SELECT '\\xAAFFAAAAFFAAAAFFAA'::BLOB::VARCHAR::BLOB");
79 REQUIRE(CHECK_COLUMN(result, 0, {Value::BLOB("\\xAAFFAAAAFFAAAAFFAA")}));
80
81 // CastFromBlob -> CastToBlob -> CastFromBlob with hex string
82 result = con.Query("SELECT '\\xAAFFAAAAFFAAAAFFAA'::BLOB::VARCHAR::BLOB::VARCHAR");
83 REQUIRE(CHECK_COLUMN(result, 0, {Value("\\xAAFFAAAAFFAAAAFFAA")}));
84
85 // CastToBlob -> CastFromBlob -> CastToBlob with hex string
86 result = con.Query("SELECT '\\xAAFFAAAAFFAAAAFFAA'::VARCHAR::BLOB::VARCHAR::BLOB");
87 REQUIRE(CHECK_COLUMN(result, 0, {Value::BLOB("\\xAAFFAAAAFFAAAAFFAA")}));
88
89 REQUIRE_FAIL(con.Query("SELECT 1::BYTEA"));
90 REQUIRE_FAIL(con.Query("SELECT 1.0::BYTEA"));
91
92 // numeric -> bytea, not valid/implemented casts
93 vector<string> types = {"tinyint", "smallint", "integer", "bigint", "decimal"};
94 for (auto &type : types) {
95 REQUIRE_FAIL(con.Query("SELECT 1::"+ type + "::BYTEA"));
96 }
97}
98
99TEST_CASE("Insert BLOB values from normal strings", "[blob]") {
100 unique_ptr<QueryResult> result;
101 DuckDB db(nullptr);
102 Connection con(db);
103
104 REQUIRE_NO_FAIL(con.Query("CREATE TABLE blobs (b BYTEA);"));
105 // insert BLOB from string
106 REQUIRE_NO_FAIL(con.Query("INSERT INTO blobs VALUES ('aaaaaaaaaa')"));
107 // sizes: 10, 100, 1000, 10000 -> double plus two due to hexadecimal representation
108 for (idx_t i = 0; i < 3; i++) {
109 // The concat function casts BLOB to VARCHAR,resulting in a hex string
110 REQUIRE_NO_FAIL(con.Query("INSERT INTO blobs SELECT b||b||b||b||b||b||b||b||b||b FROM blobs "
111 "WHERE OCTET_LENGTH(b)=(SELECT MAX(OCTET_LENGTH(b)) FROM blobs)"));
112 }
113
114 result = con.Query("SELECT OCTET_LENGTH(b) FROM blobs ORDER BY 1");
115 REQUIRE(CHECK_COLUMN(result, 0, {10, 100, 1000, 10000}));
116}
117
118TEST_CASE("Insert BLOB values from hex strings and others", "[blob]") {
119 unique_ptr<QueryResult> result;
120 DuckDB db(nullptr);
121 Connection con(db);
122
123 REQUIRE_NO_FAIL(con.Query("CREATE TABLE blobs (b BYTEA);"));
124
125 // Insert valid hex strings
126 REQUIRE_NO_FAIL(con.Query("INSERT INTO blobs VALUES('\\xAAFFAA'), ('\\xAAFFAAAAFFAA'), ('\\xAAFFAAAAFFAAAAFFAA')"));
127 result = con.Query("SELECT * FROM blobs");
128 REQUIRE(CHECK_COLUMN(result, 0, {Value::BLOB("\\xAAFFAA"), Value::BLOB("\\xAAFFAAAAFFAA"), Value::BLOB("\\xAAFFAAAAFFAAAAFFAA")}));
129
130 // Insert valid hex strings, lower case
131 REQUIRE_NO_FAIL(con.Query("DELETE FROM blobs"));
132 REQUIRE_NO_FAIL(con.Query("INSERT INTO blobs VALUES('\\xaaffaa'), ('\\xaaffaaaaffaa'), ('\\xaaffaaaaffaaaaffaa')"));
133 result = con.Query("SELECT * FROM blobs");
134 REQUIRE(CHECK_COLUMN(result, 0, {Value::BLOB("\\xaaffaa"), Value::BLOB("\\xaaffaaaaffaa"), Value::BLOB("\\xaaffaaaaffaaaaffaa")}));
135
136 // Insert valid hex strings with number and letters
137 REQUIRE_NO_FAIL(con.Query("DELETE FROM blobs"));
138 REQUIRE_NO_FAIL(con.Query("INSERT INTO blobs VALUES('\\xaa1199'), ('\\xaa1199aa1199'), ('\\xaa1199aa1199aa1199')"));
139 result = con.Query("SELECT * FROM blobs");
140 REQUIRE(CHECK_COLUMN(result, 0, {Value::BLOB("\\xaa1199"), Value::BLOB("\\xaa1199aa1199"), Value::BLOB("\\xaa1199aa1199aa1199")}));
141
142 // Insert INvalid hex strings (invalid hex chars: G, H, I)
143 REQUIRE_FAIL(con.Query("INSERT INTO blobs VALUES('\\xGAFFAA'), ('\\xHAFFAAAAFFAA'), ('\\xIAFFAAAAFFAAAAFFAA')"));
144
145 // Insert INvalid hex strings (odd # of chars)
146 REQUIRE_FAIL(con.Query("INSERT INTO blobs VALUES('\\xAAAFFAA'), ('\\xAAAFFAAAAFFAA'), ('\\xAAAFFAAAAFFAAAAFFAA')"));
147
148 // insert BLOB with “non-printable” octets
149 REQUIRE_NO_FAIL(con.Query("INSERT INTO blobs VALUES ('\153\154\155 \052\251\124'::BYTEA)"));
150
151 // insert BLOB with “non-printable” octets, but now using VARCHAR string (should fail)
152 REQUIRE_FAIL(con.Query("INSERT INTO blobs VALUES ('\153\154\155 \052\251\124'::VARCHAR)"));
153 REQUIRE_FAIL(con.Query("INSERT INTO blobs VALUES ('\153\154\155 \052\251\124')"));
154
155 // insert BLOB with “non-printable” octets, but now using string
156 REQUIRE_NO_FAIL(con.Query("INSERT INTO blobs VALUES ('\153\154\155 \052\251\124'::BLOB)"));
157}
158
159TEST_CASE("Select BLOB values", "[blob]") {
160 unique_ptr<QueryResult> result;
161 DuckDB db(nullptr);
162 Connection con(db);
163
164 REQUIRE_NO_FAIL(con.Query("CREATE TABLE blobs (b BYTEA);"));
165 REQUIRE_NO_FAIL(con.Query("INSERT INTO blobs VALUES ('\\xFF00AA'), ('a a'::BYTEA)"));
166 REQUIRE_NO_FAIL(con.Query("INSERT INTO blobs VALUES ('\153\154\155 \052\251\124'::BYTEA)"));
167
168 result = con.Query("SELECT * FROM blobs");
169 REQUIRE(CHECK_COLUMN(result, 0, {Value::BLOB("\\xFF00AA"), Value::BLOB("a a"), Value::BLOB("\153\154\155 \052\251\124")}));
170
171 //BLOB with “non-printable” octets
172 REQUIRE_NO_FAIL(con.Query("SELECT 'abc \201'::BYTEA;"));
173 result = con.Query("SELECT 'abc \201'::BYTEA;");
174 REQUIRE(CHECK_COLUMN(result, 0, {Value::BLOB("abc \201")}));
175
176 REQUIRE_NO_FAIL(con.Query("SELECT 'abc \153\154\155 \052\251\124'::BYTEA;"));
177 result = con.Query("SELECT 'abc \153\154\155 \052\251\124'::BYTEA;");
178 REQUIRE(CHECK_COLUMN(result, 0, {Value::BLOB("abc \153\154\155 \052\251\124")}));
179
180 //now VARCHAR with “non-printable” octets, should fail
181 REQUIRE_FAIL(con.Query("SELECT 'abc \201'::VARCHAR;"));
182 REQUIRE_FAIL(con.Query("SELECT 'abc \153\154\155 \052\251\124'::VARCHAR;"));
183}
184
185TEST_CASE("Test BLOB with COPY INTO", "[blob]") {
186 unique_ptr<QueryResult> result;
187 DuckDB db(nullptr);
188 Connection con(db);
189
190 // Creating a blob buffer with almost ALL ASCII chars
191 uint8_t num_chars = 256 - 5; // skipping: '\0', '\n', '\15', ',', '\32'
192 unique_ptr<char[]> blob_chars(new char[num_chars + 1]);
193 char ch = '\0';
194 idx_t buf_idx = 0;
195 for(idx_t i = 0; i < 255; ++i, ++ch) {
196 // skip chars: '\0', new line, shift in, comma, and crtl+Z
197 if(ch == '\0' || ch == '\n' || ch == '\15' || ch == ',' || ch == '\32') {
198 continue;
199 }
200 blob_chars[buf_idx] = ch;
201 ++buf_idx;
202 }
203 blob_chars[num_chars] = '\0';
204
205 // Wrinting BLOB values to a csv file
206 string blob_file_path = TestCreatePath("blob_file.csv");
207 ofstream ofs_blob_file(blob_file_path, std::ofstream::out | std::ofstream::app);
208 // Insert all ASCII chars from 1 to 255, skipping '\0', '\n', '\15', and ',' chars
209 ofs_blob_file << blob_chars.get();
210 ofs_blob_file.close();
211
212 // COPY INTO
213 REQUIRE_NO_FAIL(con.Query("CREATE TABLE blobs (b BYTEA);"));
214 result = con.Query("COPY blobs FROM '" + blob_file_path + "';");
215 REQUIRE(CHECK_COLUMN(result, 0, {1}));
216
217 // Testing if the system load/store correctly the bytes
218 string blob_str(blob_chars.get(), num_chars);
219 result = con.Query("SELECT b FROM blobs");
220 REQUIRE(CHECK_COLUMN(result, 0, {Value::BLOB(blob_str)}));
221
222 blob_chars.reset();
223 TestDeleteFile(blob_file_path);
224}
225
226TEST_CASE("Test BLOB with PreparedStatement from a file", "[blob]") {
227 unique_ptr<QueryResult> result;
228 DuckDB db(nullptr);
229 Connection con(db);
230
231 // Creating a blob buffer with almost ALL ASCII chars
232 uint8_t num_chars = 256 - 5; // skipping: '\0', '\n', '\15', ',', '\32'
233 unique_ptr<char[]> blob_chars(new char[num_chars]);
234 char ch = '\0';
235 idx_t buf_idx = 0;
236 for(idx_t i = 0; i < 255; ++i, ++ch) {
237 // skip chars: '\0', new line, shift in, comma, and crtl+Z
238 if(ch == '\0' || ch == '\n' || ch == '\15' || ch == ',' || ch == '\32') {
239 continue;
240 }
241 blob_chars[buf_idx] = ch;
242 ++buf_idx;
243 }
244
245 REQUIRE_NO_FAIL(con.Query("CREATE TABLE blobs (b BYTEA);"));
246
247 // Insert blob values through a PreparedStatement
248 string str_blob(blob_chars.get(), num_chars);
249 unique_ptr<PreparedStatement> ps = con.Prepare("INSERT INTO blobs VALUES (?::BYTEA)");
250 ps->Execute(str_blob);
251 REQUIRE(ps->success);
252 ps.reset();
253
254 // Testing if the bytes are stored correctly
255 result = con.Query("SELECT OCTET_LENGTH(b) FROM blobs");
256 REQUIRE(CHECK_COLUMN(result, 0, {num_chars}));
257
258 result = con.Query("SELECT count(b) FROM blobs");
259 REQUIRE(CHECK_COLUMN(result, 0, {1}));
260
261 result = con.Query("SELECT b FROM blobs");
262 REQUIRE(CHECK_COLUMN(result, 0, {Value::BLOB(str_blob)}));
263
264 blob_chars.reset();
265}
266
267TEST_CASE("BLOB with Functions", "[blob]") {
268 unique_ptr<QueryResult> result;
269 DuckDB db(nullptr);
270 Connection con(db);
271 REQUIRE_NO_FAIL(con.Query("CREATE TABLE blobs (b BYTEA);"));
272 REQUIRE_NO_FAIL(con.Query("INSERT INTO blobs VALUES ('a'::BYTEA)"));
273
274 // conventional concat
275 result = con.Query("SELECT b || 'ZZ'::BYTEA FROM blobs");
276 REQUIRE(CHECK_COLUMN(result, 0, {Value::BLOB("aZZ")}));
277
278 REQUIRE_NO_FAIL(con.Query("SELECT 'abc '::BYTEA || '\153\154\155 \052\251\124'::BYTEA"));
279 result = con.Query("SELECT 'abc '::BYTEA || '\153\154\155 \052\251\124'::BYTEA");
280 REQUIRE(CHECK_COLUMN(result, 0, {Value::BLOB("abc \153\154\155 \052\251\124")}));
281
282 result = con.Query("SELECT 'abc '::BYTEA || '\153\154\155 \052\251\124'::BYTEA");
283 REQUIRE(CHECK_COLUMN(result, 0, {Value::BLOB("abc \153\154\155 \052\251\124")}));
284
285 REQUIRE_NO_FAIL(con.Query("INSERT INTO blobs VALUES ('abc \153\154\155 \052\251\124'::BYTEA)"));
286
287 result = con.Query("SELECT COUNT(*) FROM blobs");
288 REQUIRE(CHECK_COLUMN(result, 0, {2}));
289
290 // octet_length
291 result = con.Query("SELECT OCTET_LENGTH(b) FROM blobs");
292 REQUIRE(CHECK_COLUMN(result, 0, {1, 11}));
293
294 // HEX strings
295 REQUIRE_NO_FAIL(con.Query("DELETE FROM blobs"));
296 REQUIRE_NO_FAIL(con.Query("INSERT INTO blobs VALUES ('\\xFF'::BYTEA)"));
297
298 result = con.Query("SELECT b || 'ZZ'::BYTEA FROM blobs");
299 REQUIRE(CHECK_COLUMN(result, 0, {Value::BLOB("\\xFF5A5A")}));
300
301 result = con.Query("SELECT b || '\\x5A5A'::BYTEA FROM blobs");
302 REQUIRE(CHECK_COLUMN(result, 0, {Value::BLOB("\\xFF5A5A")}));
303
304 // BLOB || VARCHAR is not allowed, should fail
305 REQUIRE_FAIL(con.Query("SELECT b || '5A5A'::VARCHAR FROM blobs"));
306
307 // Octet Length tests
308 REQUIRE_NO_FAIL(con.Query("DELETE FROM blobs"));
309 REQUIRE_NO_FAIL(con.Query("INSERT INTO blobs VALUES ('\\xFF'::BYTEA)"));
310 REQUIRE_NO_FAIL(con.Query("INSERT INTO blobs VALUES ('FF'::BYTEA)"));
311
312 REQUIRE_NO_FAIL(con.Query("INSERT INTO blobs VALUES ('\\x55AAFF55AAFF55AAFF01'::BYTEA)"));
313 REQUIRE_NO_FAIL(con.Query("INSERT INTO blobs VALUES ('55AAFF55AAFF55AAFF01'::BYTEA)"));
314
315 result = con.Query("SELECT OCTET_LENGTH(b) FROM blobs");
316 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 10, 20}));
317}
318
319TEST_CASE("Test BLOBs with various SQL operators", "[blob]") {
320 unique_ptr<QueryResult> result;
321 DuckDB db(nullptr);
322 Connection con(db);
323 REQUIRE_NO_FAIL(con.Query("CREATE TABLE blobs (b BYTEA, g INTEGER);"));
324 // strings: hello -> \x68656C6C6F, r -> \x72
325 REQUIRE_NO_FAIL(con.Query("INSERT INTO blobs VALUES ('hello', 0), ('\\xAAFFAA', 1), (NULL, 0), ('r', 1)"));
326
327 // simple aggregates only
328 result = con.Query("SELECT COUNT(*), COUNT(b), MIN(b), MAX(b) FROM blobs");
329 REQUIRE(CHECK_COLUMN(result, 0, {4}));
330 REQUIRE(CHECK_COLUMN(result, 1, {3}));
331 REQUIRE(CHECK_COLUMN(result, 2, {"hello"}));
332 REQUIRE(CHECK_COLUMN(result, 3, {Value::BLOB("\\xAAFFAA")}));
333
334 // ORDER BY
335 result = con.Query("SELECT * FROM blobs ORDER BY b");
336 REQUIRE(CHECK_COLUMN(result, 0, {Value(nullptr), "hello", "r", Value::BLOB("\\xAAFFAA")}));
337
338 // GROUP BY
339 REQUIRE_NO_FAIL(con.Query("INSERT INTO blobs VALUES ('hello', 3), ('\\xAAFFAA', 9), (NULL, 0), ('r', 19)"));
340 result = con.Query("SELECT SUM(g) FROM blobs GROUP BY b ORDER BY b");
341 REQUIRE(CHECK_COLUMN(result, 0, {Value(0.0), Value(3.0), Value(20.0), Value(10.0)}));
342
343 // JOIN
344 REQUIRE_NO_FAIL(con.Query("CREATE TABLE blobs2 (b BYTEA, g INTEGER);"));
345 REQUIRE_NO_FAIL(con.Query("INSERT INTO blobs2 VALUES ('hello', 0), ('\\xAAFFAA', 100), (NULL, 0), ('r', 200)"));
346
347 // group by blobs.b, explicit JOIN
348 result = con.Query("SELECT L.b, SUM(L.g) FROM blobs as L JOIN blobs2 AS R ON L.b=R.b GROUP BY L.b ORDER BY L.b");
349 REQUIRE(CHECK_COLUMN(result, 0, {"hello", "r", Value::BLOB("\\xAAFFAA")}));
350 REQUIRE(CHECK_COLUMN(result, 1, {Value(3.0), Value(20.0), Value(10.0)}));
351
352 // group by blobs2.b, implicit JOIN
353 result = con.Query("SELECT R.b, SUM(R.g) FROM blobs as L, blobs2 AS R WHERE L.b=R.b GROUP BY R.b ORDER BY R.b");
354 REQUIRE(CHECK_COLUMN(result, 0, {"hello", "r", Value::BLOB("\\xAAFFAA")}));
355 REQUIRE(CHECK_COLUMN(result, 1, {Value(0.0), Value(400.0), Value(200.0)}));
356}
357