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:
None 
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
Description:
This query fails to return any rows when semijoin is enabled, even though some rows to match the IN predicate. If the inner subquery is simplifed to not include a join, the problem goes away.

SELECT OUTR . `varchar_nokey`
FROM C AS OUTR
WHERE OUTR . `varchar_nokey` IN (
 SELECT  INNR . `varchar_nokey` AS X 
 FROM DD AS INNR2 LEFT JOIN
 CC AS INNR ON ( INNR2 . `int_nokey` = INNR . `pk` )
);

May be related to bug #38010 with the following differences - outer query does not have to have a join, and adding `pk` to the outer select does not cause the problem to go away.

How to repeat:
A test case will be uploaded shortly.
[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.