Setting to get errors on duplicated column names

[soerenwolfers]

What happens?

I regularly get tripped up by writing

SELECT *, a / 3 AS b FROM tbl;

and later noticing that b is being aliased by a column in the star expression (more precisely, b is auto-renamed to b1 so when I later reference b I get what was pulled in through the star expression).

PostgreSQL and MySQL both throw errors instead, and it'd be great if there was at least a setting to make duckdb also throw. Personally, I'd also be for changing the default (if I was aware that the column name clashes I could just name it differently myself, if I am not aware there is no way I'll actually use b_1, so in both cases the current behavior isn't useful, but in the second case it could be considered dangerous).

To Reproduce

SELECT 
    a
FROM (
    SELECT 
        *, 
        2 AS a 
    FROM (
        SELECT 1
    ) _(a)
) _;

throws an error on PostgreSQL and MySQL (e.g. ERROR: column reference "a" is ambiguous) but picks the first column in duckdb:

┌───────┐
│   a   │
│ int32 │
├───────┤
│     1 │
└───────┘

OS:

Linux

DuckDB Version:

0.10.2

DuckDB Client:

Python

Full Name:

Soeren Wolfers

Affiliation:

G-Research

Have you tried this on the latest nightly build?

I have tested with a nightly build

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

• Yes, I have