From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
Cc: | Darrin Ladd <darrin_ladd(at)hotmail(dot)com>, pgsql-general(at)postgreSQL(dot)org |
Subject: | Re: Table Alias |
Date: | 2000-08-30 04:06:48 |
Message-ID: | 18040.967608408@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> Well, technically by SQL92 I believe the second query should be an error
> since the table reference "bar b" should not be exporting the name "bar"
> unless I'm misreading the spec...
Correct. SQL sez that "FROM bar b" exposes the correlation name "b",
but *NOT* the underlying table name "bar" for references from the
rest of the query. Otherwise self-joins like "FROM bar b1, bar b2"
would be ambiguous.
> Postgres tries to be helpful by assuming you meant to put it in the from
> list and adds it internally, so the second query is effectively:
> select bar.name from foo f, bar b, bar
> where f.foo_id=1 and f.bar_id=b.bar_id.
Or to be even clearer, "FROM foo f, bar b, bar bar" is what you get;
ie, two scans of the same table under different correlation names.
The reason Postgres accepts the bare reference "bar.name" and adds
an implicit FROM-item is that this is what the original Berkeley
POSTQUEL language did, and no one's wanted to break backwards
compatibility to that extent just to generate the same error a
pure SQL implementation would do.
We have recently compromised to the extent that current sources
(7.1-to-be) will issue a warning notice on this query:
NOTICE: Adding missing FROM-clause entry for table bar
and after doing that for a release or three we'll probably go
over to the hard-line SQL interpretation. We see enough questions
about this point that it's become clear the POSTQUEL semantics are
too confusing for SQL-born-and-bred programmers...
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Val Trifonoff | 2000-08-30 04:37:47 | User_Permissions |
Previous Message | Ian Turner | 2000-08-30 02:37:28 | Re: SQL scripts - sequences |