Boolean implicitly cast to integer in UNION clause

[soerenwolfers]

What happens?

Booleans are implicitly cast to integers in UNION clauses.
This contradicts a statement made by @Mytherin at #7011 (comment) so is likely a bug. If it's not a bug, I will update my documentation PR at duckdb/duckdb-web#2830 to reflect the correct rules.

Note that booleans aren't implicitly casted to integers in other contexts, e.g., the following query fails:

SELECT 1::BOOLEAN + 1::INT

in accordance with https://duckdb.org/docs/sql/data_types/typecasting#implicit-casting.

To Reproduce

SELECT 1::BOOLEAN UNION ALL SELECT 1::INT

CAST(1 AS BOOLEAN)
1
1

OS:

Linux

DuckDB Version:

0.10.2

DuckDB Client:

CLI

Full Name:

Soeren Wolfers

Affiliation:

G-Research

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

[szarnyasg]

As discussed in the documentation PR (duckdb/duckdb-web#2830), this is expected behaviour.

[soerenwolfers]

@szarnyasg I'm confused. On the documentation PR, which you merged, I'm stating the UNION ALL is performing implicit casts only.

In the issue here, I'm complaining that UNION ALL performs more than that, because BOOL is not usually implicitly castable to integer types (as evidenced by the attempt to add bool and integer, and by the linked casting matrix)

[szarnyasg]

Right, thanks for spotting this! I'll have another round of discussion on how to word this in the docs.

[soerenwolfers]

But the expected behavior really is that the casting rules in the UNION ALL clause are different from those anywhere else?