Stephan Szabo <sszabo@megazone23.bigpanda.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
