Обсуждение: 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.