Re: Table Alias

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

In response to

Browse pgsql-general by date

  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