Bug: DuckDB binds BLOBs as TEXT when inserting data into SQLite

[asg017]

What happens?

When you attach a SQLite database from DuckDB, and you try to insert BLOBs into a SQLite table, the values get bound as TEXT instead of BLOBs.

To Reproduce

import duckdb

db = duckdb.connect(":memory:")
db.execute("load sqlite;")
db.execute("attach 'tmp.db' as target (type sqlite);")
db.execute("create table target.x(a blob);")
db.execute("insert into target.x(a) select encode('my_string_with_ü')");
db.close()

import sqlite3
db = sqlite3.connect("tmp.db")
print(db.execute("select typeof(a) from x").fetchone()[0])

The final line prints text, which means a was bound as TEXT and not a BLOB.

I believe this is because of this code, where SQLiteStatement::BindValue binds BLOBs with BindText, and a new BindBlob option could be added. https://github.com/duckdb/sqlite_scanner/blob/50b7870be099186f195bc72bac5e9e11247ee2f9/src/sqlite_stmt.cpp#L154-L157

OS:

MacOS aarch64

SQLite Version:

3.44.2

DuckDB Version:

0.10.3

DuckDB Client:

Python

Full Name:

Alex Garcia

Affiliation:

self

Have you tried this on the latest main branch?

• [X] I agree

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• [X] I agree