MySQL Bugs: #100732: BIGINT UNSIGNED arithmetic operations return wrong value

Bug #100732	BIGINT UNSIGNED arithmetic operations return wrong value
Submitted:	3 Sep 2020 16:16	Modified:	4 Sep 2020 5:37
Reporter:	Pavel Levin	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.7.24	OS:	SUSE
Assigned to:		CPU Architecture:	x86
Tags:	BIGINT

Description:
CREATE TEMPORARY TABLE tmptable (num BIGINT UNSIGNED);
INSERT INTO tmptable SELECT POW(62, 10)*21;
SELECT * FROM tmptable;

The above returns 16785987317366804480 - that's incorrect.

The actual result of (62^10)*21 is 17625286683235144704.

Value returned is less than actual by 1024 (not sure why).

I checked and it’s well below the max value for BIGINT UNSIGNED which is 2^64-1 or 18446744073709551615.

Further, storing the correct value directly does work as well:

SET @bla = 17625286683235144704;
SELECT @bla;
> 17625286683235144704

How to repeat:
See description.

Suggested fix:
Make the BIGINT arithmetic work properly with large numbers.

Hello Pavel Levin,

Thank you for the report and test case.
I'm not seeing any issues at my end in the latest GA 5.7.31 and even in the reported version.

-
 bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.24 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> CREATE TEMPORARY TABLE tmptable (num BIGINT UNSIGNED);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO tmptable SELECT POW(62, 10)*21;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tmptable;
+----------------------+
| num                  |
+----------------------+
| 17625286683235143680 |
+----------------------+
1 row in set (0.00 sec)

-
 bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.31 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> CREATE TEMPORARY TABLE tmptable (num BIGINT UNSIGNED);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO tmptable SELECT POW(62, 10)*21;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tmptable;
+----------------------+
| num                  |
+----------------------+
| 17625286683235143680 |
+----------------------+
1 row in set (0.00 sec)

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.  

Thank you for your interest in MySQL.

regards,
Umesh