Обсуждение: BUG #15088: Can create subquery with duplicate column names

The following bug has been logged on the website:

Bug reference:      15088
Logged by:          Tudor Bosman
Email address:      tudorb@gmail.com
PostgreSQL version: 9.5.11
Operating system:   Ubuntu 16.04
Description:

This may not be a bug, but you can create a subquery that has ambiguous
column names, and then you have no way to disambiguate between them, as the
originating table names are no longer in scope.

tudor=# create table t1 (x integer);
CREATE TABLE
tudor=# insert into t1 values (1);
INSERT 0 1
tudor=# create table t2 (x integer);
CREATE TABLE
tudor=# insert into t2 values (2);
INSERT 0 1
tudor=# select * from t1 cross join t2;
 x | x
---+---
 1 | 2
(1 row)
tudor=# select t1.x, t2.x from t1 cross join t2;
 x | x
---+---
 1 | 2
(1 row)
tudor=# select * from (select t1.x, t2.x from t1 cross join t2) a;
 x | x
---+---
 1 | 2
(1 row)

... and the result now has two columns named x, and I can't tell them
apart:

tudor=# select t1.x from (select t1.x, t2.x from t1 cross join t2) a;
ERROR:  missing FROM-clause entry for table "t1"
LINE 1: select t1.x from (select t1.x, t2.x from t1 cross join t2) a...

MySQL reports an error in this case:

mysql> select t1.x, t2.x from t1 cross join t2;
+------+------+
| x    | x    |
+------+------+
|    1 |    2 |
+------+------+
1 row in set (0.00 sec)

mysql> select * from (select t1.x, t2.x from t1 cross join t2) a;
ERROR 1060 (42S21): Duplicate column name 'x'

On 02/26/2018 01:34 AM, PG Bug reporting form wrote:
> The following bug has been logged on the website:
> 
> Bug reference:      15088
> Logged by:          Tudor Bosman
> Email address:      tudorb@gmail.com
> PostgreSQL version: 9.5.11
> Operating system:   Ubuntu 16.04
> Description:        
> 
> This may not be a bug, but you can create a subquery that has ambiguous
> column names, and then you have no way to disambiguate between them, as the
> originating table names are no longer in scope.
> 
> tudor=# create table t1 (x integer);
> CREATE TABLE
> tudor=# insert into t1 values (1);
> INSERT 0 1
> tudor=# create table t2 (x integer);
> CREATE TABLE
> tudor=# insert into t2 values (2);
> INSERT 0 1
> tudor=# select * from t1 cross join t2;
>  x | x
> ---+---
>  1 | 2
> (1 row)
> tudor=# select t1.x, t2.x from t1 cross join t2;
>  x | x
> ---+---
>  1 | 2
> (1 row)
> tudor=# select * from (select t1.x, t2.x from t1 cross join t2) a;
>  x | x
> ---+---
>  1 | 2
> (1 row)
> 
> ... and the result now has two columns named x, and I can't tell them
> apart:
> 
> tudor=# select t1.x from (select t1.x, t2.x from t1 cross join t2) a;
> ERROR:  missing FROM-clause entry for table "t1"
> LINE 1: select t1.x from (select t1.x, t2.x from t1 cross join t2) a...
> 

Not really. That error means the "t1" table name is unknown at that part
of the plan, because you've the subselect has alias "a". You might also
write it like this:

    select t1.x from (select t1.x, t2.x from t1 cross join t2) AS a;

but the "AS" keyword is optional. So the "t1.x" reference is incorrect,
and the database complains about it.

You should have written the query like this:

    select a.x from (select t1.x, t2.x from t1 cross join t2) a;

at which point you'll get

    test=# select a.x from (select t1.x, t2.x from t1 cross join t2) a;
    ERROR:  column reference "x" is ambiguous
    LINE 1: select a.x from (select t1.x, t2.x from t1 cross join t2) a;
                   ^

which seems rather reasonable, I guess. The point is, you'll get an
error if (and only if) the database can't resolve any reference.

I wonder if SQL standard says something about this behavior, but if I
had to be I'd put my money on "our" behavior.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

The following bug has been logged on the website:

Bug reference:      15088
Logged by:          Tudor Bosman
Email address:      tudorb@gmail.com
PostgreSQL version: 9.5.11
Operating system:   Ubuntu 16.04
Description:

This may not be a bug, but you can create a subquery that has ambiguous
column names, and then you have no way to disambiguate between them, as the
originating table names are no longer in scope.

... and the result now has two columns named x, and I can't tell them
apart: