MySQL Problem with large numbers I'm trying to put together a function to convert a size from one type to another (e.g. bytes >> gigabytes).

An extract is below

DROP FUNCTION fc_convSize;
DELIMITER $$
CREATE FUNCTION `fc_convSize`(inVal BIGINT) 
RETURNS DECIMAL(25,2)
NO SQL
BEGIN
    -- bytes 2 exabytes 
    SET @inVal := inVal/1024/1024/1024/1024/1024/1024;
    RETURN @outVal;
END$$
DELIMITER ;


But it doesn't work with large numbers. e.g.

mysql&gt; SET @input1 := (SELECT @@global.max_binlog_cache_size);
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SELECT fc_convSize(@input1);
ERROR 1264 (22003): Out of range value for column 'inVal' at row 1


But from what I can tell, max_binlog_cache_size is 184467440737095*47520* and the maximum for a BIGINT is 184467440737095*51615* so it should fit, right?   

But it gets stranger.

mysql&gt; SET @input1 := (SELECT @@global.max_binlog_cache_size);
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SELECT @input1/1024/1024/1024/1024/1024/1024;
+---------------------------------------+
| @input1/1024/1024/1024/1024/1024/1024 |
+---------------------------------------+
|           -0.000000000000003552713679 |
+---------------------------------------+
1 row in set (0.00 sec) 


mysql&gt; SET @input2 := (18446744073709547520);
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SELECT @input2/1024/1024/1024/1024/1024/1024;
+---------------------------------------+
| @input2/1024/1024/1024/1024/1024/1024 |
+---------------------------------------+
|           -0.000000000000003552713679 |
+---------------------------------------+
1 row in set (0.00 sec)


mysql&gt; SELECT 18446744073709547520/1024/1024/1024/1024/1024/1024;
+----------------------------------------------------+
| 18446744073709547520/1024/1024/1024/1024/1024/1024 |
+----------------------------------------------------+
|                        15.999999999999996447286321 |
+----------------------------------------------------+
1 row in set (0.00 sec)


What's going on???
But from what I can tell, max_binlog_cache_size is
  184467440737095*47520* and the maximum for a BIGINT is
  184467440737095*51615* so it should fit, right?


The maximum value of BIGINT SIGNED is 9223372036854775807.
The maximum value of BIGINT UNSIGNED is 18446744073709551615.
SIGNED is the default behavior. Hence the ERROR 1264 (22003): Out of range value error.

In the subsequent examples, you are expecting the @input1 variable to act like a BIGINT. But in MySQL, the datatype of a session variable depends on the value that is being assigned at runtime. For example:

rds@int[(none)]&gt; set @i := 9223372036854775807;
Query OK, 0 rows affected (0.00 sec)

rds@int[(none)]&gt; select @i + 0.0;
+-----------------------+
| @i + 0.0              |
+-----------------------+
| 9223372036854775807.0 |
+-----------------------+
1 row in set (0.00 sec)


In the above example, the variable @input1 is acting like a BIGINT.
But if we increase the value of @input1 by 1(**beyond the range of **BIGINT SIGNED****) the behavior changes.

rds@int[(none)]&gt; set @i := 9223372036854775808;
Query OK, 0 rows affected (0.01 sec)

rds@int[(none)]&gt; select @i + 0.0;
+------------------------+
| @i + 0.0               |
+------------------------+
| -9223372036854775808.0 |
+------------------------+
1 row in set (0.00 sec)


I think that in this case the session variable is acting like a FLOAT.

As suggested by Rick James, CAST the variable to the desired datatype to avoid such an unpredictable situation.