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(at)debis(dot)com (Jan Wieck) #