Bug #83320 round(bin) returns unexpected negative results
Submitted: 10 Oct 2016 13:02 Modified: 17 Feb 2021 11:18
Reporter: 帅 Bang Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.6, 5.6.33, 5.7.15 OS:Linux
Assigned to: CPU Architecture:Any

[10 Oct 2016 13:02] 帅 Bang
Description:
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1(c1 bit(64), c3 decimal(30, 5));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(0xFFFFFFFFFFFFFFFF, 456);
Query OK, 1 row affected (0.00 sec)

mysql> select round(c1, c3)  from t1;
+---------------+
| round(c1, c3) |
+---------------+
|            -1 |
+---------------+
1 row in set (0.00 sec)

-1 ? well, IMHO , this is not reasonable.  Why? Let us dig into this :

Connect mysql with option --column-type-info, just like this:

mysql -uroot -hhost -Pport --column-type-info;

and again, we get :

mysql> select round(c1, c3)  from t1;
Field   1:  `round(c1, c3)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DOUBLE
Collation:  binary (63)
Length:     17
Max_length: 2
Decimals:   0
Flags:      UNSIGNED BINARY NUM //unsigned !!!  so -1 is unexpected

+---------------+
| round(c1, c3) |
+---------------+
|            -1 |
+---------------+
1 row in set (0.00 sec)

Let us see what will happen if it were bigint unsigned.

mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1(c1  bigint unsigned,  c3 decimal(30, 5));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(0xFFFFFFFFFFFFFFFF, 456);
Query OK, 1 row affected(0.00 sec)

mysql> select round(c1, c3)  from t1;
Field   1:  `round(c1, c3)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DOUBLE
Collation:  binary (63)
Length:     17
Max_length: 20
Decimals:   0
Flags:      UNSIGNED BINARY NUM //unsigned !

+----------------------+
| round(c1, c3)        |
+----------------------+
| 18446744073709552000 | //yeah, positive
+----------------------+
1 row in set (0.00 sec)

How to repeat:
create table t1(c1 bit(64), c3 decimal(30, 5));
insert into t1 values(0xFFFFFFFFFFFFFFFF, 456);
select round(c1, c3)  from t1;

Suggested fix:
a positive guy rather than -1 returned
[12 Oct 2016 7:30] MySQL Verification Team
Hello Bang,

Thank you for the report and test case.
Observed this with 5.6.33/5.7.15.

Thanks,
Umesh
[17 Feb 2021 11:18] Erlend Dahl
This looks like it as been fixed in 8.0.23:

[5 Oct 2020 7:07] Tor Didriksen

8.0.23
select round(c1, c3)  from t1;
+----------------------+
| round(c1, c3)        |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)

select hex(round(c1, c3))  from t1;
+--------------------+
| hex(round(c1, c3)) |
+--------------------+
| FFFFFFFFFFFFFFFF   |
+--------------------+
1 row in set (0.00 sec)