Bug #105762 sum(least(a, NULL)) result error

• Submitted: 1 Dec 2021 9:42
• Modified: 17 Dec 2021 18:18
• Reporter: Yukun Liang
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.13, 8.0.27, 5.7.36
• OS: Any
• 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.