Bug #57034 incorrect OUTER JOIN result when joined on unique key
Submitted: 27 Sep 2010 12:00 Modified: 29 Jan 2011 23:13
Reporter: Ole John Aske Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.0.91, 5.1.50, 5.1.52, 5.5.7, 5.6.99 OS:Any
Assigned to: Ole John Aske CPU Architecture:Any
Triage: Triaged: D2 (Serious) / R2 (Low) / E2 (Low)

[27 Sep 2010 12:00] Ole John Aske
Description:
See 'how to repeat'

How to repeat:
create table t1 (pk int primary key, col_int int, col_int_unique int unique key) engine=myisam;
insert into t1 values (1,NULL,2), (2,0,0);
create table t2 (pk int primary key, col_int int, col_int_unique int unique key) engine=myisam;
insert into t2 values (1,0,1), (2,0,2);

select * from t1 left join t2
  on t1.col_int_unique = t2.col_int_unique and t1.col_int = t2.col_int 
  where t1.pk=1;

+----+---------+----------------+----+---------+----------------+
| pk | col_int | col_int_unique | pk | col_int | col_int_unique |
+----+---------+----------------+----+---------+----------------+
|  1 |    NULL |              2 |  2 |       0 |              2 |
+----+---------+----------------+----+---------+----------------+
1 row in set (0.00 sec)

This result is incorrect as the predicate term 't1.col_int = t2.col_int' is not true ('NULL = 0' is 'UNKNOWN' pr. ISO9075).

If we drop the unique key on t2 we get the correct result:

------------
drop index col_int_unique on t2;

mysql> select * from t1 left join t2
    ->   on t1.col_int_unique = t2.col_int_unique and t1.col_int = t2.col_int
    ->   where t1.pk=1;
+----+---------+----------------+------+---------+----------------+
| pk | col_int | col_int_unique | pk   | col_int | col_int_unique |
+----+---------+----------------+------+---------+----------------+
|  1 |    NULL |              2 | NULL |    NULL |           NULL |
+----+---------+----------------+------+---------+----------------+
1 row in set (0.00 sec)
[27 Sep 2010 13:58] Valeriy Kravchuk
Thank you for the bug report.
[3 Dec 2010 16:55] 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/125966

3477 Ole John Aske	2010-12-03
      Fix for bug#57034: incorrect OUTER JOIN result when joined on unique key.
      
      The value of 'null_value' is not valid until *after* ::store_value() or ::cmp() has 
      been called for the Item object.
      
      Fix is to check swap order of ::store_value()/::cmp() and checking of Item::null_value.
      This pattern is widely used other places inside item_cmpfunc.cc
[3 Dec 2010 17:00] 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/125972

3387 Ole John Aske	2010-12-03
      SPJ-scan-scan: Cherry picked fix for bug#57034 into SPJ branch.
[3 Dec 2010 17:02] Ole John Aske
Has verified that this bug is present in 5.5.7 and 5.6.99 - Updated 'Version'

A real simple and obvious bug fix is available.
[3 Dec 2010 17:11] Ole John Aske
It looks like this is a duplicate for bug#49600:
[6 Dec 2010 22:56] Ole John Aske
This fix seems to also solve the problems reported in bug#48046 - likely a duplicate.
[20 Dec 2010 13:17] 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/127305

3529 Ole John Aske	2010-12-20
      Updated fix for bug#57034 'incorrect OUTER JOIN result when joined on unique key'
      after review:
      
      Added testcase for bug#48046 & bug#49600 which are duplicates.
      
      .............
             The value of 'null_value' is not valid until *after* ::store_value() or ::cmp() has
             been called for the Item object.
      
             Fix is to check swap order of ::store_value()/::cmp() and checking of Item::null_value.
             This pattern is widely used other places inside item_cmpfunc.cc
[6 Jan 2011 15:48] Roy Lyseng
Risk for this bug fix is considered low. The impact is potentially higher than I3, as this bug may easily go unnoticed by customers.

Asking for 5.5 MRU tag.
[13 Jan 2011 8:24] 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/128591

