Re: Bug: Ambiguous Column Reference Allowed When Joining to pg_roles.oid

Matthew Kelly <mkelly(at)tripadvisor(dot)com> writes:
> I recently fell afoul of a weird edge case while writing an extension. It seems Postgres allows for an ambiguous column reference to oid in the where clause when joining to pg_roles. It just arbitrarily chooses pg_roles.oid and ignores the 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