How to get second argument of Round() to work with

Question

I've a simple table with an integer column:

# setup table:
create table t(x int); insert t select 1;

The query select round(1.234, 1) from t returns 1.2 as expected.

However, select round(1.234, x) from t returns 1.2000. (It should return 1.2, as per the docs.)

Is this a bug? (Tested on version 5.5.10 and latest 5.6.24.)

Or, is there any particular technical reason why columns cannot be used in the second argument of round?

How can we get round to work even while there are columns used in the second argument?

Answer 1

Consider using FORMAT instead of ROUND:

mysql> SELECT FORMAT(1.234, x) FROM ( SELECT 1 AS x ) y;
+------------------+
| FORMAT(1.234, x) |
+------------------+
| 1.2              |
+------------------+

If that is not satisfactory, file a bug about ROUND.

Answer 2

This bug is not restricted to ROUND(), but also happens for TRUNCATE().

The accepted answer/workaround from @RickJames is not entirely correct, since FORMAT() introduces thousand separators: FORMAT() documentation

Correct workaround for arbitrary values:

SELECT REPLACE(FORMAT(t.raw_number, t.decimal_places), ',', '')
FROM   my_table t;

(Adding my comment as separate answer, since I do not have enough points to comment...)