Bug #9516 left join exists is null

• Submitted: 31 Mar 2005 9:08
• Modified: 26 Apr 2005 16:35
• Reporter: Berto van de Kraats
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.0.3-beta-nt
• OS: Windows (windows and linux)
• Assigned to: Igor Babaev
• CPU Architecture: Any

[31 Mar 2005 9:08] Berto van de Kraats

Description:
query with left join and 'exists is null' returns wrong result

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

create table T3 ( c3 integer );
insert into T3 values ( 7 );
insert into T3 values ( 8 );

SELECT c1,c2 FROM ( T1 LEFT JOIN T2 ON c1 = c2) WHERE EXISTS (SELECT c3 FROM T3 WHERE (c2 IS NULL ));

returns

Empty set (0.00 sec)

it should return:

+------+------+
| c1   | c2   |
+------+------+
|    2 | NULL |
|    3 | NULL |
+------+------+

[16 Apr 2005 5:09] 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/24075

[16 Apr 2005 7:43] Igor Babaev

ChangeSet
  1.1829 05/04/15 22:08:34 [e-mail] +3 -0
  subselect.result, subselect.test:
    Added a test case for bug #9516.
  item_subselect.h:
    Fixed bug #9516.
    The bug was due to that fact that the class Item_subselect
    inherited the generic implementation of the function
    not_null_tables that was not valid for the objects
    of this class. As a result evaluation of the
    not_null_tables attribute was not correct for subqueries.
    This caused invalid transformations of outer joins into
    inner joins.

The fix will appear in version 5.0.5.

[26 Apr 2005 16:35] Paul DuBois

Noted in 5.0.5 changelog.