Bug #38075 | Wrong result: rows matching a subquery with outer join not returned | ||
---|---|---|---|
Submitted: | 12 Jul 2008 18:40 | Modified: | 22 Nov 2010 1:25 |
Reporter: | Philip Stoev | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
Version: | 6.0.6-bzr | OS: | Any |
Assigned to: | Roy Lyseng | CPU Architecture: | Any |
Tags: | optimizer_switch, outerjoin, semijoin, subquery |
[12 Jul 2008 18:40]
Philip Stoev
[12 Jul 2008 18:50]
Philip Stoev
Test case for bug 31099
Attachment: bug31099.test (application/octet-stream, text), 3.54 KiB.
[13 Jul 2008 14:31]
Sveta Smirnova
Thank you for the report. Verified as described.
[8 Jul 2009 16:38]
Philip Stoev
Another test case for this bug --disable_warnings DROP TABLE IF EXISTS CC, C, BB; --enable_warnings CREATE TABLE `CC` ( `int_nokey` int(11) NOT NULL, `int_key` int(11) NOT NULL, `datetime_key` datetime NOT NULL, `varchar_nokey` varchar(1) NOT NULL, KEY `int_key` (`int_key`), KEY `datetime_key` (`datetime_key`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `CC` VALUES (7,5,'2002-04-10 14:25:30','w'),(7,0,'0000-00-00 00:00:00','s'),(4,0,'2006-09-14 04:01:02','y'),(0,4,'0000-00-00 00:00:00','c'),(1,8,'0000-00-00 00:00:00','q'),(6,5,'0000-00-00 00:00:00',''),(2,9,'0000-00-00 00:00:00','d'),(6,8,'2007-04-01 11:04:17',''),(0,1,'0000-00-00 00:00:00','p'),(4,7,'2009-01-12 00:00:00','x'),(4,0,'2009-06-05 00:00:00','f'),(7,3,'2006-02-14 18:06:35','x'),(3,5,'2006-02-21 07:08:16','h'),(7,0,'0000-00-00 00:00:00','c'),(8,7,'0000-00-00 00:00:00','m'),(4,0,'0000-00-00 00:00:00','s'),(6,0,'2007-02-13 00:00:00','b'),(9,1,'0000-00-00 00:00:00','o'),(0,0,'0000-00-00 00:00:00','j'),(9,1,'2003-08-11 00:00:00','m'); CREATE TABLE `C` ( `int_nokey` int(11) NOT NULL, `int_key` int(11) NOT NULL, `datetime_key` datetime NOT NULL, `varchar_nokey` varchar(1) NOT NULL, KEY `int_key` (`int_key`), KEY `datetime_key` (`datetime_key`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `C` VALUES (5,0,'0000-00-00 00:00:00','k'),(3,0,'2008-04-19 07:51:37','a'),(0,2,'2006-06-03 00:00:00',''),(3,0,'0000-00-00 00:00:00','u'),(1,3,'2000-10-03 15:17:43','e'),(0,0,'2009-04-25 16:10:46','v'),(1,7,'2005-01-11 03:31:23','i'),(7,0,'0000-00-00 00:00:00','t'),(1,7,'2000-03-07 00:00:00','u'),(0,7,'2001-06-14 20:33:16','f'),(0,9,'2005-03-06 05:45:38','u'),(8,2,'0000-00-00 00:00:00','m'),(4,4,'0000-00-00 00:00:00','j'),(9,3,'2002-02-13 21:59:10','f'),(0,9,'0000-00-00 00:00:00','v'),(2,5,'0000-00-00 00:00:00','j'),(0,5,'2001-12-23 00:00:00','g'),(8,0,'2004-05-16 00:00:00','e'),(5,8,'2004-04-02 00:00:00','h'),(1,5,'2003-11-26 00:00:00','z'); CREATE TABLE `BB` ( `int_nokey` int(11) NOT NULL, `int_key` int(11) NOT NULL, `datetime_key` datetime NOT NULL, `varchar_nokey` varchar(1) NOT NULL, KEY `int_key` (`int_key`), KEY `datetime_key` (`datetime_key`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `BB` VALUES (9,5,'2005-03-17 13:58:09','i'),(0,4,'0000-00-00 00:00:00','t'); SELECT `int_key` FROM C WHERE `int_nokey` IN ( SELECT INNR .`int_key` FROM BB LEFT JOIN CC INNR ON INNR .`datetime_key` ); set optimizer_switch='semijoin=off'; SELECT `int_key` FROM C WHERE `int_nokey` IN ( SELECT INNR .`int_key` FROM BB LEFT JOIN CC INNR ON INNR .`datetime_key` );
[14 Jul 2009 22:05]
Patrick Crews
Turning the optimizer_switch variable to semijoin=off results in matching result sets for 5.1 and azalea when using the attached test file.
[8 Dec 2009 10: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/93146 3760 Roy Lyseng 2009-12-08 Bug#38075: Wrong result: rows matching a subquery with outer join not returned The problem is that duplicate weedout handling in evaluate_join_record() is incompatible with outer-join condition handling. Duplicate weedout checking was attempted when the initial predicate was successfully evaluated, but before the conditional predicates were evaluated. Hence, all the row combinations involving the first row of the innermost table that qualified according to the initial predicate would be candidates for duplicate elimination. This would prevent all row combinations where the first row to qualify from the innermost table was not the first row where the initial predicate evaluated to true. Here, "initial predicate" refers to join_tab->select_cond and "conditional predicates" refers to select_cond of "unmatched" join_tabs. Solved by moving the duplicate elimination to after the point in the function where a complete outer-join predicate has been evaluated. The fix also eliminates one boolean local variable and adds a function header. mysql-test/r/subselect_sj2.result Added test result for bug#38075. mysql-test/r/subselect_sj2_jcl6.result Added test result for bug#38075. mysql-test/t/subselect_sj2.test Added test for bug#38075. sql/sql_select.cc Moved the duplicate elimination code so that it is evaluated after the outer-join condition is fully evaluated. Eliminated a boolean variable (select_cond_result). Added Doxygen style function header for evaluate_join_record().
[17 Dec 2009 15:54]
Øystein Grøvlen
Approved with one comment: * semijoin_sj2 is only executed when the innodb storage engine is available. Since your test does not require innodb, maybe you should consider to put it in another file?
[21 Dec 2009 13:53]
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/95242 3789 Roy Lyseng 2009-12-21 Bug#38075: Wrong result: rows matching a subquery with outer join not returned The problem is that duplicate weedout handling in evaluate_join_record() is incompatible with outer-join condition handling. Duplicate weedout checked only the initial predicate, but not the conditional predicates. Hence, all the row combinations involving the first row of the innermost table that qualified according to the initial predicate would be candidates for duplicate elimination. This prevented all row combinations where the first row to qualify from the innermost table was not the first row where the initial predicate evaluated to true. Here, "initial predicate" refers to join_tab->select_cond and "conditional predicates" refers to select_cond of "unmatched" join_tabs. Solved by considering the "found" boolean when performing duplicate elimination. Notice also that duplicate elimination now sets "found" instead of exiting from routine. This ensures correct book-keeping of e.g examined_rows. The fix also eliminates one boolean local variable and adds a function header. mysql-test/r/subselect_sj2.result Added test result for bug#38075. mysql-test/r/subselect_sj2_jcl6.result Added test result for bug#38075. mysql-test/t/subselect_sj2.test Added test for bug#38075. sql/sql_select.cc Considers outer-join condition before executing duplicate elimination code. Eliminated a boolean variable (select_cond_result). Added Doxygen style function header for evaluate_join_record().
[21 Dec 2009 15:39]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091221153807-80nxoli1tw1z9bxn) (version source revid:roy.lyseng@sun.com-20091221135126-byvk6ebw8td7bl2y) (merge vers: 6.0.14-alpha) (pib:15)
[6 May 2010 14:43]
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/107673 3138 Roy Lyseng 2010-05-06 Bug#38075: Wrong result: rows matching a subquery with outer join not returned The problem is that duplicate weedout handling in evaluate_join_record() is incompatible with outer-join condition handling. Duplicate weedout checked only the initial predicate, but not the conditional predicates. Hence, all the row combinations involving the first row of the innermost table that qualified according to the initial predicate would be candidates for duplicate elimination. This prevented all row combinations where the first row to qualify from the innermost table was not the first row where the initial predicate evaluated to true. Here, "initial predicate" refers to join_tab->select_cond and "conditional predicates" refers to select_cond of "unmatched" join_tabs. Solved by considering the "found" boolean when performing duplicate elimination. Notice also that duplicate elimination now sets "found" instead of exiting from routine. This ensures correct book-keeping of e.g examined_rows. The fix also eliminates one boolean local variable and adds a function header. mysql-test/r/subselect_sj2.result Added test result for bug#38075. mysql-test/r/subselect_sj2_jcl6.result Added test result for bug#38075. mysql-test/t/subselect_sj2.test Added test for bug#38075. sql/sql_select.cc Considers outer-join condition before executing duplicate elimination code. Eliminated a boolean variable (select_cond_result). Added Doxygen style function header for evaluate_join_record(). original revid: -c3719.1.70
[16 Aug 2010 6:34]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:11]
Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[22 Nov 2010 1:25]
Paul DuBois
Bug is not in any released 5.6.x version. No changelog entry needed.