Horton

Thread

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

    PG Bug reporting form <noreply@postgresql.org> — 2025-07-30T19:20:14Z

    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.

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-07-30T21:34:41Z

    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

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

    David G. Johnston <david.g.johnston@gmail.com> — 2025-07-30T21:38:16Z

    On Wed, Jul 30, 2025, 12:50 PG Bug reporting form <noreply@postgresql.org>
wrote:

> 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.
>
>
This isn't a bug.  And at this point, regardless of such purity concerns,
there is no way we are going to change this behavior and break
well-functioning applications.

A query result is not a table.  It just has a tabular form (rows and
columns).  It is also not a set though set-theory did inspire various
aspects of SQL.

David J.

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

    Vik Fearing <vik@postgresfriends.org> — 2025-07-31T09:17:14Z

    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

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

    Alexandre Bailly <alexandrebailly1955@gmail.com> — 2025-07-31T10:50:32Z

    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
>

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

    Vik Fearing <vik@postgresfriends.org> — 2025-07-31T13:10:09Z

    On 31/07/2025 12:50, Alexandre Bailly wrote:
>
> 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