Bug #48092 Addition of DISTINCT clause results in incorrect exclusions from result set
Submitted: 15 Oct 2009 17:38 Modified: 15 Oct 2009 18:49
Reporter: Patrick Crews Email Updates:
Status: Not a Bug 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: distinct, regression

[15 Oct 2009 17:38] Patrick Crews
Description:
These two similar queries:
Simplified query:  SELECT `int_key`  FROM C  GROUP  BY `time_nokey`   
Simplified transformed query:  SELECT DISTINCT `int_key`  FROM C  GROUP  BY `time_nokey` 

Produce this diff of result sets.  Everything is ok, except for the removal of *both* 0 rows from the result set.  One of them should remain with the addition of DISTINCT.

This is present in 5.1  and the engine_condition_pushdown setting did not appear to affect this bug.
# 07:08:10 @@ -1,19 +1,10 @@
# 07:08:10 -0
# 07:08:10 -0
# 07:08:10  1
# 07:08:10  166
# 07:08:10  2
# 07:08:10 -2
# 07:08:10 -3
# 07:08:10  3
# 07:08:10  5
# 07:08:10 -5
# 07:08:10  53
# 07:08:10  6
# 07:08:10  8
# 07:08:10 -8
# 07:08:10 -9
# 07:08:10 -9
# 07:08:10 -9
# 07:08:10  9
# 07:08:10  NULL

How to repeat:
MTR test case:
Set up for 6.0 - comment out all SET SESSION statements except for engine_condition_pushdown to run on 5.1

#/* 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 = 1 */;
/*!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` (
  `int_key` int(11) DEFAULT NULL,
  `time_nokey` time DEFAULT NULL,
  KEY `int_key` (`int_key`),
  KEY `varchar_key` (`int_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (2,'11:28:45');
INSERT INTO `C` VALUES (9,'20:25:14');
INSERT INTO `C` VALUES (3,'13:47:24');
INSERT INTO `C` VALUES (9,'19:24:11');
INSERT INTO `C` VALUES (NULL,'15:59:13');
INSERT INTO `C` VALUES (9,'00:00:00');
INSERT INTO `C` VALUES (3,'15:15:04');
INSERT INTO `C` VALUES (8,'11:32:06');
INSERT INTO `C` VALUES (8,'18:32:33');
INSERT INTO `C` VALUES (53,'15:19:25');
INSERT INTO `C` VALUES (0,'19:03:19');
INSERT INTO `C` VALUES (5,'00:39:46');
INSERT INTO `C` VALUES (166,NULL);
INSERT INTO `C` VALUES (3,'00:00:00');
INSERT INTO `C` VALUES (0,'13:12:11');
INSERT INTO `C` VALUES (1,'04:56:48');
INSERT INTO `C` VALUES (9,'19:56:05');
INSERT INTO `C` VALUES (5,'19:35:19');
INSERT INTO `C` VALUES (6,'05:03:03');
INSERT INTO `C` VALUES (2,'18:38:59');

 
SELECT `int_key`  
FROM C  
GROUP  BY `time_nokey`   ;

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

#/* Begin test case for query 1 */

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

CREATE TABLE `C` (
  `int_key` int(11) DEFAULT NULL,
  `time_nokey` time DEFAULT NULL,
  KEY `int_key` (`int_key`),
  KEY `varchar_key` (`int_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (2,'11:28:45');
INSERT INTO `C` VALUES (9,'20:25:14');
INSERT INTO `C` VALUES (3,'13:47:24');
INSERT INTO `C` VALUES (9,'19:24:11');
INSERT INTO `C` VALUES (NULL,'15:59:13');
INSERT INTO `C` VALUES (9,'00:00:00');
INSERT INTO `C` VALUES (3,'15:15:04');
INSERT INTO `C` VALUES (8,'11:32:06');
INSERT INTO `C` VALUES (8,'18:32:33');
INSERT INTO `C` VALUES (53,'15:19:25');
INSERT INTO `C` VALUES (0,'19:03:19');
INSERT INTO `C` VALUES (5,'00:39:46');
INSERT INTO `C` VALUES (166,NULL);
INSERT INTO `C` VALUES (3,'00:00:00');
INSERT INTO `C` VALUES (0,'13:12:11');
INSERT INTO `C` VALUES (1,'04:56:48');
INSERT INTO `C` VALUES (9,'19:56:05');
INSERT INTO `C` VALUES (5,'19:35:19');
INSERT INTO `C` VALUES (6,'05:03:03');
INSERT INTO `C` VALUES (2,'18:38:59');

 
SELECT DISTINCT `int_key`  
FROM C  
GROUP  BY `time_nokey`    /* TRANSFORM_OUTCOME_DISTINCT */ ;

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

Suggested fix:
Ensure correct query processing.
[15 Oct 2009 17:40] Patrick Crews
NOTE:  The test case uses Innodb tables, but the bug is repeatable with MyISAM as well.
[15 Oct 2009 18:49] Patrick Crews
The results were being thrown off by the GROUP BY clause / my mistake.