Re: column aliases

Did this get resolved somehow?

> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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=4 width=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/a
> internal | f | f | 0 | n/a
> C | f | f | 0 | /bin/cc
> internal | f | f | 0 | n/a
> C | f | f | 0 | /bin/cc
> sql | 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
>

--
Bruce Momjian | http://www.op.net/~candle
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026