3233 Ole John Aske	2011-01-13
      Fix for Bug#57034 incorrect OUTER JOIN result when joined on unique key
      
      Item_equal::val_int() checked for NULL-values by checking Item::null_value
      *before* the respective ::store_value() and ::cmp(Item*) metods where called.
      
      As Item::null_value is set by these metods, the value of 'null_value' 
      is not valid until *after* ::store_value() or ::cmp() has
      been called for the Item object.
            
      Fix is to swap order of ::store_value()/::cmp() and checking of Item::null_value.
      This pattern is widely used other places inside item_cmpfunc.cc .
[13 Jan 2011 8:26] 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/128592

3494 Ole John Aske	2011-01-13 [merge]
      Merged fix for bug#57034 from mysql-5.5 -> mysql-trunk
[13 Jan 2011 8:33] 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/128594

3234 Ole John Aske	2011-01-13
      Fix for Bug#57034 incorrect OUTER JOIN result when joined on unique key
      
      Item_equal::val_int() checked for NULL-values by checking Item::null_value
      *before* the respective ::store_value() and ::cmp(Item*) metods where called.
      
      As Item::null_value is set by these metods, the value of 'null_value' 
      is not valid until *after* ::store_value() or ::cmp() has
      been called for the Item object.
            
      Fix is to swap order of ::store_value()/::cmp() and checking of Item::null_value.
      This pattern is widely used other places inside item_cmpfunc.cc .
[13 Jan 2011 8:34] 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/128595

3495 Ole John Aske	2011-01-13 [merge]
      Merged fix for bug#57034 from mysql-5.5 -> mysql-trunk
[13 Jan 2011 8:35] Bugs System
Pushed into mysql-trunk 5.6.2 (revid:ole.john.aske@oracle.com-20110113083359-1z8q0hil9y22izvl) (version source revid:ole.john.aske@oracle.com-20110113083359-1z8q0hil9y22izvl) (merge vers: 5.6.2) (pib:24)
[13 Jan 2011 8:35] Bugs System
Pushed into mysql-5.5 5.5.9 (revid:ole.john.aske@oracle.com-20110113083330-q6jqxffv9d1syew0) (version source revid:ole.john.aske@oracle.com-20110113083330-q6jqxffv9d1syew0) (merge vers: 5.5.9) (pib:24)
[13 Jan 2011 8:36] Ole John Aske
Pushed to mysql-5.5 and mysql-trunk
[13 Jan 2011 9:13] Bugs System
Pushed into mysql-trunk 5.6.2 (revid:georgi.kodinov@oracle.com-20110113090847-gtvlitfthaasqezp) (version source revid:georgi.kodinov@oracle.com-20110113090847-gtvlitfthaasqezp) (merge vers: 5.6.2) (pib:24)
[14 Jan 2011 8:55] 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/128700

4106 Ole John Aske	2011-01-14
      Backport to mysql-5.1-telco-7.0:
      Fix for Bug#57034 incorrect OUTER JOIN result when joined on unique key
            
      Item_equal::val_int() checked for NULL-values by checking Item::null_value
      *before* the respective ::store_value() and ::cmp(Item*) metods where called.
            
      As Item::null_value is set by these metods, the value of 'null_value' 
      is not valid until *after* ::store_value() or ::cmp() has
      been called for the Item object.
                  
      Fix is to swap order of ::store_value()/::cmp() and checking of Item::null_value.
      This pattern is widely used other places inside item_cmpfunc.cc .
[14 Jan 2011 8:55] Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.21 (revid:ole.john.aske@oracle.com-20110114085447-lpa53iyi8klji1g4) (version source revid:ole.john.aske@oracle.com-20110114085447-lpa53iyi8klji1g4) (merge vers: 5.1.51-ndb-7.0.21) (pib:24)
[14 Jan 2011 8:57] Ole John Aske
Fix has been cherry picked for backport to mysql-5.1-telco-7.0
[20 Jan 2011 19:50] Paul Dubois
Noted in 5.1.51-ndb-7.0.21, 5.5.10, 5.6.2 changelogs.

Outer joins on a unique key could return incorrect results.