Bug #9338 left join wrong result

• Submitted: 22 Mar 2005 14:14
• Modified: 18 May 2005 1:57
• Reporter: Berto van de Kraats
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.0.2 (also 5.0.3)
• OS: Windows (windows and linux)
• Assigned to: Igor Babaev
• CPU Architecture: Any

[22 Mar 2005 14:14] Berto van de Kraats

Description:
query with left join returns wrong result

following query returns empty set (which is NOT correct):

select * from T1 left join T2 on c1 = c2 where c2 in ( select c2 from T2 where c2 in ( 1 ) );

Empty set (0.00 sec)

if the query is simplified as follows the correct result is returned:

select * from T1 left join T2 on c1 = c2 where c2 in (1);

+------+------+
| c1   | c2   |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

How to repeat:
create table T1 ( c1 integer );
insert into T1 values ( 1 );
insert into T1 values ( 2 );
insert into T1 values ( 3 );
 
 
create table T2 ( c2 integer );
insert into T2 values ( 1 );
insert into T2 values ( 4 );
insert into T2 values ( 5 );

select * from T1 left join T2 on c1 = c2 where c2 in ( select c2 from T2 where c2 in ( 1 ) );

select * from T1 left join T2 on c1 = c2 where c2 in (1);

[16 Apr 2005 3:44] 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/24074

[16 Apr 2005 7:42] Igor Babaev

ChangeSet
  1.1828 05/04/15 20:43:45 [e-mail] +5 -0
  subselect.result, subselect.test:
    Added a test case for bug #9338.
  sql_select.cc:
    Fixed bug #9338.
    When an occurence of a field reference has to be replaced
    by another field reference the whole Item_field must be
    replaced.
  item.cc:
    Fixed bug #9338.
    The method Item_field::replace_equal_field_processor was
    replaced by Item_field::replace_equal_field. The new method
    is used to replace the occurences of Item_field objects.
  item.h:
    Fixed bug #9338.
    The virtual function replace_equal_field_processor was replaced
    by replace_equal_field. The latter is supposed to be used as a
    callback function in calls of the method transform.

The fix will appear in version 5.0.5.

[18 May 2005 1:57] Paul DuBois

Note in 5.0.5 changelog.