Re: Table Alias

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