Обсуждение: BUG #19003: A SELECT that does not return a valid table
The following bug has been logged on the website: Bug reference: 19003 Logged by: Alexandre Bailly Email address: alexandrebailly1955@gmail.com PostgreSQL version: 17.5 Operating system: Windows Description: SELECT 'John' AS nom,'James' AS nom returns a table with attributes nom and nom that I can see in the output. The table is invalid because SELECT nom FROM (SELECT 'John' AS nom,'James' AS nom) returns ERROR: column reference "nom" is ambiguous LINE 1: SELECT nom FROM (SELECT 'John' AS nom,'James' AS nom). Returning a table that contains twice the same column should not be permitted. It should be a nice mathematical property (closure) if a SELECT always returns a valid table. Remark: CREATE TABLE customer ( nom text, nom text ); returns ERROR: column "nom" specified more than once.
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.
regards, tom lane
The following bug has been logged on the website:
Bug reference: 19003
Logged by: Alexandre Bailly
Email address: alexandrebailly1955@gmail.com
PostgreSQL version: 17.5
Operating system: Windows
Description:
Returning a table that contains twice the same column should not be
permitted. It should be a nice mathematical property (closure) if a SELECT
always returns a valid table.
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
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
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.
Yes, a <with list element> is an inlined viewed table.
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.
Then don't do it?
Anyway, you have your answer to this bug report and that is: it is not a bug and we are not going to change it.
--
Vik Fearing