mysql - 为什么这个在 MySQL4 中有效的查询在 MySQL5 中不起作用？

Question

最近将数据库从 MySQL 4 升级到 MySQL 5。以下查询现已损坏:

SELECT COUNT(*) FROM Customer, Product INNER JOIN Orders 
ON (Customer.Email = Orders.Email)
WHERE NOT (Product.Flags & 8) AND 
    Customer.CustomerNumber = Product.CustomerNumber AND
    Truncate(Orders.ProductId/10, 0) = Truncate(Product.ProductId/10, 0)

出现以下错误:

#1054 - Unknown column 'Customer.Email' in 'on clause' 

这个查询不是我写的，我也不是 SQL 高手，但我猜这可能是 ANSI-89/ANSI-92 风格连接的混合体，可能存在某种优先级问题上。

您能否解释错误，描述此查询有什么问题，以及如何正确指定联接？

Answer 1

根据 JOIN Syntax 记录:

 Join Processing Changes in MySQL 5.0.12

[ deletia ]

The following list provides more detail about several effects of current join processing versus join processing in older versions. The term “previously” means “prior to MySQL 5.0.12.”

[ deletia ]

• Previously, 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.

  Example:

  CREATE TABLE t1 (i1 INT, j1 INT);
  CREATE TABLE t2 (i2 INT, j2 INT);
  CREATE TABLE t3 (i3 INT, j3 INT);
  INSERT INTO t1 VALUES(1,1);
  INSERT INTO t2 VALUES(1,1);
  INSERT INTO t3 VALUES(1,1);
  SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);
  

  Previously, the SELECT was legal due to the implicit grouping of t1,t2 as (t1,t2). Now the JOIN takes precedence, so the operands for the ON clause are t2 and t3. Because t1.i1 is not a column in either of the operands, the result is an Unknown column 't1.i1' in 'on clause' error. To allow the join to be processed, group the first two tables explicitly with parentheses so that the operands for the ON clause are (t1,t2) and t3:

  SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);
  

  Alternatively, avoid the use of the comma operator and use JOIN instead:

  SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);
  

  This change also applies to statements that mix the comma operator with INNER JOIN, CROSS JOIN, LEFT JOIN, and RIGHT JOIN, all of which now have higher precedence than the comma operator.