Good morning yawn

Good night

@AndriyM Only SQL Server suffers from this. Postgres, MySQL, SQLite have no problem naming the column with a name of their choice.

@ypercubeᵀᴹ Well, SELECT * FROM (SELECT 1) AS derived; does work in Postgres but so far I've no idea what the derived table's column is called.

It's called "?column?" ;)

In MySL, SQlite and Oracle, it's called "1"

Yeah, that's what I tried and it didn't work, and I'm out of ideas at the moment :)

You can use SUM(derived."1") or SUM(`1`) to verify

It's all weird anyway.

SQL Server does not allow columns to not have defined names

Oracle allows it but if you have 2 columns with same (auto assigned names), you can't reference them in a derived table.

SELECT 1, 1 FROM dual UNION ALL SELECT 2,2 FROM dual ; works fine

but this doesn't:

SELECT t.*
FROM (SELECT 1, 1 FROM dual UNION ALL SELECT 2,2 FROM dual) t ;

@ypercubeᵀᴹ what do you make of this: dbfiddle.uk/… ?

@JackDouglas dbfiddle.uk/…

Wants this:

select "'FOO'" from (select 'FOO' from dual);

oh ha ha, thanks

The naming is I think left as an implementation detail in the standard, so the differentiation

select '"FOO' from dual;

dbfiddle.uk/…

@JackDouglas nice!

Perhaps my answer needs some bold text in the last paragraph

Maybe I'll figure it out in the morning