MySQL Bugs: #58422: Incorrect result when OUTER JOIN'ing with an empty table

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

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

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)

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;

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*).

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*).

triage - discussion changing SRMRTBD to SR55MRU as risk is now and impact can be higher (not 5.1 as code will be somewhat different

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.

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

Pushed into mysql-5.5 and mysql-trunk

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*).

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)

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.

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'.

Set back to 'Documenting' - see previous comment.

Noted in 5.5.10, 5.6.2 changelogs.