[CLI] Empty strings for BLOB values in .mode insert / .dump #16554
Description
What happens?
For tables (or queries) with BLOB columns, .dump (or .mode insert) only produce empty strings instead of some kind "literal BLOB value".
In duckdb-cli 1.2.0 and 1.2.1.
Also checked with 1.1.3, there the BLOBs are emitted as sqlite style BLOB literal ( X'01F32B' ) which is just wrong (as it is somewhat strangly interpreded as string with a prefixed X 'x01F32B' - parser glitch ?)
It would make sense to emit these as eg '\x01\xF3\x2B'::BLOB.
To Reproduce
code
create table test(t VARCHAR, b BLOB);
insert into test values('literal blob', '\x07\x08\x09');
insert into test values('text-as-blob', 'ABC'::BLOB);
insert into test values('unhex', unhex('040506'));
select * from test;
.mode insert TEST
select * from test;
.dump
results on 1.2.0/1.2.1 / BLOBs missing
-- select * from test;
┌──────────────┬──────────────┐
│ t │ b │
│ varchar │ blob │
├──────────────┼──────────────┤
│ literal blob │ \x07\x08\x09 │
│ text-as-blob │ ABC │
│ unhex │ \x04\x05\x06 │
└──────────────┴──────────────┘
-- .mode insert TEST
-- select * from test;
INSERT INTO TEST(t,b) VALUES('literal blob','');
INSERT INTO TEST(t,b) VALUES('text-as-blob','');
INSERT INTO TEST(t,b) VALUES('unhex','');
-- .dump
BEGIN TRANSACTION;
CREATE TABLE test(t VARCHAR, b BLOB);;
INSERT INTO test VALUES('literal blob','');
INSERT INTO test VALUES('text-as-blob','');
INSERT INTO test VALUES('unhex','');
COMMIT;
results on 1.1.3 / BLOBs as invalid (sqlite-style) literals
-- select * from test;
┌──────────────┬──────────────┐
│ t │ b │
│ varchar │ blob │
├──────────────┼──────────────┤
│ literal blob │ \x07\x08\x09 │
│ text-as-blob │ ABC │
│ unhex │ \x04\x05\x06 │
└──────────────┴──────────────┘
-- .mode insert TEST
-- select * from test;
INSERT INTO TEST(t,b) VALUES('literal blob',X'070809');
INSERT INTO TEST(t,b) VALUES('text-as-blob',X'414243');
INSERT INTO TEST(t,b) VALUES('unhex',X'040506');
-- .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(t VARCHAR, b BLOB);;
INSERT INTO test VALUES('literal blob',X'070809');
INSERT INTO test VALUES('text-as-blob',X'414243');
INSERT INTO test VALUES('unhex',X'040506');
COMMIT;
OS:
Linux (Ubuntu 24.04), x86_x64
DuckDB Version:
1.2.1
DuckDB Client:
CLI
Hardware:
No response
Full Name:
Marc Gerber
Affiliation:
private
What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.
I have tested with a stable release
Did you include all relevant data sets for reproducing the issue?
Yes
Did you include all code required to reproduce the issue?
- Yes, I have
Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?
- Yes, I have