Re: Equivalence of CROSS JOIN and comma

Adam Mackler <pgsql-novice@mackler.org> writes:
> The PostgreSQL manual [1] reads in part:
> "FROM T1 CROSS JOIN T2 is equivalent to FROM T1, T2."

That's true as far as it goes ...

> Yet here are two statements that are identical to each other except
> that one has a CROSS JOIN where the other has a comma:

>   WITH t1 (val)  AS ( VALUES (1) ),
>        t2 (name) AS ( VALUES ('foo') ),
>        t3 (num)  AS ( VALUES (1) )
>   SELECT * FROM t1 CROSS JOIN t2 JOIN t3 ON num=val;

>   WITH t1 (val)  AS ( VALUES (1) ),
>        t2 (name) AS ( VALUES ('foo') ),
>        t3 (num)  AS ( VALUES (1) )
>   SELECT * FROM t1, t2 JOIN t3 ON num=val;

> and one works but the other doesn't.

The issue there is that JOIN binds tighter than comma.  The first one
means

    ((t1 CROSS JOIN t2) JOIN t3 ON num=val)

and the second one means

    t1, (t2 JOIN t3 ON num=val)

which is equivalent to

    (t1 CROSS JOIN (t2 JOIN t3 ON num=val))

so the reference to t1.val fails because t1 isn't part of the JOIN that
the ON condition is attached to.

People migrating from MySQL tend to get this wrong because MySQL gets it
wrong :-(, or at least it did in older versions --- I've not checked it
lately.  The SQL standard is perfectly clear about this though.

            regards, tom lane