Thread: Weirdness with OIDs and JOIN ON?

Why doesn't this work:

test=# select oid, relname, indisclustered from pg_index join pg_class 
on indexrelid=oid where indexrelid > 17205;
ERROR:  column "oid" does not exist

I'm _joining_ on the oid column.

If I qualify it, it works:

test=# select pg_class.oid, relname, indisclustered from pg_index join 
pg_class on indexrelid=oid where indexrelid > 17205;  oid  |   relname    | indisclustered
-------+--------------+---------------- 17214 | child_b_key  | t 17210 | parent_a_key | t
(2 rows)

I can't see that I've made an error in the first example - is it a 
Postgres bug?

Chris

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> Why doesn't this work:
> test=# select oid, relname, indisclustered from pg_index join pg_class 
> on indexrelid=oid where indexrelid > 17205;
> ERROR:  column "oid" does not exist

The JOIN is a bit like a view --- it doesn't have any system columns.
For example consider

select alias.* from (a join b on ...) as alias;

This should certainly not show any system columns --- both because SQL92
says so, and because there'd be no way to choose which input table's
system columns to use.

In scenarios where only one input table has an OID column, we could
maybe kluge things to allow it to be accessed without qualification,
but I don't really see the value of adding a wart for that ...
        regards, tom lane