Russ Brown <pickscrape@gmail.com> writes:
> On Fri, 2006-05-05 at 12:49 -0400, Tom Lane wrote:
>> "Fred" <frederic.fleche@gmail.com> writes:
>>> SELECT g.id, t1.name, substring(g.path, 1, (6*(-1+l.id)) + 5) as
>>> subpath,l.id-1 as level
>>> FROM graph_path g
>>> INNER JOIN term AS t1
>>> INNER JOIN term AS t2 ON (t2.id = g.term2_id)
>>> INNER JOIN levels l ON (substring(path, 1+(6*(-1+l.id)), 5) = t1.id
>>> AND g.distance+1 >= l.id)
>>> WHERE t2.name = 'blood_coagulation' and g.term1_id=1
>>> ORDER BY g.id, subpath;
>>
>> Does MySQL really accept that as-is? (Standards compliance was never
>> their strong point :-()
> Yes, MySQL (4.1.14) quite happily accepts that as-is...
[ tries it... ] Hm, 5.0.x is no better. This is really bad, because it
shows that they completely misimplemented the JOIN syntax. The above
query is ambiguous because it's not clear which JOIN each ON is supposed
to go with. Per spec, you can write something like
FROM a JOIN b JOIN c ON b_c_cond ON a_bc_cond
which is supposed to be parenthesized as
FROM a JOIN (b JOIN c ON b_c_cond) ON a_bc_cond
so that the conditions are associated with the joins I named them after.
If you parse things so that ON is optional then it's completely unclear
which JOINs the ONs that are there are supposed to go with. And this
matters, particularly for outer joins.
mysql> select * from a join b left join c on (b1=c1) on (a1=b1);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server
versionfor the right syntax to use near 'on (a1=b1)' at line 1
Wonderful :-(
regards, tom lane