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'
