Bug #106208 IF function with DECIMAL get different precision result

• Submitted: 19 Jan 2022 7:37
• Modified: 19 Jan 2022 9:08
• Reporter: Shenghui Wu
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.27, 8.0.28, 5.7.37
• OS: Any
• CPU Architecture: Any

[19 Jan 2022 7:37] Shenghui Wu

Description:
Test Case:
drop table t;
create table t(a decimal(5,3), b decimal(10,4), c int);
insert into t values(33.333, null, null);
insert into t values(null, 44.4444, 1);

MySQL [test]> select ifnull(a,b), if(a is not null, a, b) from t;
+-------------+-------------------------+
| ifnull(a,b) | if(a is not null, a, b) |
+-------------+-------------------------+
|     33.3330 |                  33.333 |
|     44.4444 |                 44.4444 |
+-------------+-------------------------+
2 rows in set (0.000 sec)

MySQL [test]> select ifnull(a,c), if(a is not null, a, c) from t;
+-------------+-------------------------+
| ifnull(a,c) | if(a is not null, a, c) |
+-------------+-------------------------+
|      33.333 |                  33.333 |
|       1.000 |                       1 |
+-------------+-------------------------+
2 rows in set (0.000 sec)

IF function should keep all results with the same precision.

How to repeat:
See description

[19 Jan 2022 9:08] MySQL Verification Team

Hello Shenghui Wu,

Thank you for the report and test case.
Observed that 5.7/8.0 affected.

regards,
Umesh

[21 Jan 2022 9:27] Xiaochen Cui

This issue is caused by `my_decimal_round` in the function `Item_func_numhybrid::val_str`, and was present in all versions: 5.5, 5.6, 5.7, 8.0.

code:

```cpp
String *Item_func_numhybrid::val_str(String *str) {
  assert(fixed == 1);
  switch (hybrid_type) {
    case DECIMAL_RESULT: {
      my_decimal decimal_value, *val;
      if (!(val = decimal_op(&decimal_value))) return nullptr;  // null is set
      my_decimal_round(E_DEC_FATAL_ERROR, val, decimals, false, val);  // <- The root cause of the problem. [version: mysql-8.0.27] [location: sql/item_func.cc:1599]
      str->set_charset(collation.collation);
      my_decimal2string(E_DEC_FATAL_ERROR, val, str);
      break;
    }
```

It’s easy to keep output precision aligned with the table scheme by simply removing it.

So the key question is whether alignment accuracy it is a feature when different decimal type is mixed in a single column. If yes, then the bug probably doesn't need to be fixed. If not, simply removing the line may solve the problem.

Or we could let the user choose whether to do precision alignment, although I don't think that's a good idea.