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 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)

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_roles is 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 that happen to be named oid do survive that far. Therefore, postgres does not realize that is has an ambiguous column reference on its hands and binds to column presented by the view. I could definitely be wrong because I haven’t looked a the 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