Bruce Momjian <pgman@candle.pha.pa.us> writes:
> test=> select * from pg_language p where p.oid = pg_language.oid;
> lanname | lanispl | lanpltrusted | lanplcallfoid | lancompiler
> ----------+---------+--------------+---------------+-------------
> internal | f | f | 0 | n/a
> C | f | f | 0 | /bin/cc
> sql | f | f | 0 | postgres
> (3 rows)
Oh, this is interesting! According to Postgres' view of the world,
you have written a join between "pg_language p" and
"pg_language pg_language", where the latter is an implicitly added
FROM clause. If you do an EXPLAIN you can see that a join is indeed
being done:
regression=# explain
regression-# select * from pg_language p where p.oid = pg_language.oid;
NOTICE: QUERY PLAN:
Hash Join (cost=2.60 rows=4 width=58) -> Seq Scan on pg_language p (cost=1.13 rows=4 width=54) -> Hash (cost=1.13
rows=4width=4) -> Seq Scan on pg_language (cost=1.13 rows=4 width=4)
EXPLAIN
and a more graphic demonstration is had by using a WHERE clause that
can produce multiple matches:
regression=# select * from pg_language p where p.oid < pg_language.oid;lanname | lanispl | lanpltrusted |
lanplcallfoid| lancompiler
----------+---------+--------------+---------------+-------------internal | f | f | 0 |
n/ainternal| f | f | 0 | n/aC | f | f | 0 |
/bin/ccinternal| f | f | 0 | n/aC | f | f | 0 |
/bin/ccsql | f | f | 0 | postgres
(6 rows)
What it looks like to me is that we have a bug in the expansion of '*'.
It should be generating columns for both the explicit and the implicit
FROM clause, but it's evidently deciding that it should only produce
output columns for the first one.
This may go a long way towards explaining why people have been so
readily confused by the implicit-FROM-clause business! If they saw
two sets of columns coming out, it'd be more obvious that they were
getting a join.
> Does the standard say the first query is legal?
I believe it is not strict SQL92 --- we've been around on that question
before.
regards, tom lane