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