MySQL Bugs: #14047: Unknown column when column exists

Bug #14047	Unknown column when column exists
Submitted:	15 Oct 2005 15:16	Modified:	15 Oct 2005 15:29
Reporter:	Tomas Prochazka	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.13-rc	OS:	Windows (Windows XP SP2)
Assigned to:		CPU Architecture:	Any

Description:
MySQL 5.0.13-rc can't find existing column. I tested it on MySQL 4.0.20-st and it works. I add test SQL to thid bug error.

This error write my my MySQL 5.0.13:
#1054 - Unknown column 'n.id' in 'on clause'

I use phpMyAdmin, PHP 5.0.4 on the Windows XP SP2.

Comment:
In MySQL 5.x also don't work LIKE 'some%' for me. But this will be mayby problem only for me, but I don't  why :-(

How to repeat:
Here is test case SQL commands:

DROP TABLE IF EXISTS `portal_users`;
CREATE TABLE IF NOT EXISTS `portal_users` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `email` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

DROP TABLE IF EXISTS `seznamka_persons`;
CREATE TABLE IF NOT EXISTS `seznamka_persons` (
  `id` int(10) unsigned NOT NULL auto_increment, 
  `title` varchar(20) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

DROP TABLE IF EXISTS `seznamka_atributes`;
CREATE TABLE IF NOT EXISTS `seznamka_atributes` (
  `id_person` int(10) unsigned NOT NULL default '0',
  `name` varchar(200) NOT NULL,
  `value` tinytext NOT NULL
) ENGINE=MyISAM;
        

SELECT * FROM seznamka_persons n,portal_users p
LEFT JOIN `seznamka_atributes` a ON n.id = a.id_person
WHERE n.id = p.id        

Suggested fix:
When I swap 'seznamka_persons n' and 'portal_users p' in SELECT command, it works, but I think that this must work in both case.

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:

Yes, this is new, but correct behaviour ob 5.0.12 and newer versions. It is documented (http://dev.mysql.com/doc/refman/5.0/en/join.html):

"This means that some queries that appeared to work correctly prior to 5.0.12 must be rewritten to comply with the standard. For example, a query of the form

SELECT t1.id,t2.id,t3.id 
    FROM t1,t2 
    LEFT JOIN t3 ON (t3.id=t1.id)
    WHERE t1.id=t2.id;

will be interpreted in MySQL 5.0.12 and later as

SELECT t1.id,t2.id,t3.id 
    FROM t1,(  t2 LEFT JOIN t3 ON (t3.id=t1.id)  )
    WHERE t1.id=t2.id;

Such a query must now be written like so:

SELECT t1.id,t2.id,t3.id 
    FROM (t1,t2) 
    LEFT JOIN t3 ON (t3.id=t1.id)
    WHERE t1.id=t2.id;"

It is just what you had reported about...

Sorry and thanks you. And in LIKE syntax is also changes?

Tomas, if you have a testcase that shows problem with LIKE, please submit another bugreport