MySQL Bugs: #48103: Using STRAIGHT_JOIN with Innodb tables can produce duplicate results

Bug #48103	Using STRAIGHT_JOIN with Innodb tables can produce duplicate results
Submitted:	16 Oct 2009 0:32
Reporter:	Patrick Crews	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1, 6.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	innodb, regression, straight_join

Description:
Adding STRAIGHT_JOIN to a query is producing duplicate results with Innodb tables in 5.1 and 6.0 (not tested in 5.0).  This does not occur with MyISAM tables and is not affected by engine_condition_pushdown settings.

These two queries:
Simplified query:  SELECT OUTR .`varchar_nokey`  FROM C OUTR2  JOIN B OUTR  ON OUTR2 .`time_key`  < OUTR .`datetime_nokey`; 
Simplified transformed query:  SELECT STRAIGHT_JOIN OUTR .`varchar_nokey`  FROM C OUTR2  JOIN B OUTR  ON OUTR2 .`time_key`  < OUTR .`datetime_nokey` ;

produce this diff in result sets:
# 14:23:33  f
# 14:23:33  f
# 14:23:33 +f
# 14:23:33 +f
# 14:23:33 +f
# 14:23:33 +f
# 14:23:33 +f
# 14:23:33 +f
# 14:23:33 +f
# 14:23:33 +f
# 14:23:33 +f
# 14:23:33 +f
# 14:23:33 +f
# 14:23:33 +f
# 14:23:33 +f
# 14:23:33 +f
# 14:23:33 +f
# 14:23:33 +f
# 14:23:33 +f

How to repeat:
MTR test case:
#/* Server0: MySQL 6.0.14-alpha-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 = '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;
DROP TABLE /*! IF EXISTS */ B;
--enable_warnings

CREATE TABLE `C` (
  `time_key` time DEFAULT NULL,
  `datetime_nokey` datetime DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  KEY `time_key` (`time_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES ('11:28:45','2004-10-11 18:13:16','w');
INSERT INTO `C` VALUES ('20:25:14',NULL,'m');
INSERT INTO `C` VALUES ('13:47:24','1900-01-01 00:00:00','m');
INSERT INTO `C` VALUES ('19:24:11','2009-07-25 00:00:00','k');
INSERT INTO `C` VALUES ('15:59:13',NULL,'r');
INSERT INTO `C` VALUES ('00:00:00','2008-07-27 00:00:00','t');
INSERT INTO `C` VALUES ('15:15:04','2002-11-13 16:37:31','j');
INSERT INTO `C` VALUES ('11:32:06','1900-01-01 00:00:00','u');
INSERT INTO `C` VALUES ('18:32:33','2003-12-10 00:00:00','h');
INSERT INTO `C` VALUES ('15:19:25','2001-12-21 22:38:22','o');
INSERT INTO `C` VALUES ('19:03:19','2008-12-13 23:16:44',NULL);
INSERT INTO `C` VALUES ('00:39:46','2005-08-15 12:39:41','k');
INSERT INTO `C` VALUES (NULL,NULL,'e');
INSERT INTO `C` VALUES ('00:00:00','2006-09-11 12:06:14','n');
INSERT INTO `C` VALUES ('13:12:11','2007-12-15 12:39:34','t');
INSERT INTO `C` VALUES ('04:56:48','2005-08-09 00:00:00','c');
INSERT INTO `C` VALUES ('19:56:05','2001-09-02 22:50:02','m');
INSERT INTO `C` VALUES ('19:35:19','2005-12-16 22:58:11','y');
INSERT INTO `C` VALUES ('05:03:03','2007-04-19 00:19:53','f');
INSERT INTO `C` VALUES ('18:38:59','1900-01-01 00:00:00','d');
CREATE TABLE `B` (
  `time_key` time DEFAULT NULL,
  `datetime_nokey` datetime DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  KEY `time_key` (`time_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `B` VALUES ('01:13:38','2005-02-05 00:00:00','f');

 
SELECT OUTR .`varchar_nokey`  
FROM C OUTR2  JOIN B OUTR  ON OUTR2 .`time_key`  < OUTR .`datetime_nokey`   ;

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

#/* Begin test case for query 1 */

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

CREATE TABLE `C` (
  `time_key` time DEFAULT NULL,
  `datetime_nokey` datetime DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  KEY `time_key` (`time_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES ('11:28:45','2004-10-11 18:13:16','w');
INSERT INTO `C` VALUES ('20:25:14',NULL,'m');
INSERT INTO `C` VALUES ('13:47:24','1900-01-01 00:00:00','m');
INSERT INTO `C` VALUES ('19:24:11','2009-07-25 00:00:00','k');
INSERT INTO `C` VALUES ('15:59:13',NULL,'r');
INSERT INTO `C` VALUES ('00:00:00','2008-07-27 00:00:00','t');
INSERT INTO `C` VALUES ('15:15:04','2002-11-13 16:37:31','j');
INSERT INTO `C` VALUES ('11:32:06','1900-01-01 00:00:00','u');
INSERT INTO `C` VALUES ('18:32:33','2003-12-10 00:00:00','h');
INSERT INTO `C` VALUES ('15:19:25','2001-12-21 22:38:22','o');
INSERT INTO `C` VALUES ('19:03:19','2008-12-13 23:16:44',NULL);
INSERT INTO `C` VALUES ('00:39:46','2005-08-15 12:39:41','k');
INSERT INTO `C` VALUES (NULL,NULL,'e');
INSERT INTO `C` VALUES ('00:00:00','2006-09-11 12:06:14','n');
INSERT INTO `C` VALUES ('13:12:11','2007-12-15 12:39:34','t');
INSERT INTO `C` VALUES ('04:56:48','2005-08-09 00:00:00','c');
INSERT INTO `C` VALUES ('19:56:05','2001-09-02 22:50:02','m');
INSERT INTO `C` VALUES ('19:35:19','2005-12-16 22:58:11','y');
INSERT INTO `C` VALUES ('05:03:03','2007-04-19 00:19:53','f');
INSERT INTO `C` VALUES ('18:38:59','1900-01-01 00:00:00','d');
CREATE TABLE `B` (
  `time_key` time DEFAULT NULL,
  `datetime_nokey` datetime DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  KEY `time_key` (`time_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `B` VALUES ('01:13:38','2005-02-05 00:00:00','f');

 
SELECT STRAIGHT_JOIN OUTR .`varchar_nokey`  
FROM C OUTR2  JOIN B OUTR  ON OUTR2 .`time_key`  < OUTR .`datetime_nokey`     /* TRANSFORM_OUTCOME_UNORDERED_MATCH */;

DROP TABLE C;
DROP TABLE B;
#/* End of test case for query 1 */

Suggested fix:
Ensure consistent result sets for equivalent queries

EXPLAIN output:

Query 1:
EXPLAIN SELECT OUTR .`varchar_nokey`  
FROM C OUTR2  JOIN B OUTR  ON OUTR2 .`time_key`  < OUTR .`datetime_nokey`   ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	OUTR	ALL	NULL	NULL	NULL	NULL	1	
1	SIMPLE	OUTR2	ALL	time_key	NULL	NULL	NULL	20	Range checked for each record (index map: 0x1)

Query 2:
EXPLAIN SELECT STRAIGHT_JOIN OUTR .`varchar_nokey`  
FROM C OUTR2  JOIN B OUTR  ON OUTR2 .`time_key`  < OUTR .`datetime_nokey`     /*
TRANSFORM_OUTCOME_UNORDERED_MATCH */;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	OUTR2	index	time_key	time_key	4	NULL	20	Using index
1	SIMPLE	OUTR	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer