Setting to make column identifiers more case sensitive

[soerenwolfers]

It'd be nice if there was a configuration setting to make duckdb either (1) throw if a column identifier matches two or more columns that match exactly or differ only in capitalization (currently it'll randomly pick one) (2) require matching capitalization in that case (3) always require matching capitalization, i.e., be fully case sensitive.

In scientific applications and in quick exploratory research, it's very common to use capitalization to distinguish variables. For example, M could be a matrix while m could be a count; L could be a physical constant, while l could be a length, V could be a volume while v could be velocity.

Currently duckdb remembers capitalization of columns but randomly picks out of matching columns in case of conflicts. I personally find this behavior never useful.

[Tishj]

Currently duckdb remembers capitalization of columns but randomly picks out of matching columns in case of conflicts

Can you provide an example of this happening?
That sounds like a bug to me, we don't remember capitalization of columns, we use a case-insensitive map/set to store them.
It sounds like the second addition is overwriting the first one, which likely isn't checked but should be and throw an error

[soerenwolfers]

Turns out I misinterpreted "in case of a conflict, when the same identifier is spelt with different cases, one will be selected randomly."

My example was going to be

SELECT 
a
FROM
(
    SELECT 1, 2
) _(A, a)

which throws in PostgreSQL but returns 1 in duckdb.

I thought random picking was going on here because if you just look at SELECT 1 AS A, 2 AS a in duckdb you get

┌───────┬───────┐
│   A   │   a   │
│ int32 │ int32 │
├───────┼───────┤
│     1 │     2 │
└───────┴───────┘

which doesn't show deduplication happening,
but it turns out that if this is used within another query it's deduplicated to A, a_1 and then a is matched against A.

Nevertheless: This all feels rather unhelpful to me. Given what I've learned about the deduplication now, I'd wish for two orthogonal settings: (1) Be completely case sensitive (2) throw on duplicate column names instead of deduplication. I'd personally set both of these, but I can imagine many users might also just want (2) alone, which seems to be PostgreSQL behavior.

[soerenwolfers]

And in fact, I already asked for (2) over at #11520 -- I just didn't understand what was going on properly when I asked it. (Since PostgreSQL behaves differently and this has the potential of serious bugs I reported it as issue rather than idea/discussion).

[soerenwolfers]

Closing this in favor of #13952 to make

SELECT ... FROM (...) _(A, a)

throw an error.