Bug #79410 "coalesce(0, 1.2345)" is not returning 0.0000 with aggregate func in select list

• Submitted: 25 Nov 2015 13:13
• Modified: 25 Nov 2015 14:50
• Reporter: Su Dylan
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.7.8
• OS: Any
• CPU Architecture: Any

[25 Nov 2015 13:13] Su Dylan

Description:
Output:
=======
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1(c1 int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(1);
 coalQuery OK, 1 row affected (0.00 sec)

mysql> select coalesce(0, 1.2345);
+---------------------+
| coalesce(0, 1.2345) |
+---------------------+
|              0.0000 |
+---------------------+
1 row in set (0.00 sec)

mysql> select coalesce(0, 1.2345), sum(coalesce(0, 1.2345)), sum(1) from t1 as t2, t1 as t3 where t2.c1=t3.c1;
+---------------------+--------------------------+--------+
| coalesce(0, 1.2345) | sum(coalesce(0, 1.2345)) | sum(1) |
+---------------------+--------------------------+--------+
|                   0 |                   0.0000 |      1 |
+---------------------+--------------------------+--------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.8-rc  |
+-----------+
1 row in set (0.00 sec)

Problem:
========
"coalesce(0, 1.2345)" is expected to return 0.0000, with or without any aggregate functions.

How to repeat:

drop table if exists t1;
create table t1(c1 int);
insert into t1 values(1);
select coalesce(0, 1.2345);
select coalesce(0, 1.2345), sum(coalesce(0, 1.2345)), sum(1) from t1 as t2, t1 as t3 where t2.c1=t3.c1;

Suggested fix:
"coalesce(0, 1.2345)" is expected to return 0.0000, with or without any aggregate functions.

[25 Nov 2015 14:00] MySQL Verification Team

Thank you for the bug report. Not repeatable anymore with source server build:

mysql> select coalesce(0, 1.2345);
+---------------------+
| coalesce(0, 1.2345) |
+---------------------+
|              0.0000 |
+---------------------+
1 row in set (0.00 sec)

mysql> select coalesce(0, 1.2345), sum(coalesce(0, 1.2345)), sum(1) from t1 as t2, t1 as t3 where t2.c1=t3.c1;
+---------------------+--------------------------+--------+
| coalesce(0, 1.2345) | sum(coalesce(0, 1.2345)) | sum(1) |
+---------------------+--------------------------+--------+
|                   0 |                   0.0000 |      1 |
+---------------------+--------------------------+--------+
1 row in set (0.00 sec)

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

mysql 5.7 > select coalesce(0, 1.2345);
+---------------------+
| coalesce(0, 1.2345) |
+---------------------+
|              0.0000 |
+---------------------+
1 row in set (0.02 sec)

mysql 5.7 > select coalesce(0, 1.2345), sum(coalesce(0, 1.2345)), sum(1) from t1 as t2, t1 as t3 where t2.c1=t3.c1;
+---------------------+--------------------------+--------+
| coalesce(0, 1.2345) | sum(coalesce(0, 1.2345)) | sum(1) |
+---------------------+--------------------------+--------+
|              0.0000 |                   0.0000 |      1 |
+---------------------+--------------------------+--------+
1 row in set (0.02 sec)

mysql 5.7 > show variables like "%version%";
+-------------------------+---------------------------------------+
| Variable_name           | Value                                 |
+-------------------------+---------------------------------------+
| innodb_version          | 5.7.10                                |
| protocol_version        | 10                                    |
| slave_type_conversions  |                                       |
| version                 | 5.7.10                                |
| version_comment         | Source distribution PULL: 2015-NOV-07 |
| version_compile_machine | x86_64                                |
| version_compile_os      | Win64                                 |
+-------------------------+---------------------------------------+
7 rows in set (0.00 sec)

[25 Nov 2015 14:50] Su Dylan

Hi Miguel,

From the result you provided with 5.7.9 and 5.7.10, it seems that 5.7.9 result has this bug, and it is fixed in 5.7.10.