Обсуждение: column aliases
Can someone explain this to me? When I use the alias p, should a reference to pg_language.oid create a new instance of pg_language in the range table. Seems it does not. Does the standard say the first query is legal? --------------------------------------------------------------------------- test=> select * from pg_language p where p.oid = pg_language.oid;lanname | lanispl | lanpltrusted | lanplcallfoid | lancompiler ----------+---------+--------------+---------------+-------------internal | f | f | 0 | n/aC | f | f | 0 | /bin/ccsql | f | f | 0 | postgres (3 rows) test=> select * from pg_language p, pg_language q where p.oid = q.oid;lanname | lanispl | lanpltrusted | lanplcallfoid |lancompiler | lanname | lanispl | lanpltrusted | lanplcallfoid | lancompiler ----------+---------+--------------+---------------+-------------+----------+---------+--------------+---------------+-------------internal |f | f | 0 | n/a | internal | f | f | 0 | n/aC |f | f | 0 | /bin/cc | C | f | f | 0 | /bin/ccsql | f | f | 0 | postgres | sql | f | f | 0 | postgres (3 rows) -- 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
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
At 11:12 AM 1/23/00 -0500, Tom Lane wrote: >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. Yes, since it is joining the two tables it should be returning all columns of the join. >This may go a long way towards explaining why people have been so >readily confused by the implicit-FROM-clause business! It *is* confusing, that's for sure! > 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. Is this feature something many folks depend upon? It appears that at least some of the folks who hang out on this list aren't entirely clear on this implicit "from" clause thing - if that's true, how many users who are out of the loop understand it? It isn't that burdensome to explictly list a table again in the from list. The query's certainly more readable and portable for folks who are (say) trying to port PostgreSQL-based stuff to (say) Oracle. On the other hand, if a lot of folks depend on this (somewhat dubious, IMHO) feature then I suppose it should be preserved - with "*" expansion fixed, presumably! - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
>> 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.
I thought that the behavior had something to do with the fact that
the underlying table was the same for both the explicit and the implicit
rangetable entry, but not so. In fact, ExpandAllTables() deliberately
refrains from expanding implicit entries, and always has AFAICT:
/* * we only expand those listed in the from clause. (This will * also prevent us from using the
wrongtable in inserts: eg. * tenk2 in "insert into tenk2 select * from tenk1;") */ if
(!rte->inFromCl) continue;
I think the comment in parentheses is no longer applicable, but there
are other sources of implicit rangetable entries besides this particular
feature --- rules and set functions both seem to add them. So I'm not
sure what we might break by changing it.
I'm inclined to leave this behavior alone, and go back to the idea of
emitting warning notices when an implicit FROM clause is added.
regards, tom lane
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
OK, seems it is resolved. We now throw a warning for the query. > >> 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. > > I thought that the behavior had something to do with the fact that > the underlying table was the same for both the explicit and the implicit > rangetable entry, but not so. In fact, ExpandAllTables() deliberately > refrains from expanding implicit entries, and always has AFAICT: > > /* > * we only expand those listed in the from clause. (This will > * also prevent us from using the wrong table in inserts: eg. > * tenk2 in "insert into tenk2 select * from tenk1;") > */ > if (!rte->inFromCl) > continue; > > I think the comment in parentheses is no longer applicable, but there > are other sources of implicit rangetable entries besides this particular > feature --- rules and set functions both seem to add them. So I'm not > sure what we might break by changing it. > > I'm inclined to leave this behavior alone, and go back to the idea of > emitting warning notices when an implicit FROM clause is added. > > 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
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Did this get resolved somehow?
>>
>> ... 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.
Looks like the behavior is still the same (except now it says
NOTICE: Adding missing FROM-clause entry for table pg_language
as well). I'm inclined to say we should change it, and am willing
to do the work if no one objects...
regards, tom lane
> >> 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. > > Looks like the behavior is still the same (except now it says > NOTICE: Adding missing FROM-clause entry for table pg_language > as well). I'm inclined to say we should change it, and am willing > to do the work if no one objects... Which part, the expansion of * or the warning code? -- 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
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> Looks like the behavior is still the same (except now it says
>> NOTICE: Adding missing FROM-clause entry for table pg_language
>> as well). I'm inclined to say we should change it, and am willing
>> to do the work if no one objects...
> Which part, the expansion of * or the warning code?
Changing expansion of * to include the implicit FROM clause entries
was what I was thinking of. However, given that we have the NOTICE,
maybe it's not necessary to do it. Let's leave the code as it is
for now and see what people think of it in actual use.
regards, tom lane