Bug #65970 AVG group function when dealing with maximum number give wrong results

• Submitted: 20 Jul 2012 21:50
• Modified: 21 Jul 2012 17:41
• Reporter: calle kula
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.5.19, 5.5.26
• OS: Any
• CPU Architecture: Any
• Tags: avg, Maximum value

[20 Jul 2012 21:50] calle kula

Description:
I have a table with a date and a double. For many rows the double contains the maximum value mysql allows for a double (1,7976....e308). Extract from the table: 

date            myDouble
...             ...
2010-01-10	1.7976931348623157e308
2009-12-18	1.7976931348623157e308
2009-12-17	1.7976931348623157e308
2009-12-16	1.7976931348623157e308
2009-12-15	1.7976931348623157e308
2009-12-14	1.7976931348623157e308
2009-01-10      1.7976931348623157e308

When I execute 

SELECT MAX(date), AVG(myDouble)FROM mytable GROUP BY YEARWEEK(date) ORDER BY date DESC

the AVG(myDouble) returns (2009-12-18	0) instead of (2009-12-18	1.7976931348623157e308)
 

How to repeat:
Create a table with a date and a double. Populate table with the data in the description. Execute query.

[21 Jul 2012 6:42] Valeriy Kravchuk

Please, send the exact CREATE TABLE statement used to create the table. Or, even better, just attach the dump of this table.

[21 Jul 2012 16:30] calle kula

Fair enough =) 

CREATE TABLE `fundamenta`.`myTestTable` (
  `companyID` int(11) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `myDouble` double DEFAULT NULL);
  
INSERT INTO myTestTable values (1, '2010-10-01', 1.7976931348623157e308);
INSERT INTO myTestTable values (1, '2010-10-02', 1.7976931348623157e308);
INSERT INTO myTestTable values (1, '2010-10-03', 1.7976931348623157e308);
INSERT INTO myTestTable values (1, '2010-10-04', 1.7976931348623157e308);
INSERT INTO myTestTable values (1, '2010-10-05', 1.7976931348623157e308);
INSERT INTO myTestTable values (1, '2010-10-06', 1.7976931348623157e308);
INSERT INTO myTestTable values (1, '2010-10-07', 1.7976931348623157e308);

SELECT MAX(date), AVG(myDouble) FROM myTestTable WHERE companyID = 1 GROUP BY YEARWEEK(date) ORDER BY date DESC;
 

hope this will help you reproduce the error

[21 Jul 2012 17:41] Valeriy Kravchuk

Thank you for the complete test case. Problem is easy visible with your table, data:

mysql> SELECT myDouble FROM myTestTable;
+------------------------+
| myDouble               |
+------------------------+
| 1.7976931348623157e308 |
| 1.7976931348623157e308 |
| 1.7976931348623157e308 |
| 1.7976931348623157e308 |
| 1.7976931348623157e308 |
| 1.7976931348623157e308 |
| 1.7976931348623157e308 |
+------------------------+
7 rows in set (0.00 sec)

and even more simple query:

mysql> SELECT AVG(myDouble) FROM myTestTable;
+---------------+
| AVG(myDouble) |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

Verified with 5.5.26 on Mac OS X also.