MySQL Bugs: #73543: SUM(DISTINCT ...) incorrectly converts DATE, DATETIME, and TIMESTAMP to YEAR

Bug #73543	SUM(DISTINCT ...) incorrectly converts DATE, DATETIME, and TIMESTAMP to YEAR
Submitted:	12 Aug 2014 0:28	Modified:	30 Sep 2014 16:26
Reporter:	Arthur O'Dwyer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.5.8, 5.5.31, 5.5.34, 5.5.40	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
SUM(DISTINCT x) returns the wrong answer whenever x is an expression of type DATE, DATETIME, TIME, or TIMESTAMP.  Apparently what's happening is that the timestamp type is being quietly converted to YEAR(4), or perhaps it's being converted to string and then back to integer or double. The weird and incorrect behavior is that this bad conversion happens **only** when the SUM(DISTINCT ...) modifier is used. Non-distinct SUM(...) produces the correct result.

How to repeat:
drop database if exists d;
create database d;
use d;
create table z (a date, b datetime, c timestamp);
insert into z values (now(), now(), now());

select * from z;
+------------+---------------------+---------------------+
| a          | b                   | c                   |
+------------+---------------------+---------------------+
| 2013-09-25 | 2013-09-25 23:28:58 | 2013-09-25 23:28:58 |
+------------+---------------------+---------------------+

select sum(a), sum(b), sum(c) from z;
+----------+-----------------------+----------------+
| sum(a)   | sum(b)                | sum(c)         |
+----------+-----------------------+----------------+
| 20130925 | 20130925232858.000000 | 20130925232858 | 
+----------+-----------------------+----------------+

select sum(distinct a), sum(distinct b), sum(distinct c) from z;
+-----------------+-----------------+-----------------+
| sum(distinct a) | sum(distinct b) | sum(distinct c) |
+-----------------+-----------------+-----------------+
| 2013            | 2013.000000     | 2013            | 
+-----------------+-----------------+-----------------+

And with TIME types, the same behavior:

mysql> SELECT SUM(CURTIME()), SUM(DISTINCT CURTIME()) FROM DUAL;
+----------------+-------------------------+
| SUM(CURTIME()) | SUM(DISTINCT CURTIME()) |
+----------------+-------------------------+
|  172436.000000 |               17.000000 |
+----------------+-------------------------+

Suggested fix:
The presence or absence of DISTINCT should affect only *which* and *how many* expressions are summed together; it should not cause changes to the *values* of those expressions.

Hello Arthur,

Thank you for the report and test case.
Verified as described.

Thanks,
Umesh

// 5.5.40

mysql> drop database if exists d;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create database d;
Query OK, 1 row affected (0.00 sec)

mysql> use d;
Database changed
mysql> create table z (a date, b datetime, c timestamp);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into z values (now(), now(), now());
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+----------------------------------------+
| Level | Code | Message                                |
+-------+------+----------------------------------------+
| Note  | 1265 | Data truncated for column 'a' at row 1 |
+-------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql> select * from z;
+------------+---------------------+---------------------+
| a          | b                   | c                   |
+------------+---------------------+---------------------+
| 2014-08-14 | 2014-08-14 06:22:54 | 2014-08-14 06:22:54 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> select sum(a), sum(b), sum(c) from z;
+----------+-----------------------+----------------+
| sum(a)   | sum(b)                | sum(c)         |
+----------+-----------------------+----------------+
| 20140814 | 20140814062254.000000 | 20140814062254 |
+----------+-----------------------+----------------+
1 row in set (0.00 sec)

mysql> select sum(distinct a), sum(distinct b), sum(distinct c) from z;
+-----------------+-----------------+-----------------+
| sum(distinct a) | sum(distinct b) | sum(distinct c) |
+-----------------+-----------------+-----------------+
|            2014 |     2014.000000 |            2014 |
+-----------------+-----------------+-----------------+
1 row in set (0.00 sec)

mysql> SELECT SUM(CURTIME()), SUM(DISTINCT CURTIME()) FROM DUAL;
+----------------+-------------------------+
| SUM(CURTIME()) | SUM(DISTINCT CURTIME()) |
+----------------+-------------------------+
|   62359.000000 |                6.000000 |
+----------------+-------------------------+
1 row in set (0.00 sec)

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.5.40                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.5.40                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | linux2.6                     |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)

// 5.6.21

mysql> drop database if exists d;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create database d;
Query OK, 1 row affected (0.00 sec)

mysql> use d;
Database changed
mysql> create table z (a date, b datetime, c timestamp);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into z values (now(), now(), now());
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from z;
+------------+---------------------+---------------------+
| a          | b                   | c                   |
+------------+---------------------+---------------------+
| 2014-08-14 | 2014-08-14 06:41:01 | 2014-08-14 06:41:01 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> select sum(a), sum(b), sum(c) from z;
+----------+----------------+----------------+
| sum(a)   | sum(b)         | sum(c)         |
+----------+----------------+----------------+
| 20140814 | 20140814064101 | 20140814064101 |
+----------+----------------+----------------+
1 row in set (0.00 sec)

mysql> select sum(distinct a), sum(distinct b), sum(distinct c) from z;
+-----------------+-----------------+-----------------+
| sum(distinct a) | sum(distinct b) | sum(distinct c) |
+-----------------+-----------------+-----------------+
|        20140814 |  20140814064101 |  20140814064101 |
+-----------------+-----------------+-----------------+
1 row in set (0.00 sec)

mysql> SELECT SUM(CURTIME()), SUM(DISTINCT CURTIME()) FROM DUAL;
+----------------+-------------------------+
| SUM(CURTIME()) | SUM(DISTINCT CURTIME()) |
+----------------+-------------------------+
|          64132 |                   64132 |
+----------------+-------------------------+
1 row in set (0.00 sec)

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.6.21                                                  |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.6.21-enterprise-commercial-advanced                   |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | Linux                                                   |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

Noted in 5.6.4 changelog.

SUM(DISTINCT) incorrectly converted DATE, DATETIME, TIME, and
TIMESTAMP arguments to YEAR.