Re: Table Alias

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.