The problem was introduced in 5.3 tree (and further in 5.5.33 and 10.0.5) with the following revision:

revno: 3689

revision-id: bar@mnogosearch.org-20130909113225-k56ivrazr3wq1up4

parent: igor@askmonty.org-20130906165532-5v2ybq2t8f0o9pke

committer: Alexander Barkov <bar@mnogosearch.org>

branch nick: maria-5.3.b4863

timestamp: Mon 2013-09-09 15:32:25 +0400

message:

  MDEV-4863 COALESCE(time_or_datetime) returns wrong results in numeric context

MDEV-4863 was an intentional change, to make behaviour more consistent in different context, and closer to the standard.
All control functions that are CASE abbreviations, including IF, now aggregate the return choice arguments in the same way, even if the switch argument is a constant.

Behaviour before the fix was inconsistent:

mysql> SELECT IF(1=1,ROUND(0.123451, 3), ROUND(0.123451, 5)) AS `IF`, COALESCE (ROUND(0.123451,3),ROUND(0.123451, 5)) AS `COALESCE`, CASE WHEN 1 THEN ROUND(0.123451, 3) ELSE  ROUND(0.123451, 5) END AS `CASE`,IFNULL(ROUND(0.123451, 3), ROUND(0.123451, 5)) AS `IFNULL`;

+-------+----------+-------+---------+

| IF    | COALESCE | CASE  | IFNULL  |

+-------+----------+-------+---------+

| 0.123 |  0.12300 | 0.123 | 0.12300 |

+-------+----------+-------+---------+

Notice, all these expressions effectively return the first argument,
but the output format was different.

Behaviour after the fix:

mysql> SELECT IF(1=1,ROUND(0.123451, 3), ROUND(0.123451, 5)) AS `IF`, COALESCE (ROUND(0.123451,3),ROUND(0.123451, 5)) AS `COALESCE`, CASE WHEN 1 THEN ROUND(0.123451, 3) ELSE  ROUND(0.123451, 5) END AS `CASE`,IFNULL(ROUND(0.123451, 3), ROUND(0.123451, 5)) AS `IFNULL`;

+---------+----------+---------+---------+

| IF      | COALESCE | CASE    | IFNULL  |

+---------+----------+---------+---------+

| 0.12300 |  0.12300 | 0.12300 | 0.12300 |

+---------+----------+---------+---------+

All CASE and its abbreviations return exactly the same result.

Intentional behaviour change.

The expected and documented behavior of ROUND(nnn, x) is to return a string with x number of decimals, not x number of decimal precision padded with the maximum x in any ROUND/TRUNCATE that is part of a different clause.

It seems like you originally got a mix of good and bad results (good - IF/CASE, bad COALESCE/IFNULL) and decided to standardize on the BAD. This is bizarre.

The only thing I can think of that would cause this decision is that you'd want consistent output out of COALESCE, which could easily have been handled by putting the COALESCE within the ROUND, not by ignoring the plain intent of IF, IFNULL and CASE.

SELECT ROUND(COALESCE(0.123, 0.12345), 5)

Works the same in both MySQL and MariaDB.

IF (1=1,ROUND(0.123451, 3), ROUND(0.123451, 5))

is a CASE abbreviation:

CASE WHEN 1=1 THEN (0.123451, 3) ELSE ROUND(0.123451, 5) END

According to the SQL standard, the result type for CASE in this particulate case is
exact numeric, and the result's scale is the biggest scale of any of the aggregated values,
which is 5.

If the goal is indeed to have variable precision for the result, the condition should be placed inside of the ROUND() function, not around it. For example, instead of this:

SELECT IF (1=1, ROUND(0.123451, 3), ROUND(0.123451, 5)) as result;

Use this instead:

SELECT  ROUND(0.123451, IF (1=1, 3, 5)) as result;