Elena Stepanova added a comment - 2014-12-10 03:02 - edited 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

Alexander Barkov added a comment - 2014-12-16 10:17 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.

Alexander Barkov added a comment - 2014-12-16 10:19 Intentional behaviour change.

Fred Rexon added a comment - 2014-12-19 17:31 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.

Alexander Barkov added a comment - 2014-12-19 23:07 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.

Kolbe Kegel (Inactive) added a comment - 2014-12-20 00:10 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;