Thread: Table Alias
Through the help of some inexperienced embedded SQL coders I stubled across a strang Postgres quirk. I am wondering if this is normal or if I have configured something incorrectly. Here's the situation... The following two selects return very different values: select b.name from foo f, bar b where f.foo_id = 1 and f.bar_id = b.bar_id; select bar.name from foo f, bar b where f.foo_id = 1 and f.bar_id = b.bar_id; The first returns the rows in the name column of bar that have the same bar_id as the foo record with foo_id = 1. (Correct) The second returns all rows in the name column of the bar table. (?) I am quite aware that if you declare an alias for a table that you should use it, but I am very surprised that the select becomes unqualified if you mistakenly use the full table name. Is this normal behavior? Thanks! Darrin _________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. Share information about yourself, create your own public profile at http://profiles.msn.com.
On Tue, 29 Aug 2000, Darrin Ladd wrote:
> Through the help of some inexperienced embedded SQL coders I stubled across
> a strang Postgres quirk. I am wondering if this is normal or if I have
> configured something incorrectly. Here's the situation...
>
> The following two selects return very different values:
>
> select b.name from foo f, bar b
> where f.foo_id = 1
> and f.bar_id = b.bar_id;
>
> select bar.name from foo f, bar b
> where f.foo_id = 1
> and f.bar_id = b.bar_id;
>
> The first returns the rows in the name column of bar that have the same
> bar_id as the foo record with foo_id = 1. (Correct)
>
> The second returns all rows in the name column of the bar table. (?)
>
> I am quite aware that if you declare an alias for a table that you should
> use it, but I am very surprised that the select becomes unqualified if you
> mistakenly use the full table name. Is this normal behavior?
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...
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.
-----
<table reference> ::=
<table name> [ [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ] ]
Syntax Rules
1) A <correlation name> immediately contained in a <table refer-
ence> TR is exposed by TR. A <table name> immediately contained
in a <table reference> TR is exposed by TR if and only if TR
does not specify a <correlation name>.
Thanks, that makes sense. Although, I'd much rather have an error then the wrong data. :( Cheers, Darrin >From: Stephan Szabo <sszabo@megazone23.bigpanda.com> >To: Darrin Ladd <darrin_ladd@hotmail.com> >CC: pgsql-general@postgreSQL.org >Subject: Re: [GENERAL] Table Alias >Date: Tue, 29 Aug 2000 17:48:57 -0700 (PDT) > > >On Tue, 29 Aug 2000, Darrin Ladd wrote: > > > Through the help of some inexperienced embedded SQL coders I stubled >across > > a strang Postgres quirk. I am wondering if this is normal or if I have > > configured something incorrectly. Here's the situation... > > > > The following two selects return very different values: > > > > select b.name from foo f, bar b > > where f.foo_id = 1 > > and f.bar_id = b.bar_id; > > > > select bar.name from foo f, bar b > > where f.foo_id = 1 > > and f.bar_id = b.bar_id; > > > > The first returns the rows in the name column of bar that have the same > > bar_id as the foo record with foo_id = 1. (Correct) > > > > The second returns all rows in the name column of the bar table. (?) > > > > I am quite aware that if you declare an alias for a table that you >should > > use it, but I am very surprised that the select becomes unqualified if >you > > mistakenly use the full table name. Is this normal behavior? > >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... > >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. > >----- > <table reference> ::= > <table name> [ [ AS ] <correlation name> > [ <left paren> <derived column list> <right paren> ] ] > Syntax Rules > 1) A <correlation name> immediately contained in a <table refer- > ence> TR is exposed by TR. A <table name> immediately contained > in a <table reference> TR is exposed by TR if and only if TR > does not specify a <correlation name>. > _________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. Share information about yourself, create your own public profile at http://profiles.msn.com.
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