Bug #49590 Server reporting different result sets for a query depending on use of indexes

• Submitted: 10 Dec 2009 16:03
• Reporter: Patrick Crews
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.0+
• OS: Any
• CPU Architecture: Any
• Tags: INDEX

[10 Dec 2009 16:03] Patrick Crews

Description:
The server (versions 5.0+) is reporting different result sets for the same query depending on whether or not indexes are enabled on the relevant tables:

From the attached test case:
SELECT  MAX( table1 .`col_int_key`  ) field1  
FROM C table1  STRAIGHT_JOIN ( BB table2  JOIN CC  ON table2 .`pk`  )  ON table2 .`col_varchar_key`  
HAVING field1   ;
field1
NULL  <----------------------With indexes on, we get a NULL row
ALTER TABLE BB DISABLE KEYS;
ALTER TABLE C DISABLE KEYS;
ALTER TABLE CC DISABLE KEYS;
SELECT  MAX( table1 .`col_int_key`  ) field1  
FROM C table1  STRAIGHT_JOIN ( BB table2  JOIN CC  ON table2 .`pk`  )  ON table2 .`col_varchar_key`  
HAVING field1
             <---------------------We get nothing here without indexes

How to repeat:
MTR test case for 6.0-codebase
Comment out the SET statements as necessary for older versions of the server

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

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

#/* Begin test case for query 0 */

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

CREATE TABLE `CC` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,8,'v');
INSERT INTO `CC` VALUES (11,9,'r');
INSERT INTO `CC` VALUES (12,9,'a');
INSERT INTO `CC` VALUES (13,186,'m');
INSERT INTO `CC` VALUES (14,NULL,'y');
INSERT INTO `CC` VALUES (15,2,'j');
INSERT INTO `CC` VALUES (16,3,'d');
INSERT INTO `CC` VALUES (17,0,'z');
INSERT INTO `CC` VALUES (18,133,'e');
INSERT INTO `CC` VALUES (19,1,'h');
INSERT INTO `CC` VALUES (20,8,'b');
INSERT INTO `CC` VALUES (21,5,'s');
INSERT INTO `CC` VALUES (22,5,'e');
INSERT INTO `CC` VALUES (23,8,'j');
INSERT INTO `CC` VALUES (24,6,'e');
INSERT INTO `CC` VALUES (25,51,'f');
INSERT INTO `CC` VALUES (26,4,'v');
INSERT INTO `CC` VALUES (27,7,'x');
INSERT INTO `CC` VALUES (28,6,'m');
INSERT INTO `CC` VALUES (29,4,'c');
CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,2,'w');
INSERT INTO `C` VALUES (2,9,'m');
INSERT INTO `C` VALUES (3,3,'m');
INSERT INTO `C` VALUES (4,9,'k');
INSERT INTO `C` VALUES (5,NULL,'r');
INSERT INTO `C` VALUES (6,9,'t');
INSERT INTO `C` VALUES (7,3,'j');
INSERT INTO `C` VALUES (8,8,'u');
INSERT INTO `C` VALUES (9,8,'h');
INSERT INTO `C` VALUES (10,53,'o');
INSERT INTO `C` VALUES (11,0,NULL);
INSERT INTO `C` VALUES (12,5,'k');
INSERT INTO `C` VALUES (13,166,'e');
INSERT INTO `C` VALUES (14,3,'n');
INSERT INTO `C` VALUES (15,0,'t');
INSERT INTO `C` VALUES (16,1,'c');
INSERT INTO `C` VALUES (17,9,'m');
INSERT INTO `C` VALUES (18,5,'y');
INSERT INTO `C` VALUES (19,6,'f');
INSERT INTO `C` VALUES (20,2,'d');
CREATE TABLE `BB` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
INSERT INTO `BB` VALUES (10,8,NULL);

 
SELECT  MAX( table1 .`col_int_key`  ) field1  
FROM C table1  STRAIGHT_JOIN ( BB table2  JOIN CC  ON table2 .`pk`  )  ON table2 .`col_varchar_key`  
HAVING field1   ;

#/* End of test case for query 0 */

ALTER TABLE BB DISABLE KEYS; 
ALTER TABLE C DISABLE KEYS; 
ALTER TABLE CC DISABLE KEYS; SELECT  MAX( table1 .`col_int_key`  ) field1  
FROM C table1  STRAIGHT_JOIN ( BB table2  JOIN CC  ON table2 .`pk`  )  ON table2 .`col_varchar_key`  
HAVING field1    /* TRANSFORM_OUTCOME_UNORDERED_MATCH */; 

ALTER TABLE BB ENABLE KEYS; 
ALTER TABLE C ENABLE KEYS; 
ALTER TABLE CC ENABLE KEYS; 

DROP TABLE CC;
DROP TABLE C;
DROP TABLE BB;