Craig Dunn wrote:
Hi,
I'm trying to migrate an application from 4.1 to 5.1, theres a bunch of
queries that seem to be failing and it looks like the order of INNER
JOIN's... for example...
SELECT....etc
INNER JOIN tablex AS x ON y.foo = a.bar
INNER JOIN tablea AS a ON y.foo = b.bar
... works in 4.1, but in 5.1 I get unknown column a.bar, but if I switch
the order of these joins around it works fine. Is this a known
compatibility issue between the major versions? and is there a work
around apart from changing the SQL (which we're trying to avoid where
possible!)
Yes, it's a known compatibility issue. With release 5.0.12, MySQL became
more compliant with the SQL standard which meant that the order of
operations in the FROM...JOIN clauses changed and the comma operator was
demoted.
It's all documented here:
http://dev.mysql.com/doc/refman/5.0/en/join.html
scan down about midway and look for
~~~~
Join Processing Changes in MySQL 5.0.12
Beginning with MySQL 5.0.12, natural joins and joins with USING,
including outer join variants, are processed according to the SQL:2003
standard.
~~~~
Yours,
--
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
