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

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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.049 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.