Bug #14297 Query problems
Submitted: 25 Oct 2005 14:54 Modified: 25 Oct 2005 15:13
Reporter: Davila Camargo Junior Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.15 OS:Windows (Windows XP / Windows Server 2003)
Assigned to: CPU Architecture:Any

[25 Oct 2005 14:54] Davila Camargo Junior
Description:
This SELECT works correctly in MySQL 4.1, but  
it presents problems in JOIN in the version 5.0.

SELECT UF1.Nome, Pais1.Sigla, UF2.Nome, Pais2.Sigla
FROM
  (SELECT Id, Nome, Pais_Id FROM UF) AS UF1,
  (SELECT Id, Nome, Pais_Id FROM UF) AS UF2
JOIN Pais AS Pais1 ON Pais1.Id = UF1.Pais_Id
JOIN Pais AS Pais2 ON Pais2.Id = UF2.Pais_Id
WHERE UF1.Id <> UF2.Id
ORDER BY UF1.Nome, UF2.Nome

other,

SELECT CidadeA.Nome, CidadeB.Nome
FROM Cidade CidadeA, Cidade CidadeB
JOIN UF ON UF.Id = CidadeA.UF_Id

How to repeat:
This SELECT works correctly in MySQL 4.1, but  
it presents problems in JOIN in the version 5.0.

SELECT UF1.Nome, Pais1.Sigla, UF2.Nome, Pais2.Sigla
FROM
  (SELECT Id, Nome, Pais_Id FROM UF) AS UF1,
  (SELECT Id, Nome, Pais_Id FROM UF) AS UF2
JOIN Pais AS Pais1 ON Pais1.Id = UF1.Pais_Id
JOIN Pais AS Pais2 ON Pais2.Id = UF2.Pais_Id
WHERE UF1.Id <> UF2.Id
ORDER BY UF1.Nome, UF2.Nome

other,

SELECT CidadeA.Nome, CidadeB.Nome
FROM Cidade CidadeA, Cidade CidadeB
JOIN UF ON UF.Id = CidadeA.UF_Id
[25 Oct 2005 15:13] Valeriy Kravchuk
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Please, read the manual carefully (http://dev.mysql.com/doc/refman/5.0/en/join.html):

"Before MySQL 5.0.12, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was intrepreted 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);

Prior to 5.0.12, the SELECT is legal due to the implicit grouping of t1,t2 as (t1,t2). From 5.0.12 on, 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);

This change also applies to INNER JOIN, CROSS JOIN, LEFT JOIN, and RIGHT JOIN."

Looks like it exactly describes your problem and a way to solve it.