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

[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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.