Bug #73927 SELECT DISTINCT SUM() RETURNS DUPLICATE ROWS
Submitted: 16 Sep 2014 10:12 Modified: 9 Feb 2015 17:48
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any

[16 Sep 2014 10:12] Guilhem Bichot
Description:
verified with trunk revision-id:sayantan.dutta@oracle.com-20140916082540-hq7b3vpurt40w1bb ; have not checked 5.6

CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
`c3` int(11) DEFAULT NULL,
`c4` int(11) DEFAULT NULL,
`c5` int(11) DEFAULT NULL,
`c6` int(11) DEFAULT NULL,
`c7` int(11) DEFAULT NULL,
`c8` int(11) DEFAULT NULL,
`c9` int(11) DEFAULT NULL,
`c10` int(11) DEFAULT NULL,
`c11` int(11) DEFAULT NULL,
`c12` int(11) DEFAULT NULL,
`c13` int(11) DEFAULT NULL,
`c14` int(11) DEFAULT NULL,
`c15` int(11) DEFAULT NULL,
`c16` int(11) DEFAULT NULL,
`c17` int(11) DEFAULT NULL,
`c18` int(11) DEFAULT NULL,
`c19` int(11) DEFAULT NULL,
`c20` int(11) DEFAULT NULL,
`c21` int(11) DEFAULT NULL,
`c22` int(11) DEFAULT NULL,
`c23` int(11) DEFAULT NULL,
`c24` int(11) DEFAULT NULL,
`c25` int(11) DEFAULT NULL,
`c26` int(11) DEFAULT NULL,
`c27` int(11) DEFAULT NULL,
`c28` int(11) DEFAULT NULL,
`c29` int(11) DEFAULT NULL,
`c30` int(11) DEFAULT NULL,
`c31` int(11) DEFAULT NULL,
`c32` int(11) DEFAULT NULL,
`c33` int(11) DEFAULT NULL,
`c34` int(11) DEFAULT NULL,
`c35` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into t1 (a,b) values (1,4), (2,2),(2,2), (4,1),(4,1),(4,1),(4,1);
select sum(b) from t1 group by a, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30, c31, c32, c33, c34, c35;
sum(b)
4
4
4
select distinct sum(b) from t1 group by a, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30, c31, c32, c33, c34, c35;
sum(b)
4
4
4

The second SELECT is the same as the first but with an added DISTINCT word; as there are 3 rows containing "4" in the first SELECT's result, the second SELECT should return one row, not four.

How to repeat:
see description

Suggested fix:
this big number of columns triggers the use of "unique constraint" (hash_field) in the internal tmp table used for GROUP BY. This is necessary for the bug to appear.
[9 Feb 2015 17:48] Paul DuBois
Noted in 5.7.6 changelog.

The capabililty of using InnoDB for temporary tables in MySQL 5.7.5
resulted in certain queries failing: Some queries involving
multiple-table UPDATE, queries involving long PRIMARY KEY values, and
queries involving DISTINCT SUM().
[24 Mar 2015 21:34] Todd Farmer
Does not affect 5.6.