pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19003: A SELECT that does not return a valid table
6+ messages / 5 participants
[nested] [flat]
* BUG #19003: A SELECT that does not return a valid table
@ 2025-07-30 19:20 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 6+ messages in thread
From: PG Bug reporting form @ 2025-07-30 19:20 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: alexandrebailly1955@gmail.com
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19003: A SELECT that does not return a valid table
@ 2025-07-30 21:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2025-07-30 21:34 UTC (permalink / raw)
To: alexandrebailly1955@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19003: A SELECT that does not return a valid table
@ 2025-07-30 21:38 David G. Johnston <david.g.johnston@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 0 replies; 6+ messages in thread
From: David G. Johnston @ 2025-07-30 21:38 UTC (permalink / raw)
To: alexandrebailly1955@gmail.com, PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19003: A SELECT that does not return a valid table
@ 2025-07-31 09:17 Vik Fearing <vik@postgresfriends.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Vik Fearing @ 2025-07-31 09:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; alexandrebailly1955@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19003: A SELECT that does not return a valid table
@ 2025-07-31 10:50 Alexandre Bailly <alexandrebailly1955@gmail.com>
parent: Vik Fearing <vik@postgresfriends.org>
0 siblings, 1 reply; 6+ messages in thread
From: Alexandre Bailly @ 2025-07-31 10:50 UTC (permalink / raw)
To: Vik Fearing <vik@postgresfriends.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org
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)?redir...,
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19003: A SELECT that does not return a valid table
@ 2025-07-31 13:10 Vik Fearing <vik@postgresfriends.org>
parent: Alexandre Bailly <alexandrebailly1955@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Vik Fearing @ 2025-07-31 13:10 UTC (permalink / raw)
To: Alexandre Bailly <alexandrebailly1955@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2025-07-31 13:10 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-07-30 19:20 BUG #19003: A SELECT that does not return a valid table PG Bug reporting form <noreply@postgresql.org>
2025-07-30 21:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-07-31 09:17 ` Vik Fearing <vik@postgresfriends.org>
2025-07-31 10:50 ` Alexandre Bailly <alexandrebailly1955@gmail.com>
2025-07-31 13:10 ` Vik Fearing <vik@postgresfriends.org>
2025-07-30 21:38 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox