Re: BUG #19003: A SELECT that does not return a valid table
| От | Alexandre Bailly |
|---|---|
| Тема | Re: BUG #19003: A SELECT that does not return a valid table |
| Дата | |
| Msg-id | CADZkWe1Ya2UCz6X87ikNHsD0PdKxiXJQ5vGWFWO4w6uyzdZvHA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: BUG #19003: A SELECT that does not return a valid table (Vik Fearing <vik@postgresfriends.org>) |
| Ответы |
Re: BUG #19003: A SELECT that does not return a valid table
|
| Список | pgsql-bugs |
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.
On 30/07/2025 23:34, Tom Lane wrote:PG Bug reporting form <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
В списке pgsql-bugs по дате отправления: