Re: [HACKERS] Curiously confused query parser.

Gene Sokolov wrote:

> And this one is invalid:
>
> test=> select o1.id from ord as o1, ord as o2 where o1.pos>2 and o2.pos<2
> test-> and o1.tp=o2.tp and ord.id>3;
> id
> --
>  5
>  5
>  3
>  3
> (4 rows)
>
> This query should probably fail instead of returning an invalid result. MS
> SQL 6.5 does just that:
>
> Msg 107, Level 16, State 3
> The column prefix 'ord' does not match with a table name or alias name used
> in the query.

    Seems   PostgreSQL  tries  to  be  a  little  too  smart.  It
    automatically adds another rangetable entry for ORD,  so  the
    query is executed as

    test=> select o1.id from ord as o1, ord as o2, ord as auto_rte
    test->    where o1.pos>2 and o2.pos<2
    test->    and o1.tp=o2.tp and auto_rte.id>3;

    For this query, the result is O.K.

    I  don't know if this is according to the SQL specs and MS is
    wrong, or if PostgreSQL is violating the specs. Thomas?


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #