Bug #58422 Incorrect result when OUTER JOIN'ing with an empty table
Submitted: 23 Nov 2010 16:39 Modified: 24 Jan 2011 15:12
Reporter: Ole John Aske Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:next-mr, 5.5, 5.1.x OS:Any
Assigned to: Ole John Aske CPU Architecture:Any
Triage: Triaged: D2 (Serious) / R2 (Low) / E2 (Low)

[23 Nov 2010 16:39] Ole John Aske
Description:
When an empty table is part of the outer joined tables the result is incorrect.

How to repeat:
create table t_empty(pk int primary key) engine = myisam;
create table t1(pk int primary key) engine = myisam;
insert into t1 values (1), (2), (3);
create table t2(pk int primary key) engine = myisam;
insert into t2 values (1), (2), (3);

select *
  from 
    t1
  left outer join
    (t2 inner join t_empty on true)
  on t1.pk=t2.pk
  where t2.pk <> 2;

+----+------+------+
| pk | pk   | pk   |
+----+------+------+
|  1 |    1 | NULL |
|  3 |    3 | NULL |
+----+------+------+

The sub join '(t2 inner join t_empty on true)' can't produce any rows
as table 't_empty' is .... empty. The correct result for this join
should be :

T1.pk, NULL, NULL
[23 Nov 2010 16:42] Valeriy Kravchuk
Verified as described:

macbook-pro:5.5-sec openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 306
Server version: 5.5.8-rc-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table t1, t2;
ERROR 1051 (42S02): Unknown table 't2'
mysql> create table t_empty(pk int primary key) engine = myisam;
Query OK, 0 rows affected (0.06 sec)

mysql> create table t1(pk int primary key) engine = myisam;
Query OK, 0 rows affected (0.17 sec)

mysql> insert into t1 values (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create table t2(pk int primary key) engine = myisam;
Query OK, 0 rows affected (0.10 sec)

mysql> insert into t2 values (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> 
mysql> select *
    ->   from 
    ->     t1
    ->   left outer join
    ->     (t2 inner join t_empty on true)
    ->   on t1.pk=t2.pk
    ->   where t2.pk <> 2;
+----+------+------+
| pk | pk   | pk   |
+----+------+------+
|  1 |    1 | NULL |
|  3 |    3 | NULL |
+----+------+------+
2 rows in set (0.04 sec)
[23 Nov 2010 16:43] Ole John Aske
There is a similar problem for the query:

select *
  from 
    t1
  left outer join
    (t2 inner join t_empty on t_empty.pk is NULL)
  on t1.pk=t2.pk
  where t2.pk <> 2;

While this variant return the correct result (Empty result as 'where t2.pk <> 2 will be 'not true' as all t2.pk==NULL)

select *
  from 
    t1
  left outer join
    (t2 inner join t_empty on t_empty.pk is NOT NULL)
  on t1.pk=t2.pk
  where t2.pk <> 2;
[7 Dec 2010 11:50] 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/commits/126195

3477 Ole John Aske	2010-12-07
      Fix for bug#58422: Incorrect result when OUTER JOIN'ing with an empty table
      
      Fixed incorrect checks in join_read_const_table() for when to 
      accept a non-existing, or empty const-row as a part of the const'ified 
      set of tables.
      
      Intention of this test is to only accept NULL-rows if this table is outer joined
      into the resultset. (In case of an inner-join we can conclude at this point that 
      resultset will be empty, end we want to return 'error' to signal this.)
      
      Initially 'maybe_null' is set to the same value as 'outer_join' in 
      setup_table_map(), mysql_priv.h ~line 2424. Later simplify_joins() will
      attemp to replace outer joins by inner join whenever possible. This
      will cause 'outer_join' to be updated. However, 'maybe_null' is *not* updated
      to reflect this rewrite as this field is used to currectly set the 'nullability'
      property for the columns in the resultset.
      
      We should therefore change join_read_const_table() to check the 'outer_join'
      property instead of 'maybe_null', as this correctly reflect the nullability of
      the *execution plan* (not *resultset*).
[7 Dec 2010 11:56] 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/commits/126196

3389 Ole John Aske	2010-12-07
      SPJ-scan-scan: Cherry picked fix for bug#58422: Incorrect result when OUTER JOIN'ing with an empty table
      
      Fixed incorrect checks in join_read_const_table() for when to 
      accept a non-existing, or empty const-row as a part of the const'ified 
      set of tables.
      
      Intention of this test is to only accept NULL-rows if this table is outer joined
      into the resultset. (In case of an inner-join we can conclude at this point that 
      resultset will be empty, end we want to return 'error' to signal this.)
      
      Initially 'maybe_null' is set to the same value as 'outer_join' in 
      setup_table_map(), mysql_priv.h ~line 2424. Later simplify_joins() will
      attemp to replace outer joins by inner join whenever possible. This
      will cause 'outer_join' to be updated. However, 'maybe_null' is *not* updated
      to reflect this rewrite as this field is used to currectly set the 'nullability'
      property for the columns in the resultset.
      
      We should therefore change join_read_const_table() to check the 'outer_join'
      property instead of 'maybe_null', as this correctly reflect the nullability of
      the *execution plan* (not *resultset*).
[12 Jan 2011 16:26] Omer Barnir
triage - discussion changing SRMRTBD to SR55MRU as risk is now and impact can be higher (not 5.1 as code will be somewhat different
[12 Jan 2011 20:24] Guilhem Bichot
The discussion about "outer join converted to inner join" in the commit comment reminds me of
http://bugs.mysql.com/bug.php?id=53793
where the conversion seems to not update table->null_row.
[13 Jan 2011 10: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/commits/128626

3498 Ole John Aske	2011-01-13 [merge]
      Merge of fix for bug#58422 from mysql-5.5 -> mysql-trunk
[13 Jan 2011 10:50] Ole John Aske
Pushed into mysql-5.5 and mysql-trunk
[14 Jan 2011 11:14] 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/commits/128721

4108 Ole John Aske	2011-01-14
      Backport to mysql-5.1-telco-7.0:
      Fix for bug#58422: Incorrect result when OUTER JOIN'ing with an empty table
            
      Fixed incorrect checks in join_read_const_table() for when to 
      accept a non-existing, or empty const-row as a part of the
      const'ified set of tables.
            
      Intention of this test is to only accept NULL-rows if this table
      is outer joined into the resultset. (In case of an inner-join
      we can conclude at this point thatresultset will be empty,
      and we want to return 'error' to signal this.)
            
      Initially 'maybe_null' is set to the same value as 'outer_join'
      in setup_table_map(), mysql_priv.h ~line 2424. Later
      simplify_joins() will attemp to replace outer joins by inner 
      join whenever possible.
      
      This will cause 'outer_join' to be updated. However, 'maybe_null'
      is *not* updated to reflect this rewrite as this field is used 
      to currectly set the 'nullability' property for the columns 
      in the resultset.
            
      We should therefore change join_read_const_table() to check the
      'outer_join' property instead of 'maybe_null', as this correctly
      reflect the nullability of the partial *execution plan* (not *resultset*).
[14 Jan 2011 11:15] Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.21 (revid:ole.john.aske@oracle.com-20110114111426-esdkcnm8d0ovau6b) (version source revid:ole.john.aske@oracle.com-20110114111426-esdkcnm8d0ovau6b) (merge vers: 5.1.51-ndb-7.0.21) (pib:24)
[22 Jan 2011 22:46] Paul Dubois
Noted in 5.1.51-ndb-7.0.21 changelog.

Outer joins with an empty table could produce incorrect results.

Setting report to Need Merge pending push to 5.5.x, 5.6.x.
[23 Jan 2011 12:26] Ole John Aske
This fix *has* been pushed into 5.5.x and trunk by the pushes:

http://lists.mysql.com/commits/128631
http://lists.mysql.com/commits/128630

3238 Ole John Aske	2011-01-13
      Fix for #58422: Incorrect result when OUTER JOIN'ing with an empty table.

However, as the commit message incorrectly contained only '#58422' it has not been picked up by bazaar as a fix for bug#58422.

Please check, and change status from 'Need merge' -> 'Close'.
[23 Jan 2011 16:34] Ole John Aske
Set back to 'Documenting' - see previous comment.
[24 Jan 2011 15:12] Paul Dubois
Noted in 5.5.10, 5.6.2 changelogs.