Bug #25342 Select with left join error

• Submitted: 30 Dec 2006 23:56
• Modified: 31 Dec 2006 0:17
• Reporter: Anton Levshunov
• Status: Not a Bug
• Category: MySQL Server: Parser
• Severity: S1 (Critical)
• Version: 5.0.27
• OS: Windows (Win)
• CPU Architecture: Any

[30 Dec 2006 23:56] Anton Levshunov

Description:
select *
from table1 t1, table2 t2
	left join table3 t3 on t3.t1_id = t1.id
where t1.t2_id = t2.id

return error, but in older version (4.1) it's work.

select *
from table2 t2, table1 t1
	left join table3 t3 on t3.t1_id = t1.id
where t1.t2_id = t2.id

return correctly data

How to repeat:
Just query this:

select *
from table1 t1, table2 t2
	left join table3 t3 on t3.t1_id = t1.id
where t1.t2_id = t2.id

Test tables:

CREATE TABLE `table1` (                  
          `id` int(11) NOT NULL auto_increment,  
          `t2_id` int(11) default NULL,          
          `t3_id` int(11) default NULL,          
          PRIMARY KEY  (`id`)                    
        ) ENGINE=MyISAM DEFAULT CHARSET=cp1251   

CREATE TABLE `table2` (                 
          `id` int(11) NOT NULL auto_increment,    
          `t1_id` int(11) default NULL,         
          `t3_id` int(11) default NULL,         
          PRIMARY KEY  (`id`)                   
        ) ENGINE=MyISAM DEFAULT CHARSET=cp1251  

CREATE TABLE `table3` (                 
          `id` int(11) NOT NULL auto_increment,    
          `t1_id` int(11) default NULL,         
          `t2_id` int(11) default NULL,         
          PRIMARY KEY  (`id`)                   
        ) ENGINE=MyISAM DEFAULT CHARSET=cp1251

[31 Dec 2006 0:17] MySQL Verification Team

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read the Manual about the changes done in the Join syntax from
version 5.0.12.

mysql> select *
    -> from table1 t1, table2 t2
    ->  left join table3 t3 on t3.t1_id = t1.id
    -> where t1.t2_id = t2.id;
ERROR 1054 (42S22): Unknown column 't1.id' in 'on clause'
mysql> select *
    -> from (table1 t1, table2 t2)
    ->  left join table3 t3 on t3.t1_id = t1.id
    -> where t1.t2_id = t2.id;
Empty set (0.39 sec)