MySQL Bugs: #13597: Column in ON condition not resolved if references a table in nested right join

Bug #13597	Column in ON condition not resolved if references a table in nested right join
Submitted:	29 Sep 2005 7:39	Modified:	5 Oct 2005 23:57
Reporter:	Timour Katchaounov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.12	OS:	Any (any)
Assigned to:	Timour Katchaounov	CPU Architecture:	Any

Description:
A column in the ON condition of a join that references a table
in a nested join cannot be resolved if the nested join is a
right join.

How to repeat:
drop table if exists a, b, c;
create table a (
  id int(11) not null default '0'
) engine=myisam default charset=latin1;

insert into a values (123),(191),(192);

create table b (
  id char(16) character set utf8 not null default ''
) engine=myisam default charset=latin1;

insert into b values ('58013'),('58014'),('58015'),('58016');

create table c (
  a_id int(11) not null default '0',
  b_id char(16) character set utf8 default null
) engine=myisam default charset=latin1;

insert into c values
(123,null),(123,null),(123,null),(123,null),(123,null),(123,'58013');

-- With LEFT JOIN, it works OK
select count(*) from a inner join (c left join b on b.id = c.b_id) on a.id =
c.a_id;

-- With RIGHT JOIN, it gives an error
select count(*) from a inner join (b right join c on b.id = c.b_id) on a.id =
c.a_id;

ERROR 1054 (42S22) at line 22: Unknown column 'c.a_id' in 'on clause'

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30541

Fixed in 5.0.15

Noted in 5.0.15 changelog.