TRY_CAST as INT fails on column with bad value: column was declared as integer, found "" of type "text" instead.

[arcutright]

What happens?

• try to import sqlite database into duckdb
• have nullable column with bad values
• use TRY_CAST, expecting it to insert as null | 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?

• [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