|
After fixes made in MDEV-23337, ROUND() returns good results for integer input:
CREATE OR REPLACE TABLE t1 (a bigint(21) unsigned);
|
CREATE OR REPLACE TABLE t2 AS SELECT ROUND(a),TRUNCATE(a,0),FLOOR(a),CEILING(a) FROM t1;
|
DESC t2;
|
+---------------+---------------------+------+-----+---------+-------+
|
| Field | Type | Null | Key | Default | Extra |
|
+---------------+---------------------+------+-----+---------+-------+
|
| ROUND(a,0) | bigint(21) unsigned | YES | | NULL | |
|
| TRUNCATE(a,0) | bigint(21) unsigned | YES | | NULL | |
|
| FLOOR(a) | bigint(21) unsigned | YES | | NULL | |
|
| CEILING(a) | bigint(21) unsigned | YES | | NULL | |
|
+---------------+---------------------+------+-----+---------+-------+
|
However, if the length get longer than 21, the result is still not nice:
CREATE OR REPLACE TABLE t1 (a bigint(22) unsigned);
|
CREATE OR REPLACE TABLE t2 AS SELECT ROUND(a),TRUNCATE(a,0),FLOOR(a),CEILING(a) FROM t1;
|
DESC t2;
|
+---------------+------------------------+------+-----+---------+-------+
|
| Field | Type | Null | Key | Default | Extra |
|
+---------------+------------------------+------+-----+---------+-------+
|
| ROUND(a,0) | decimal(22,0) unsigned | YES | | NULL | |
|
| TRUNCATE(a,0) | bigint(22) unsigned | YES | | NULL | |
|
| FLOOR(a) | bigint(22) unsigned | YES | | NULL | |
|
| CEILING(a) | bigint(22) unsigned | YES | | NULL | |
|
+---------------+------------------------+------+-----+---------+-------+
|
Notice, decimal(22,0) instead of bigint(22), which is the data type of the argument.
ROUND(bigint,0) cannot go outside of the BIGINT limit. No needs to create the decimal data type.
|