Bug #46277 differing results for equivalent queries using VAR_POP() & AVG() with DISTINCT
Submitted: 17 Jul 2009 21:33
Reporter: Patrick Crews Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0, 5.1, 5.4, 6.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: aggregate functions, avg, distinct, VAR_POP
Triage: Triaged: D2 (Serious)

[17 Jul 2009 21:33] Patrick Crews
Description:
Two equivalent queries using VAR_POP return differing result sets.  The only addition is the use of DISTINCT:

SELECT  VAR_POP( `int_nokey`  )
FROM C
GROUP  BY `int_key`   ;

returns 8 rows while

SELECT DISTINCT  VAR_POP( `int_nokey`  )
FROM C
GROUP  BY `int_key`    /* TRANSFORM_OUTCOME_DISTINCT */ ;
VAR_POP( `int_nokey`  )

returns only the first row from the previous query's result set.

This is present in 5.0+

How to repeat:
/*!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 = 0 */;
/*!50400 SET SESSION join_cache_level = 1 */;

# Begin test case for query 0

--disable_warnings
DROP TABLE IF EXISTS C;
--enable_warnings

CREATE TABLE `C` (
  `int_nokey` int(11) NOT NULL,
  `int_key` int(11) NOT NULL,
  KEY `int_key` (`int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (9,9),(0,0),(8,6),(3,6),(7,6),(0,4),(1,7),(9,4),(0,8),(9,4),(0,7),(5,5),(0,0),(8,5),(8,7),(5,2),(1,8),(7,0),(0,9),(9,5);

 
SELECT  VAR_POP( `int_nokey`  )  
FROM C  
GROUP  BY `int_key`   ;

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_nokey` int(11) NOT NULL,
  `int_key` int(11) NOT NULL,
  KEY `int_key` (`int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (9,9),(0,0),(8,6),(3,6),(7,6),(0,4),(1,7),(9,4),(0,8),(9,4),(0,7),(5,5),(0,0),(8,5),(8,7),(5,2),(1,8),(7,0),(0,9),(9,5);

 
SELECT DISTINCT  VAR_POP( `int_nokey`  )  
FROM C  
GROUP  BY `int_key`    /* TRANSFORM_OUTCOME_DISTINCT */ ;

DROP TABLE C;

# End of test case for query 1

Suggested fix:
Ensure equivalent queries return consistent result sets.