Bug #48954 Server is processing compound WHERE clause incorrectly - missing rows
Submitted: 20 Nov 2009 21:25 Modified: 17 Feb 2010 9:42
Reporter: Patrick Crews Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0+ OS:Any
Assigned to: Sergei Glukhov CPU Architecture:Any

[20 Nov 2009 21:25] Patrick Crews
Description:
The server is processing what appears to be a simple query with a compound WHERE clause incorrectly:

This behavior is present in all versions of the server from 5.0+

This query:
SELECT table2 .`int_key`
FROM C table1  JOIN C table2  ON table2 .`pk`  = table1 .`int_key`
WHERE table1 .`varchar_key`  IN ( 'i' , 't' )  OR table1 .`varchar_key`  != 'x'  ;

Produces this diff as compared to JavaDB and Postgres (which experimentation has shown to be correct).

#/* Diff: */

#/* --- /tmp//randgen3397-1258732240-server0.dump       2009-11-20 10:50:40.000000000 -0500
# +++ /tmp//randgen3397-1258732240-server1.dump 2009-11-20 10:50:40.000000000 -0500
# @@ -1,5 +1,4 @@
#  2
# -2
#  3
#  3
#  3
# @@ -9,9 +8,6 @@
#  8
#  8
#  8
# -8
# -9
# -9
#  9
#  9
#  9 */

How to repeat:
MTR test case - set up for use with 6.0.  Comment out SET statements as needed for use with other versions of the server

#/* Server0: MySQL 6.0.14-alpha-gcov-debug-log */
#/* Server1: JavaDB Version N/A */

/*!50400 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' */;
/*!50400 SET SESSION optimizer_use_mrr = 'disable' */;
/*!50400 SET SESSION engine_condition_pushdown = 'ON' */;
/*!50400 SET SESSION join_cache_level = 1 */;
/*!50400 SET SESSION debug = 'd,optimizer_no_icp' */;

#/* Begin test case for query 0 */

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

CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_key` int(11) DEFAULT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `varchar_key` (`varchar_key`,`int_key`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (11,0,NULL);
INSERT INTO `C` VALUES (16,1,'c');
INSERT INTO `C` VALUES (20,2,'d');
INSERT INTO `C` VALUES (13,166,'e');
INSERT INTO `C` VALUES (19,6,'f');
INSERT INTO `C` VALUES (9,8,'h');
INSERT INTO `C` VALUES (7,3,'j');
INSERT INTO `C` VALUES (12,5,'k');
INSERT INTO `C` VALUES (4,9,'k');
INSERT INTO `C` VALUES (3,3,'m');
INSERT INTO `C` VALUES (2,9,'m');
INSERT INTO `C` VALUES (17,9,'m');
INSERT INTO `C` VALUES (14,3,'n');
INSERT INTO `C` VALUES (10,53,'o');
INSERT INTO `C` VALUES (5,NULL,'r');
INSERT INTO `C` VALUES (15,0,'t');
INSERT INTO `C` VALUES (6,9,'t');
INSERT INTO `C` VALUES (8,8,'u');
INSERT INTO `C` VALUES (1,2,'w');
INSERT INTO `C` VALUES (18,5,'y');

# This is just here to show that the WHERE clause is processed badly - the JOIN shows 2 rows that
# should pass.  Please delete this on adding to the test suite
SELECT table2.pk, table1.int_key, table2.varchar_key from C table1 JOIN C table2 WHERE table2.pk = table1.int_key;

 
SELECT table2 .`int_key`  
FROM C table1  JOIN C table2  ON table2 .`pk`  = table1 .`int_key`  
WHERE table1 .`varchar_key`  IN ( 'i' , 't' )  OR table1 .`varchar_key`  != 'x'  ;

#/* Diff: */

#/* --- /tmp//randgen3397-1258732240-server0.dump	2009-11-20 10:50:40.000000000 -0500
# +++ /tmp//randgen3397-1258732240-server1.dump	2009-11-20 10:50:40.000000000 -0500
# @@ -1,5 +1,4 @@
#  2
# -2
#  3
#  3
#  3
# @@ -9,9 +8,6 @@
#  8
#  8
#  8
# -8
# -9
# -9
#  9
#  9
#  9 */

DROP TABLE C;
#/* End of test case for query 0 */
[17 Feb 2010 9:43] Sergei Glukhov
can not repeat the bug on latest 5.1-bugteam tree(see test result below):
--
+/*!50400 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'
+*/;
+/*!50400 SET SESSION optimizer_use_mrr = 'disable' */;
+/*!50400 SET SESSION engine_condition_pushdown = 'ON' */;
+/*!50400 SET SESSION join_cache_level = 1 */;
+/*!50400 SET SESSION debug = 'd,optimizer_no_icp' */;
+DROP TABLE /*! IF EXISTS */ C;
+CREATE TABLE `C` (
+`pk` int(11) NOT NULL AUTO_INCREMENT,
+`int_key` int(11) DEFAULT NULL,
+`varchar_key` varchar(1) DEFAULT NULL,
+PRIMARY KEY (`pk`),
+KEY `int_key` (`int_key`),
+KEY `varchar_key` (`varchar_key`,`int_key`)
+) AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
+INSERT INTO `C` VALUES (11,0,NULL);
+INSERT INTO `C` VALUES (16,1,'c');
+INSERT INTO `C` VALUES (20,2,'d');
+INSERT INTO `C` VALUES (13,166,'e');
+INSERT INTO `C` VALUES (19,6,'f');
+INSERT INTO `C` VALUES (9,8,'h');
+INSERT INTO `C` VALUES (7,3,'j');
+INSERT INTO `C` VALUES (12,5,'k');
+INSERT INTO `C` VALUES (4,9,'k');
+INSERT INTO `C` VALUES (3,3,'m');
+INSERT INTO `C` VALUES (2,9,'m');
+INSERT INTO `C` VALUES (17,9,'m');
+INSERT INTO `C` VALUES (14,3,'n');
+INSERT INTO `C` VALUES (10,53,'o');
+INSERT INTO `C` VALUES (5,NULL,'r');
+INSERT INTO `C` VALUES (15,0,'t');
+INSERT INTO `C` VALUES (6,9,'t');
+INSERT INTO `C` VALUES (8,8,'u');
+INSERT INTO `C` VALUES (1,2,'w');
+INSERT INTO `C` VALUES (18,5,'y');
+SELECT table2.pk, table1.int_key, table2.varchar_key from C table1 JOIN C table2 WHERE
+table2.pk = table1.int_key;
+pk	int_key	varchar_key
+1	1	w
+2	2	m
+2	2	m
+3	3	m
+3	3	m
+3	3	m
+5	5	r
+5	5	r
+6	6	t
+8	8	u
+8	8	u
+9	9	h
+9	9	h
+9	9	h
+9	9	h
+SELECT table1.int_key, table2 .`int_key`, table2.varchar_key
+FROM C table1  JOIN C table2  ON table2 .`pk`  = table1 .`int_key`
+WHERE table1 .`varchar_key`  IN ( 'i' , 't' )  OR table1 .`varchar_key`  != 'x'  ;
+int_key	int_key	varchar_key
+1	2	w
+2	9	m
+6	9	t
+8	8	u
+3	3	m
+5	NULL	r
+9	8	h
+3	3	m
+9	8	h
+9	8	h
+3	3	m
+9	8	h
+8	8	u
+2	9	m
+5	NULL	r
--
Both results are correct.