MySQL Bugs: #49604: 6.0 processing compound WHERE clause incorrectly with Innodb

Bug #49604	6.0 processing compound WHERE clause incorrectly with Innodb - extra rows
Submitted:	10 Dec 2009 21:17	Modified:	23 Nov 2010 3:08
Reporter:	Patrick Crews	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	6.0	OS:	Any
Assigned to:	Olav Sandstå	CPU Architecture:	Any
Tags:	extra rows, innodb, regression

Description:
The 6.0-codebase-bugfixing tree is processing compound WHERE clauses incorrectly with Innodb tables.  This does not occur with MyISAM and does not seem to be tied to the optimizer variables:
join_cache_level, optimizer_use_mrr, engine_condition_pushdown, debug.

If we break down the query to include one part of the WHERE clause or another, things are processed correctly, it is only the OR'ing of the two conditionals that cause things to go awry

From the attached test case:
This query:
SELECT  STRAIGHT_JOIN table2 .`col_int_key`, table1.col_int_key, table2.pk
FROM Z table1  JOIN Q table2  ON table1 .`col_int_key`  = table2 .`pk`
WHERE table2 .`pk`  IN (  2  ,  1  ,  6  )  OR table2 .`pk`  NOT  IN (  1  )   ;

Produces this result set: The '2 1 1' rows are repeated unnecessarily.

col_int_key	col_int_key	pk
2	1	1
2	1	1
2	1	1
2	1	1
2	1	1
2	1	1
2	1	1
2	1	1
9	2	2
3	3	3
3	3	3
3	3	3
3	3	3
3	3	3
8	4	4
8	4	4
8	4	4
1	6	6
3	8	8
3	8	8
3	8	8
1	9	9

EXPLAIN output:
EXPLAIN SELECT  STRAIGHT_JOIN table2 .`col_int_key`
FROM Z table1  JOIN Q table2  ON table1 .`col_int_key`  = table2 .`pk`
WHERE table2 .`pk`  IN (  2  ,  1  ,  6  )  OR table2 .`pk`  NOT  IN (  1  )   ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	table1	range	col_int_key	col_int_key	5	NULL	16	Using where; Using index
1	SIMPLE	table2	eq_ref	PRIMARY	PRIMARY	4	test.table1.col_int_key	1	

How to repeat:
MTR test case:
To use on earlier versions of the server, comment out the SET statements accordingly
Be sure to use --mysqld=--loose-innodb when recording this test case.

#/* Server0: MySQL 6.0.14-alpha-gcov-debug-log */
#/* Server1: MySQL 5.1.42-gcov-debug-log */

SET SESSION optimizer_switch = 'firstmatch=off,index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,loosescan=off,materialization=off,semijoin=off' ;
#/* Server 1 : SET SESSION optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on' */;

SET SESSION optimizer_use_mrr = 'force' ;

/*!50400 SET SESSION engine_condition_pushdown = ON */;

SET SESSION join_cache_level = 1; 

SET SESSION debug = 'd,optimizer_no_icp,optimizer_innodb_ds_mrr' ;

#/* Begin test case for query 0 */

--disable_warnings
DROP TABLE /*! IF EXISTS */ Z;
DROP TABLE /*! IF EXISTS */ Q;
--enable_warnings

CREATE TABLE `Z` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_key` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=latin1;
INSERT INTO `Z` VALUES (1,NULL);
INSERT INTO `Z` VALUES (9,NULL);
INSERT INTO `Z` VALUES (17,NULL);
INSERT INTO `Z` VALUES (6,0);
INSERT INTO `Z` VALUES (7,0);
INSERT INTO `Z` VALUES (12,1);
INSERT INTO `Z` VALUES (15,1);
INSERT INTO `Z` VALUES (16,1);
INSERT INTO `Z` VALUES (22,1);
INSERT INTO `Z` VALUES (20,2);
INSERT INTO `Z` VALUES (2,3);
INSERT INTO `Z` VALUES (3,3);
INSERT INTO `Z` VALUES (18,3);
INSERT INTO `Z` VALUES (19,3);
INSERT INTO `Z` VALUES (21,3);
INSERT INTO `Z` VALUES (4,4);
INSERT INTO `Z` VALUES (10,4);
INSERT INTO `Z` VALUES (13,4);
INSERT INTO `Z` VALUES (11,6);
INSERT INTO `Z` VALUES (8,8);
INSERT INTO `Z` VALUES (14,8);
INSERT INTO `Z` VALUES (23,8);
INSERT INTO `Z` VALUES (5,9);
CREATE TABLE `Q` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_key` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
INSERT INTO `Q` VALUES (1,2);
INSERT INTO `Q` VALUES (2,9);
INSERT INTO `Q` VALUES (3,3);
INSERT INTO `Q` VALUES (4,8);
INSERT INTO `Q` VALUES (5,7);
INSERT INTO `Q` VALUES (6,1);
INSERT INTO `Q` VALUES (7,2);
INSERT INTO `Q` VALUES (8,3);
INSERT INTO `Q` VALUES (9,1);
INSERT INTO `Q` VALUES (10,2);
 
SELECT  STRAIGHT_JOIN table2 .`col_int_key`  
FROM Z table1  JOIN Q table2  ON table1 .`col_int_key`  = table2 .`pk`  
WHERE table2 .`pk`  IN (  2  ,  1  ,  6  )  OR table2 .`pk`  NOT  IN (  1  )   ;

#/* Diff: */

#/* --- /tmp//randgen21155-1260471508-server0.dump	2009-12-10 13:58:28.000000000 -0500
# +++ /tmp//randgen21155-1260471508-server1.dump	2009-12-10 13:58:28.000000000 -0500
# @@ -4,10 +4,6 @@
#  2
#  2
#  2
# -2
# -2
# -2
# -2
#  3
#  3
#  3 */

DROP TABLE Z;
DROP TABLE Q;
#/* End of test case for query 0 */

I should have been more clear in the original submission.

In the result sets I have shown in the Description field, what I meant what that the correct result set has 4 '2 1 1' rows.  The bug is causing these 4 rows to be repeated, giving us 8 rows.

No duplication is occurring for other rows in the result set.

This failure seems not to be reproducible using the current version of mysql-6.0-codebase-bugfixing. I will do an attempt to find what has caused the extra result rows to go away.

This bug has been solved by the fix to Bug#48665. The patch is available here:

   http://lists.mysql.com/commits/91078

and included in the source code as change set joro@sun.com-20091119162619-op3ny0dvpw8eibij .

I have verified that the test case fails without this change and passes with it.

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/101055

2943 Olav.Sandstaa@sun.com	2010-02-22
      Test case for Bug#49604 6.0 processing compound WHERE clause incorrectly with Innodb - extra rows
      
      The fix for this bug is found in the patch for Bug#48665. This patch adds a test for this bug.
     @ mysql-test/r/innodb_mysql.result
        Result for test for Bug#49604.
     @ mysql-test/t/innodb_mysql.test
        Test case for Bug#49604.

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/101094

2945 Olav.Sandstaa@sun.com	2010-02-22
      Test case for Bug#49604 6.0 processing compound WHERE clause incorrectly with Innodb - extra rows
      
      The fix for this bug is found in the patch for Bug#48665. This patch adds a test for this bug.
     @ mysql-test/r/innodb_mysql.result
        Result for test for Bug#49604.
     @ mysql-test/t/innodb_mysql.test
        Test case for Bug#49604.

Patch is pushed to mysql-6.0-codebase-bugfixing with revision id:
olav.sandstaa@sun.com-20100222142955-2rxum4ls3icj95ti .

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100225194420-p60r4u90tszj8q2x) (version source revid:olav.sandstaa@sun.com-20100222142955-2rxum4ls3icj95ti) (merge vers: 6.0.14-alpha) (pib:16)

Test case. No changelog entry needed.

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/105472

3070 Olav Sandstaa	2010-04-13
      Test case for Bug#49604 6.0 processing compound WHERE clause incorrectly with Innodb - extra rows
      
      (Backporting of revid:olav.sandstaa@sun.com-20100222142955-2rxum4ls3icj95ti )
        
      The fix for this bug is found in the patch for Bug#48665. This patch adds a test for this bug.
     @ mysql-test/r/innodb_mysql.result
        Result for test for Bug#49604.
     @ mysql-test/t/innodb_mysql.test
        Test case for Bug#49604.

Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)

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)