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(at)megazone23(dot)bigpanda(dot)com>
>To: Darrin Ladd <darrin_ladd(at)hotmail(dot)com>
>CC: pgsql-general(at)postgreSQL(dot)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.