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

[29 Sep 2005 7:39] Timour Katchaounov
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'
[30 Sep 2005 7:39] Bugs System
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
[30 Sep 2005 14:10] Timour Katchaounov
Fixed in 5.0.15
[5 Oct 2005 23:57] Paul Dubois
Noted in 5.0.15 changelog.