[thelist] join syntax change in mysql (as of ver 5.0.12)

[Sarah Adams]

<tip type="MySQL">
Just spent a few hours debugging a mysterious query problem (preceded by
a database upgrade) before discovering that it was caused by a change in
the MySQL JOIN syntax. From mysql.com:

"[Prior to MySQL 5.0.12], the comma operator (,) and JOIN both had the
same precedence, so the join expression t1, t2 JOIN t3 was interpreted
as ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression
is interpreted as (t1, (t2 JOIN t3)). This change affects statements
that use an ON clause, because that clause can refer only to columns in
the operands of the join, and the change in precedence changes
interpretation of what those operands are."

So this query:
  SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);
should be rewritten as:
  SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);
or:
  SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);
</tip>

-- 
sarah adams
web developer & programmer
portfolio: http://sarah.designshift.com
blog: http://hardedge.ca