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