Bug #105762 sum(least(a, NULL)) result error
Submitted: 1 Dec 2021 9:42 Modified: 17 Dec 2021 18:18
Reporter: Yukun Liang Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.13, 8.0.27, 5.7.36 OS:Any
Assigned to: CPU Architecture:Any

[1 Dec 2021 9:42] Yukun Liang
Description:
least(a, NULL) will return a in SUM/AVG function

How to repeat:
mysql> create table t1 (a double, b double);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t1 values(1, NULL);
Query OK, 1 row affected (0.03 sec)

mysql> insert into t1 values(1, 1);
Query OK, 1 row affected (0.04 sec)

mysql> select sum(least(a, b)) from t1;
+------------------+
| sum(least(a, b)) |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

Suggested fix:
Item_func_min_max::real_op() should return 0.0 if null_value is TRUE.

for (uint i = 0; i < arg_count; i++) {
  const double tmp = args[i]->val_real();
  if ((null_value = args[i]->null_value)) break; // return 0.0
  if (i == 0 || (tmp < result) == m_is_least_func) result = tmp;
}
[1 Dec 2021 10:09] MySQL Verification Team
Hello Yukun Liang,

Thank you for the report and test case.

regards,
Umesh
[17 Dec 2021 18:18] Christine Cole
Posted by developer:
 
Fixed as of the upcoming MySQL 8.0.29 release, and here's the proposed changelog entry from the documentation team:

The functions MIN() and MAX() should return zero if they encounter a NULL
value, as is done currently for integer and decimal types, but returned an
incorrect result for double types.

Thank you for the bug report.