Thread: Curiously confused query parser.

Welcome to the POSTGRESQL interactive sql monitor: Please read the file COPYRIGHT for copyright terms of POSTGRESQL
[PostgreSQL 6.6.0 on i386-unknown-freebsd3.2, compiled by gcc 2.7.2.1]

(a side note - wouldn't it be helpful to have a little more info about the
build, namely its time stamp and/or the CVS time stamp)

test=> \d ord
Table    = ord
+----------------------------------+----------------------------------+-----
--+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-----
--+
| id                               | int4                             |
4 |
| pos                              | int4                             |
4 |
| tp                               | int4                             |
4 |
+----------------------------------+----------------------------------+-----
--+

test=> select * from ord;
id|pos|tp
--+---+--1|  1| 12|  2| 13|  3| 24|  1| 25|  3| 1
(5 rows)

This query is fine:

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;
id
--53
(2 rows)

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

Gene Sokolov

"Gene Sokolov" <hook@aktrad.ru> writes:
> This query is fine:

> 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;
> id
> --
>  5
>  3
> (2 rows)

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

It's not invalid, at least not according to Postgres' view of the world;
your reference to ord.id adds an implicit "FROM ord AS ord" to the FROM
clause, turning the query into a 3-way join.  The output is correct for
that interpretation.

Implicit FROM clauses are a POSTQUEL leftover that is not to be found
in the SQL92 spec.  There's been some talk of emitting a warning message
when one is added, because we do regularly see questions from confused
users.  But if we took the feature out entirely, we'd doubtless break
some existing applications :-(
        regards, tom lane

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) #