TRY_CAST as INT fails on column with bad value: column was declared as integer, found "" of type "text" instead. #107
Description
What happens?
- try to import sqlite database into duckdb
- have nullable column with bad values
- use
TRY_CAST, expecting it to insert asnull | bigint - get an error
To Reproduce
import os
import sqlite3
import duckdb
for file in ('file.sqlite', 'file.duckdb'):
if os.path.exists(file):
os.remove(file)
with sqlite3.connect('file.sqlite') as conn:
conn.execute(
"""
CREATE TABLE mytable (
id integer primary key autoincrement
, gain int
)
""")
for value in range(1, 5):
conn.execute("INSERT INTO mytable (gain) values (?)", [value])
conn.execute("INSERT INTO mytable (gain) values ('')")
with duckdb.connect('file.duckdb') as conn:
conn.execute("INSTALL sqlite; LOAD sqlite;")
conn.execute("ATTACH 'file.sqlite' as _sqlite (TYPE sqlite);")
conn.execute("CREATE TABLE mytable as select * from _sqlite.mytable LIMIT 0") # import schema only
conn.execute("INSERT INTO mytable select id, TRY_CAST(gain as BIGINT) from _sqlite.mytable")leads to
Traceback (most recent call last):
File "test.py", line 21, in <module>
with duckdb.connect('file.duckdb') as conn:
File "test.py", line 25, in <module>
conn.execute("INSERT INTO mytable select id, TRY_CAST(gain as BIGINT) from _sqlite.mytable")
duckdb.duckdb.TypeMismatchException: Mismatch Type Error: Invalid type in column "gain": column was declared as integer, found "" of type "text" instead.
OS:
win10 x64
SQLite Version:
3.43.1
DuckDB Version:
1.0.0
DuckDB Client:
python
Full Name:
Aaron C
Affiliation:
n/a
Have you tried this on the latest main branch?
- 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?
- I agree