Description:
The ROUND(X,D) operation return unexpected value when b is defined in tables
For example:there's a table like this(a DECIMAL(4, 3), b INT):
+-------+------+
| a | b |
+-------+------+
| 1.000 | 1 |
| 2.000 | 2 |
| 3.000 | 3 |
| 4.000 | 4 |
+-------+------+
when we do :SELECT ROUND(a,b) from t1 ; We still got 3 decimal places in each row ,which is obviously wrong.
In another case :SELECT ROUND(a,b-2) from t1 WHERE a=1 and b=1;
We can get 0 but with 3 decimal places .According to the define of ROUND(X,D), it seems to have right value but wrong decimal.
Then I try SELECT ROUND(a,-1) from t1 WHERE a=1; get right value 0
How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1( a DECIMAL(4, 3), b INT );
INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4);
SELECT ROUND(a,b) from t1 ;
+------------+
| ROUND(a,b) |
+------------+
| 1.000 |
| 2.000 |
| 3.000 |
| 4.000 |
+------------+
SELECT ROUND(a,b) from t1 where a=4 and b=4 ;
+------------+
| ROUND(a,b) |
+------------+
| 4.000 |
+------------+
SELECT ROUND(a,b-2) from t1 WHERE a=1 and b=1;
+--------------+
| ROUND(a,b-2) |
+--------------+
| 0.000 |
+--------------+
SELECT ROUND(a,-1) from t1 WHERE a=1;
+-------------+
| ROUND(a,-1) |
+-------------+
| 0 |
+-------------+
