[bug?] UNION operation with different datatypes doesn't fail.

[wtfzambo]

What happens?

I'm not sure if this is actually a bug or a feature, but I noticed that when UNIONing two tables with same columns that may have different data types, duckdb successfully completes the union and re-casts the incompatible columns to a compatible data type if there is one.

To be fair I'm not complaining, I actually find this quite convenient for what I'm doing, but I thought it was right to report.

To Reproduce

data = [
    [None, 1, 'a', None],
    ['foo', 2, 'b', None],
    ['bar', 3.0, 42, {'foo': 'bar'}]
]

cols = ['col1', 'col2', 'col3', 'col4']

df = pd.DataFrame(data, columns=cols)
head = df.head(0)
duckdb.sql('SELECT * FROM head UNION SELECT * FROM df')

OS:

Windows 11 Home x64

DuckDB Version:

0.7.1

DuckDB Client:

Python 3.10

Full Name:

Federico Zambelli

Affiliation:

Vinhood

Have you tried this on the latest master 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

[Mytherin]

Thanks for the report! This is indeed not a bug but intended behavior - if the types of a union do not match casts are added to compatible types if possible according to the implicit cast rules in the system, see here for more information.

[wtfzambo]

Fantastic, thanks for the explanation! I missed that in the docs as I was looking in the "set operations" part.