Bug #11285 left join problem
Submitted: 13 Jun 2005 10:44 Modified: 13 Jul 2005 18:59
Reporter: Yoshiaki Tajika (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0 OS:Any (*)
Assigned to: Igor Babaev CPU Architecture:Any

[13 Jun 2005 10:44] Yoshiaki Tajika 
Description:
left join problem.
(I'm sorry I can't describe it simply.)

How to repeat:
create table t1(c11 int) engine=myisam;
create table t2(c21 int) engine=myisam;
insert into t1 values (30), (40), (50);
insert into t2 values (300), (400), (500);

select t1.*, t2.* from t1 left join t2
on(t1.c11=t2.c21 and t2.c21= 30 ) where t1.c11=40;

  --  Odd result.
  --  +------+------+
  --  | c11  | c21  |
  --  +------+------+
  --  |   40 |  300 |
  --  |   40 |  400 |
  --  |   40 |  500 |
  --  +------+------+
  --  3 rows in set (0.00 sec)

select t1.*, t2.* from t1 left join t2
on(t1.c11=t2.c21 and t2.c21='30') where t1.c11=40;

  --  Correct result.
  --  +------+------+
  --  | c11  | c21  |
  --  +------+------+
  --  |   40 | NULL |
  --  +------+------+
  --  1 row in set (0.00 sec)

The former query is more correct, because c21 is an integer column.
I expect the former works correctly like the latter.
[13 Jun 2005 12:07] Aleksey Kishkin 
tested on win xp 5.0.6
[15 Jun 2005 9:02] 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/26015
[15 Jun 2005 14:57] Igor Babaev 
ChangeSet
  1.1936 05/06/15 02:02:25 igor@rurik.mysql.com +3 -0
  join_outer.result, join_outer.test:
    Added a test case for bug #11285.
  sql_select.cc:
    Fixed bug #11285.
    The problem occurred with Item_equal in an 'on expression'
    that was evaluated to false.

The fix will appear in 5.0.8.
[13 Jul 2005 18:59] Paul DuBois 
Noted in 5.0.8 changelog.