Re: Statement parsing problem ?

G'day,

There seems to be a kind of statement parsing problem in 7.4.5
(from debian postgresql-7.4.5-3, i386).

Either that, or I'm missing something...

The following script:

----------------------------------------------------------------------
create table t1 ( foo1 integer, foo2 integer );
create table t2 ( foo3 integer );
create table t3 ( foo4 integer, foo5 integer );
create table t4 ( foo6 integer );

\echo
\echo -----------
\echo this works
\echo -----------

select 1 as "OK"
from
t1,
t2,
t3
join t4 on (t4.foo6 = t3.foo5)
where t2.foo3 = t1.foo1
and t3.foo4 = t1.foo2 ;

\echo
\echo ------------
\echo Error, from simply swapping the order of t2 and t3 ???
\echo ------------

select 1
from
t1,
t3,
t2
join t4 on (t4.foo6 = t3.foo5)
where t2.foo3 = t1.foo1
and t3.foo4 = t1.foo2 ;

\echo
\echo ------------
\echo slightly different error, using a table alias
\echo ------------

select 1
from
t1,
t3 a,
t2
join t4 on (t4.foo6 = a.foo5)
where t2.foo3 = t1.foo1
and a.foo4 = t1.foo2 ;
----------------------------------------------------------------------

produces the output:

----------------------------------------------------------------------
-----------
this works
-----------
OK
----
(0 rows)

------------
Error, from simply swapping the order of t2 and t3 ???
------------
psql:/tmp/test.sql:32: NOTICE: adding missing FROM-clause entry for table "t3"
psql:/tmp/test.sql:32: ERROR: JOIN/ON clause refers to "t3", which is not part of JOIN

------------
slightly different error, using a table alias
------------
psql:/tmp/test.sql:46: ERROR: relation "a" does not exist

----------------------------------------------------------------------

So is it me, or is this just a bit borken ?

Cheers,

Chris.

On Sep 15, 2004, at 9:43 AM, Chris Dunlop wrote:

> Either that, or I'm missing something...

From the SELECT docs ...

A JOIN clause combines two FROM items. Use parentheses if necessary
to determine the order of nesting. In the absence of parentheses,
JOINs nest left-to-right. In any case JOIN binds more tightly than the
commas separating FROM items.

CROSS JOIN and INNER JOIN produce a simple Cartesian product, the
same result as you get from listing the two items at the top level of
FROM, but restricted by the join condition (if any). CROSS JOIN is
equivalent to INNER JOIN ON (TRUE), that is, no rows are removed by
qualification. These join types are just a notational convenience,
since they do nothing you couldn't do with plain FROM and WHERE.
---

Since you're doing a simple join, you'd be better off using form

select 1 as "OK" from t1, t2, t3, t4 on where t4.foo6 = t3.foo5 and
t2.foo3 = t1.foo1 and t3.foo4 = t1.foo2 ;

and then you can vary the order of the and clauses any way you like.

But using the "FROM t1, t2, t3 JOIN t4" form binds left-to-right tigher
than the comma separated list, so it is operating on exactly two tables
(t3 and t4), not the t1, t2, t3 cartesian product joined with t4.

----
James Robinson
Socialserve.com

Replying to my own post, thanks to the assistance of Paul
Bort...

On Wed, Sep 15, 2004 at 11:43:47PM +1000, Chris Dunlop wrote:
> There seems to be a kind of statement parsing problem in 7.4.5
> (from debian postgresql-7.4.5-3, i386).
>
> Either that, or I'm missing something...
>
> \echo ------------
> \echo Error, from simply swapping the order of t2 and t3 ???
> \echo ------------
>
> select 1
> from
> t1,
> t3,
> t2
> join t4 on (t4.foo6 = t3.foo5)
> where t2.foo3 = t1.foo1
> and t3.foo4 = t1.foo2 ;

I'd always thought:

FROM t1, t2 join t3

meant:

FROM (t1, t2) join t3

but as Paul pointed out, it's actually:

FROM t1, (t2 join t3)

I.e. in the example above:

t2 join t4 on (t4.foo6 = t3.foo5)

doesn't work because there's no t3.foo5 on the left of the join.

> So is it me, or is this just a bit borken ?

It was me!

Cheers,

Chris.