Re: column aliases

Did this get resolved somehow?

> 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=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@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026