Обсуждение: Bug: Ambiguous Column Reference Allowed When Joining to pg_roles.oid

I recently fell afoul of a weird edge case while writing an extension.  It seems Postgres allows for an ambiguous
columnreference to oid in the where clause when joining to pg_roles.  It just arbitrarily chooses pg_roles.oid and
ignoresthe conflicting name.  Example:
 


postgres=# CREATE TABLE t_demo();
CREATE TABLE
postgres=# SELECT r.rolname FROM pg_class c JOIN pg_roles r ON (c.relowner = r.oid) WHERE oid = 't_demo'::regclass;
 rolname
---------
(0 rows)

postgres=# SELECT r.rolname FROM pg_class c JOIN pg_roles r ON (c.relowner = r.oid) WHERE c.oid = 't_demo'::regclass;
 rolname
----------
 postgres
(1 row)

postgres=# SELECT r.rolname FROM pg_class c JOIN pg_roles r ON (c.relowner = r.oid) WHERE r.oid = 't_demo'::regclass;
 rolname
---------
(0 rows)


It seems like ambiguous oid references generally hit a different error message than normal ambiguous column
references.


postgres=# CREATE TABLE t1(x int, y int) WITH OIDS;
CREATE TABLE
postgres=# CREATE TABLE t2(x int, y int) WITH OIDS;
CREATE TABLE
postgres=# SELECT * FROM t1 JOIN t2 ON (t1.x = t2.x) WHERE y = 5;
ERROR:  column reference "y" is ambiguous
LINE 1: SELECT * FROM t1 JOIN t2 ON (t1.x = t2.x) WHERE y = 5;
postgres=# SELECT * FROM t1 JOIN t2 ON (t1.x = t2.x) WHERE oid = 5;
ERROR:  column "oid" does not exist
LINE 1: SELECT * FROM t1 JOIN t2 ON (t1.x = t2.x) WHERE oid = 5;
                                                        ^
HINT:  There is a column named "oid" in table "t1", but it cannot be referenced from this part of the query.



It’s clear that oids are getting to another code path normally and I suspected that it is related to the fact that
pg_rolesis a view with an explicit oid column.  So I tried this test:
 


postgres=# CREATE VIEW v1 AS SELECT x, y, oid FROM t1;
CREATE VIEW
postgres=# SELECT * FROM v1 JOIN t2 ON (v1.x = t2.x) WHERE oid = 5;
 x | y | oid | x | y
---+---+-----+---+---
(0 rows)


It would appear that unqualified oid columns do not make it all the way to where clause evaluation, whereas columns
thathappen to be named oid do survive that far.  Therefore, postgres does not realize that is has an ambiguous column
referenceon its hands and binds to column presented by the view.  I could definitely be wrong because I haven’t looked
athe code but that is what the behavior looks like.
 

This bug was first found on 9.3.19, but I just tested this against 10.1 as well.

- Matt K

Matthew Kelly <mkelly@tripadvisor.com> writes:
> I recently fell afoul of a weird edge case while writing an extension.  It seems Postgres allows for an ambiguous
columnreference to oid in the where clause when joining to pg_roles.  It just arbitrarily chooses pg_roles.oid and
ignoresthe conflicting name.  Example: 
> postgres=# CREATE TABLE t_demo();
> CREATE TABLE
> postgres=# SELECT r.rolname FROM pg_class c JOIN pg_roles r ON (c.relowner = r.oid) WHERE oid = 't_demo'::regclass;
>  rolname
> ---------
> (0 rows)

I do not think that's a bug exactly.  There's only one column named "oid"
exposed by the join, and once you're above the join it hides the column(s)
supplied by the input relations --- were that not so, you could never
reference a join output column without qualifying it.  If you try this
with just regular OID columns, you get

regression=# create table t1 (f1 int);
CREATE TABLE
regression=# create table t2 (f2 int) with oids;
CREATE TABLE
regression=# select * from t1 join t2 on (f1=f2) where oid = 42;
ERROR:  column "oid" does not exist
LINE 1: select * from t1 join t2 on (f1=f2) where oid = 42;
                                                  ^
HINT:  There is a column named "oid" in table "t2", but it cannot be referenced from this part of the query.

which indicates that you have to qualify the table's column if you
want to reference it above the join.  But if there's a matching
user-defined column in the join output then that doesn't happen.

It definitely is a bit unfortunate that the pg_roles view exposes
a user-defined column named "oid", but we felt we had to do that
to avoid breaking user queries that date from when pg_roles was
a plain table.

            regards, tom lane