Equivalence of CROSS JOIN and comma

[Adam Mackler]

Hi:

The PostgreSQL manual [1] reads in part:

"FROM T1 CROSS JOIN T2 is equivalent to FROM T1, T2."

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. To my way of thinking, they are not
equivalent, which makes me wonder if I am misunderstanding the quoted
text above to mean that both statements should have the same result.

What am I missing?

I am using version 9.2.0.

Thanks very munch in advance.

[1] http://www.postgresql.org/docs/9.2/interactive/queries-table-expressions.html#QUERIES-JOIN

[Tom Lane]

Post by Adam Mackler
"FROM T1 CROSS JOIN T2 is equivalent to FROM T1, T2."

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

Post by Adam Mackler
Yet here are two statements that are identical to each other except
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

[Adam Mackler]

That makes perfect sense. Thank you for clearing that up.

As an aside, it doesn't seem that section 7.2.1.1 of the manual
explains what you just said. I don't know whether or not it's in
another section, but 7.2.1.1 is where I looked since it's the only
reference in the index under "cross join" and there's no index entry
for comma, either spelt out or as a symbol, including among the
entries under "join." I don't think it would be inappropriate to
mention the difference in binding precedence as a qualification of the
cross-join-comma equivalence statement that I quoted in my original
post.

Also, now knowing what you just explained, I looked again at the
manual and came across section 14.3, which, if not directly on point,
does seems very closely related. In particular the fourth highlighted
example (looking at the HTML version) strongly implies the same
equivalence by being preceded with the words "...these three queries are
logically equivalent:" and including three queries, two of which are
the same as each other but for cross-joins versus commas.

While it seems less likely that someone confused at the behavior that
I was asking about, and who doesn't suspect it's a matter of binding
precedence will find their way to section 14.3 as to 7.2.1.1, it does
seem highly probably that someone who is reading section 14.3 for
whatever reason would be of the mind to want to know about this
distinction between the binding of cross-joins and commas. It might
not be inappropriate to mention it there in section 14.3 as well as in
section 7.2.1.1.

Than you again for your prompt and effective response to my question.

Adam Mackler

Post by Tom Lane

Post by Adam Mackler
"FROM T1 CROSS JOIN T2 is equivalent to FROM T1, T2."

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

Post by Adam Mackler
Yet here are two statements that are identical to each other except
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