Russ Brown <pickscrape@xxxxxxxxx> writes: > On Fri, 2006-05-05 at 12:49 -0400, Tom Lane wrote: >> "Fred" <frederic.fleche@xxxxxxxxx> 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 version for the right syntax to use near 'on (a1=b1)' at line 1 Wonderful :-( regards, tom lane
