Re: Table Alias

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>.