Re: BUG #19003: A SELECT that does not return a valid table

WITH customer AS
(SELECT 'John' AS name,'James' AS name)
SELECT name FROM customer;

returns ERROR: column reference "name" is ambiguous

I can improve into

WITH customer(name1,name2) AS
(SELECT 'John' AS name,'James' AS name)
SELECT name2 FROM customer;

that returns name2 James.

 Looking at
https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms190766(v=sql.105)?redirectedfrom=MSDN,
I see.

The basic syntax structure for a CTE is:

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

The list of column names is optional only if distinct names for all
resulting columns are supplied in the query definition.

Well it looks like SQL Server is doing a better job.

I am OK with SELECT 'John' AS name,'James' AS name returning 2 columns with
the same name.

I am not OK with the same query used as a sub-select.

Le jeu. 31 juil. 2025 à 11:17, Vik Fearing <vik@postgresfriends.org> a
écrit :

>
> On 30/07/2025 23:34, Tom Lane wrote:
>
> PG Bug reporting form <noreply@postgresql.org> <noreply@postgresql.org> writes:
>
> SELECT 'John' AS nom,'James' AS nom
> returns a table with attributes nom and nom that I can see in the output.
> Returning a table that contains twice the same column should not be
> permitted.
>
> There is pretty much zero chance that we will enforce that
> restriction.  It would break too much application code.
> Also, it looks to me like there is no such requirement in
> the SQL standard.
>
>
> Indeed.  In SQL:2023-1 (available free of charge at [1]) says in Subclause
> 4.6, "Tables":
>
>
>     "An operation that references zero or more base tables and returns a
> table is called a *query*. The result of a query is called a *derived
> table*."
>
>
> and
>
>
>     "Derived tables, other than viewed tables, may contain more than one
> column with the same name."
>
>
> So, not only is this not a bug we should fix, it is explicitly allowed by
> the standard.
>
>
> [1] https://www.iso.org/standard/76583.html
>
> --
>
> Vik Fearing
>