SQL: Expression within SELECT produces error("missing")

[philrz]

$ super -c 'SELECT 1 AS a, a * 2'
{a:1,"a*2":error("missing")}

Details

Repro is with super commit cabf51f. This is a query from a sqllogictest.

Other SQL-based systems produce the result of the expression, e.g.,

$ duckdb --version
v1.3.0 71c5c07cdd

$ duckdb -c 'SELECT 1 AS a, a * 2'
┌───────┬─────────┐
│   a   │ (a * 2) │
│ int32 │  int32  │
├───────┼─────────┤
│   1   │    2    │
└───────┴─────────┘

Interestingly, with SuperDB, the expected result is produced if I launder through a CTE.

$ super -version
Version: cabf51f1a

$ super -c '
WITH vals AS (SELECT 1 AS a)
SELECT a, a * 2 FROM vals;'

{a:1,"a*2":2}

[mattnibs]

On postgres I get:

ERROR:  column "a" does not exist
LINE 1: SELECT 1 as a, a*2
                       ^

[mattnibs]

duckdb seems to default to the column if you have an alias and a column collide:

duckdb -c 'select 1 as a, a*2 from (values (1),(2),(3)) as x(a);'
┌───────┬─────────┐
│   a   │ (a * 2) │
│ int32 │  int32  │
├───────┼─────────┤
│     1 │       2 │
│     1 │       4 │
│     1 │       6 │
└───────┴─────────┘

[philrz]

Thanks for pointing out the Postgres behavior @mattnibs. Yeah, this one was earlier in my work with sqllogictest before I was in the habit of only filing issues when I could also repro the behavior in Postgres. So if this is one where we don't want to try to match the behavior I'd totally understand that.

FWIW, I did bump into the "alias and column collide" in yet another sqllogictest and that one does repro is Postgres. See #5974.