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.
