Bug #46725 Incorrectly ordered result set returned when using MRR
Submitted: 14 Aug 2009 15:40 Modified: 14 Aug 2009 16:42
Reporter: Patrick Crews Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.4/6.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: incorrect order, mrr, optimizer_use_mrr, order

[14 Aug 2009 15:40] Patrick Crews
Description:
Azalea is returning an incorrectly ordered result set when optimizer_use_mrr='force'

When optimizer_use_mrr is turned off, the results of the query below match 5.1's results.  When it is on, it produces the diff below (azalea-> 5.1 comparison)

SELECT DISTINCT  table1 . `time_key` AS field1 
FROM ( D AS table1 LEFT  JOIN ( ( D AS table2 INNER JOIN B AS table3 ON (table3 . `int_nokey` = table2 . `int_key` ) ) ) ON (table3 . `int_key` = table2 . `int_key` ) ) 
WHERE (   EXISTS ( ( 
SELECT  SQL_SMALL_RESULT SUBQUERY1_t2 . `int_nokey` AS SUBQUERY1_field1 
FROM ( B AS SUBQUERY1_t1 STRAIGHT_JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `pk` = SUBQUERY1_t1 . `pk` ) )  
GROUP BY SUBQUERY1_field1  ) ) ) AND table1 . `int_key` NOT IN (115, 100, 142)   
ORDER BY table1 . `int_key` DESC , field1 
LIMIT 10 OFFSET 8;

/* Diff: */

/* --- /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen10305-1250180870-server0.dump	2009-08-13 12:27:50.000000000 -0400
# +++ /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen10305-1250180870-server1.dump	2009-08-13 12:27:50.000000000 -0400
# @@ -1,10 +1,10 @@
# -00:00:00
#  00:22:24
#  09:58:06
#  10:58:07
#  11:31:12
#  11:46:03
#  18:09:07
# +18:30:05
# +18:45:35
#  21:22:47
#  23:15:50
# -NULL */

How to repeat:
Run the following test case:

You will need to comment / uncomment the appropriate Server 0 / Server 1 lines depending on which version of the server you are testing.

Toggle the optimizer_use_mrr variable between force and disable and observe the change in query results (compare to 5.1)

/* Server0: version = 5.4.4-alpha-gcov-debug-log */
/* Server1: version = 5.1.38-debug-log */

/* The value of optimizer_switch is distinct between the two servers: */
Server 0 : SET SESSION optimizer_switch = 'firstmatch=on,index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,loosescan=on,materialization=off,semijoin=on';
#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: */
 Server 0 : 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: */
 Server 0 : SET SESSION join_cache_level = 1; 
# Server 1 : SET SESSION join_cache_level = '' ;

# NOTE - no simplified test case.  The RQG produced a test case
# without an appropriate ORDER BY clause and adding it to the automatically
# simplified case does not repeat this bug

#/* Begin test case for query 1 */

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

CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) DEFAULT NULL,
  `int_key` int(11) DEFAULT NULL,
  `time_key` time DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `time_key` (`time_key`),
  KEY `varchar_key` (`int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,7,9,'20:25:14'),(2,9,3,'13:47:24'),(3,7,9,'19:24:11'),(4,4,NULL,'15:59:13'),(5,2,9,'00:00:00'),(6,6,3,'15:15:04'),(7,8,8,'11:32:06'),(8,NULL,8,'18:32:33'),(9,5,53,'15:19:25'),(10,NULL,0,'19:03:19'),(11,6,5,'00:39:46'),(12,188,166,NULL),(13,2,3,'00:00:00'),(14,1,0,'13:12:11'),(15,1,1,'04:56:48'),(16,0,9,'19:56:05'),(17,9,5,'19:35:19'),(18,NULL,6,'05:03:03'),(19,4,2,'18:38:59'),(20,6,NULL,NULL);
CREATE TABLE `D` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) DEFAULT NULL,
  `int_key` int(11) DEFAULT NULL,
  `time_key` time DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `time_key` (`time_key`),
  KEY `varchar_key` (`int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
INSERT INTO `D` VALUES (1,8,0,'14:34:45'),(2,6,0,'11:49:48'),(3,6,7,'18:12:55'),(4,3,8,'18:30:05'),(5,9,4,'14:19:30'),(6,2,6,'05:20:04'),(7,1,5,'20:29:31'),(8,8,NULL,'07:08:09'),(9,0,NULL,'14:49:14'),(10,9,8,'00:00:00'),(11,8,8,'09:58:06'),(12,NULL,8,NULL),(13,0,1,'18:24:16'),(14,5,1,'17:39:57'),(15,1,5,'08:23:21'),(16,1,2,NULL),(17,6,7,'21:50:46'),(18,6,6,'12:33:17'),(19,1,4,'03:06:43'),(20,8,7,'03:46:14'),(21,1,0,'20:34:52'),(22,0,9,NULL),(23,4,3,'10:41:20'),(24,9,5,'08:43:11'),(25,8,1,NULL),(26,5,1,'10:17:51'),(27,9,5,'06:34:09'),(28,5,9,'21:22:47'),(29,NULL,2,'04:02:32'),(30,NULL,5,'02:33:14'),(31,105,248,'16:32:56'),(32,0,0,'21:32:42'),(33,3,8,'23:04:47'),(34,1,1,'22:05:43'),(35,75,255,'02:05:45'),(36,9,9,'00:00:00'),(37,7,9,'18:09:07'),(38,NULL,3,'10:54:06'),(39,NULL,9,'23:15:50'),(40,4,6,'10:17:40'),(41,0,4,'03:37:09'),(42,204,60,'22:26:06'),(43,0,7,'17:10:38'),(44,9,1,'00:00:00'),(45,8,6,'17:08:49'),(46,7,4,'19:04:40'),(47,8,NULL,'20:53:28'),(48,NULL,8,'11:46:03'),(49,6,0,NULL),(50,5,8,'10:58:07'),(51,2,151,'00:00:00'),(52,3,7,'09:43:15'),(53,7,6,'21:40:32'),(54,NULL,NULL,'00:17:44'),(55,145,23,'16:47:26'),(56,0,2,'00:00:00'),(57,1,4,'05:25:59'),(58,7,NULL,'00:00:00'),(59,3,NULL,'20:33:04'),(60,NULL,77,'00:46:12'),(61,2,NULL,'00:00:00'),(62,2,NULL,'02:13:59'),(63,2,7,'02:54:47'),(64,8,1,'18:13:59'),(65,6,9,'13:53:08'),(66,1,6,'22:21:30'),(67,NULL,2,'11:41:50'),(68,1,3,'15:20:02'),(69,0,0,NULL),(70,4,NULL,NULL),(71,1,6,'07:51:52'),(72,1,3,'00:00:00'),(73,27,195,NULL),(74,4,5,NULL),(75,6,2,'12:31:05'),(76,2,7,'00:00:00'),(77,248,25,'01:16:45'),(78,NULL,NULL,'20:38:54'),(79,9,0,'00:30:15'),(80,75,98,'23:46:36'),(81,2,6,'19:03:13'),(82,9,5,'10:54:27'),(83,4,0,'00:25:47'),(84,0,3,'08:44:27'),(85,0,1,'08:15:38'),(86,1,1,'19:56:21'),(87,119,147,'00:00:00'),(88,1,3,'20:50:52'),(89,7,3,'03:54:39'),(90,2,NULL,'23:58:17'),(91,7,2,'12:54:58'),(92,2,1,'04:02:43'),(93,6,8,'11:31:12'),(94,4,8,'20:20:04'),(95,5,8,'00:22:24'),(96,7,0,'10:09:31'),(97,1,1,NULL),(98,7,8,'18:45:35'),(99,2,5,'11:49:25'),(100,1,4,'12:52:58');
CREATE TABLE `B` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) DEFAULT NULL,
  `int_key` int(11) DEFAULT NULL,
  `time_key` time DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `time_key` (`time_key`),
  KEY `varchar_key` (`int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
INSERT INTO `B` VALUES (1,1,7,'01:13:38'),(2,NULL,2,'11:28:45');

 
SELECT DISTINCT  table1 . `time_key` AS field1 
FROM ( D AS table1 LEFT  JOIN ( ( D AS table2 INNER JOIN B AS table3 ON (table3 . `int_nokey` = table2 . `int_key` ) ) ) ON (table3 . `int_key` = table2 . `int_key` ) ) 
WHERE (   EXISTS ( ( 
SELECT  SQL_SMALL_RESULT SUBQUERY1_t2 . `int_nokey` AS SUBQUERY1_field1 
FROM ( B AS SUBQUERY1_t1 STRAIGHT_JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `pk` = SUBQUERY1_t1 . `pk` ) )  
GROUP BY SUBQUERY1_field1  ) ) ) AND table1 . `int_key` NOT IN (115, 100, 142)   
ORDER BY table1 . `int_key` DESC , field1 
LIMIT 10 OFFSET 8;

/* Diff: */

/* --- /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen10305-1250180870-server0.dump	2009-08-13 12:27:50.000000000 -0400
# +++ /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen10305-1250180870-server1.dump	2009-08-13 12:27:50.000000000 -0400
# @@ -1,10 +1,10 @@
# -00:00:00
#  00:22:24
#  09:58:06
#  10:58:07
#  11:31:12
#  11:46:03
#  18:09:07
# +18:30:05
# +18:45:35
#  21:22:47
#  23:15:50
# -NULL */

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

Suggested fix:
Ensure consistent, correct query processing regardless of optimizer settings.
[14 Aug 2009 16:42] Patrick Crews
Was incorrect in my interpretation of the ORDER BY.  The use of the int_key column affects the result set.  Altering the ORDER BY clause produces consistent result sets in both versions of the server.