MySQL Bugs: #50198: Server adding duplicate row with duplicate WHERE clause condition and Innodb

Bug #50198	Server adding duplicate row with duplicate WHERE clause condition and Innodb
Submitted:	8 Jan 2010 22:55	Modified:	17 Feb 2010 8:12
Reporter:	Patrick Crews	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:	Sergei Glukhov	CPU Architecture:	Any
Tags:	duplicate row, innodb, regression

Description:
The server is adding an extra row if a query contains a duplicate WHERE clause condition using Innodb tables.
This is *not* occurring in 6.0-codebase, 5.0 or with MyISAM tables.

For this query:
SELECT `col_varchar_1024_latin1_key`  
FROM F  
WHERE `pk`  >  3  OR `pk`  IN (  5  ,  3  )  OR `pk`  >  3   ;
col_varchar_1024_latin1_key

We get the following diff on a comparison to 6.0-codebase.
Note that there is only a single row with 'z' for this field value.
/* Diff: */
/* --- /tmp//randgen21762-1262983432-server0.dump	2010-01-08 15:43:52.000000000 -0500
# +++ /tmp//randgen21762-1262983432-server1.dump	2010-01-08 15:43:52.000000000 -0500
# @@ -6,3 +6,4 @@
#  q
#  u
#  z
# +z */

If we remove one pk > 3 clause from the query, we will get proper result sets.

EXPLAIN output:
5.0:
EXPLAIN SELECT `col_varchar_1024_latin1_key`  
FROM F  
WHERE `pk`  >  3  OR `pk`  IN (  5  ,  3  ) OR `pk` > 3 ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	F	range	PRIMARY	PRIMARY	4	NULL	5	Using where

5.1:
EXPLAIN SELECT `col_varchar_1024_latin1_key`  
FROM F  
WHERE `pk`  >  3  OR `pk`  IN (  5  ,  3  )  ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	F	range	PRIMARY	PRIMARY	4	NULL	7	Using where

6.0
EXPLAIN 
SELECT `col_varchar_1024_latin1_key`  
FROM F  
WHERE `pk`  >  3  OR `pk`  IN (  5  ,  3  )  OR `pk`  >  3   ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	F	range	PRIMARY	PRIMARY	4	NULL	6	Using where

How to repeat:
MTR test case:
Currently set up for use with 5.1 / 5.0.
I do not believe the SET statements are needed for use with 6.0 to illustrate the bug

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

#/* The value of optimizer_switch is distinct between the two servers: */
#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' */;

#/* The value of optimizer_use_mrr is distinct between the two servers: */
#SET SESSION optimizer_use_mrr = 'force' ;
#/* Server 1 : SET SESSION optimizer_use_mrr = '' */;

#/* The value of engine_condition_pushdown is common between the two servers: */
/*!50400 SET SESSION engine_condition_pushdown = ON */;

#/* The value of join_cache_level is distinct between the two servers: */
#SET SESSION join_cache_level = 1; 
#/* Server 1 : SET SESSION join_cache_level = '' */;

#/* The value of debug is distinct between the two servers: */
#SET SESSION debug = 'd,optimizer_no_icp,optimizer_innodb_ds_mrr' ;
#/* Server 1 : SET SESSION debug = '' */;

#/* Begin test case for query 0 */

--disable_warnings
DROP TABLE /*! IF EXISTS */ F;
--enable_warnings

CREATE TABLE `F` (
  `col_int_key` int(11) DEFAULT NULL,
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_varchar_1024_latin1_key` varchar(1024) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_1024_latin1_key` (`col_varchar_1024_latin1_key`(767))
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
INSERT INTO `F` VALUES (NULL,1,'f');
INSERT INTO `F` VALUES (8,2,'q');
INSERT INTO `F` VALUES (8,3,'z');
INSERT INTO `F` VALUES (6,4,'q');
INSERT INTO `F` VALUES (8,5,'m');
INSERT INTO `F` VALUES (NULL,6,NULL);
INSERT INTO `F` VALUES (1,7,NULL);
INSERT INTO `F` VALUES (3,8,'p');
INSERT INTO `F` VALUES (8,9,'u');
INSERT INTO `F` VALUES (NULL,10,'n');

 SELECT `col_varchar_1024_latin1_key`  
FROM F  
WHERE `pk`  >  3  OR `pk`  IN (  5  ,  3  )  ;
#OR `pk`  >  3   ;

/* Diff: */

/* --- /tmp//randgen21762-1262983432-server0.dump	2010-01-08 15:43:52.000000000 -0500
# +++ /tmp//randgen21762-1262983432-server1.dump	2010-01-08 15:43:52.000000000 -0500
# @@ -6,3 +6,4 @@
#  q
#  u
#  z
# +z */

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

can't repeat with latest 5.1-bugteam tree:
--
+/*!50400 SET SESSION engine_condition_pushdown = ON */;
+DROP TABLE /*! IF EXISTS */ F;
+CREATE TABLE `F` (
+`col_int_key` int(11) DEFAULT NULL,
+`pk` int(11) NOT NULL AUTO_INCREMENT,
+`col_varchar_1024_latin1_key` varchar(1024) DEFAULT NULL,
+PRIMARY KEY (`pk`),
+KEY `col_int_key` (`col_int_key`),
+KEY `col_varchar_1024_latin1_key` (`col_varchar_1024_latin1_key`(767))
+) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
+INSERT INTO `F` VALUES (NULL,1,'f');
+INSERT INTO `F` VALUES (8,2,'q');
+INSERT INTO `F` VALUES (8,3,'z');
+INSERT INTO `F` VALUES (6,4,'q');
+INSERT INTO `F` VALUES (8,5,'m');
+INSERT INTO `F` VALUES (NULL,6,NULL);
+INSERT INTO `F` VALUES (1,7,NULL);
+INSERT INTO `F` VALUES (3,8,'p');
+INSERT INTO `F` VALUES (8,9,'u');
+INSERT INTO `F` VALUES (NULL,10,'n');
+SELECT `col_varchar_1024_latin1_key`
+FROM F
+WHERE `pk`  >  3  OR `pk`  IN (  5  ,  3  )  ;
+col_varchar_1024_latin1_key
+z
+q
+m
+NULL
+NULL
+p
+u
+n
+SELECT `col_varchar_1024_latin1_key`
+FROM F
+WHERE `pk`  >  3  OR `pk`  IN (  5  ,  3  ) OR `pk`  >  3   ;
+col_varchar_1024_latin1_key
+z
+q
+m
+NULL
+NULL
+p
+u
+n
+show create table F;
+Table	Create Table
+F	CREATE TABLE `F` (
+  `col_int_key` int(11) DEFAULT NULL,
+  `pk` int(11) NOT NULL AUTO_INCREMENT,
+  `col_varchar_1024_latin1_key` varchar(1024) DEFAULT NULL,
+  PRIMARY KEY (`pk`),
+  KEY `col_int_key` (`col_int_key`),
+  KEY `col_varchar_1024_latin1_key` (`col_varchar_1024_latin1_key`(767))
+) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1
--