Re: BUG #12000: "CROSS JOIN" not equivalent to ","

Tom Lane-2 wrote
> kunert@.hu-berlin

>  writes:
>> Still not sure if this is really a bug, but I could not find a good
>> explanation for the following behaviour:
>
>> According to the documentation
>> http://www.postgresql.org/docs/9.3/static/queries-table-expressions.html
>> (7.2.1.1):
>> "FROM T1 CROSS JOIN T2 is equivalent to FROM T1, T2"
>
>> However, in the following example the SQL-Query using "CROSS JOIN" works,
>> while the usage of "," results in an invalid reference:
>
> It's not a bug.  The quoted statement is correct as far as it goes,
> but what it fails to point out is that CROSS JOIN has a different
> syntactic priority from ",", which matters when you have more than
> two tables.  Fully parenthesized, your examples would look like
>
>> from
>>     ((table1 a cross join table1 b)
>>      left outer join table2 c on a.x = y and b.x = z);
>
>> from
>>     table1 a
>>     , (table1 b left outer join table2 c on a.x = y and b.x = z);
>
> so that table "a" is within the scope of the ON clause in the first case
> but not the second.
>
> I'm not sure we ought to dive into these syntactic details right at
> that point in the manual, though; it's not really on point for what
> we're trying to explain there, and could easily make the text less
> comprehensible not more so.
>
> A simple "fix" would be to remove the claim about "," and just compare
> CROSS JOIN to INNER JOIN ON TRUE.  I'm not really convinced that's an
> improvement ...

How about adding the following to that sentence:

"However, in the presence of three or more joined relations it is
recommended to only use either explicit joins or commas since mixing them
introduces non-obvious join order differences."

David J.




--
View this message in context:
http://postgresql.nabble.com/BUG-12000-CROSS-JOIN-not-equivalent-to-tp5827394p5827399.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.