Question 20350 in Mysql Question posted in
The official MySQL documentation can be found here.

DOUBLE value is out of range – Mysql

I am getting the error "DOUBLE value is out of range," but the suggested answers don’t seem to work.

The equation is:

select 144254*8.524789415648847e307;

I am going to guess that this is just a limitation of MySQL. The answer should be:

12297349723650088420210958255312752983226655887341313892168288956573735751743741397634413196477674817934705029300600187860424954067080731983114901902495162280851999670265668298743275405871711207842259780845007110767808685197279959365690523810797428906456118395209507549026477413087458372311414663408660706125414400

Basically, I am creating a clicker game entirely in MySQL. I know that this is a terrible idea, I just ran with it to learn as much about MySQL as I can. It has taught me a lot about its limitations. Just one example, I had to change each column’s datatype to LONGTEXT in order to handle the length of some variables.

You can check out my project here: https://github.com/GarinJTanner/CLICK

Conveniently enough, the game breaks right after you hit Centillionaire, the last title given in the game.

Tags:

2

Answers


  1. 0

    The issue here, is that you are trying to store the result in a double.

    But the problem is that a double have a maximum value of 1.7976931348623157E+308

    Source :
    https://learn.microsoft.com/en-us/dotnet/api/system.double.maxvalue?view=net-7.0

    Considering that you are actually multiplying by more than 10 you are reaching a higher value than the maximum allowed.

    Thus your error problem.

    It is not espicially related to mySQL it is related to the variable format.

    I would suggest you to have a look at other types of variable if you want to store such a huge value.
    But it might not be easy.

    Double is the variable type meant for big numbers so finding another might be hard

    Hope it helped you,

    Have a goood day !

    Login or Signup to reply.
  2. 0

    but I never specified the variable to be double.

    The value 8.524789415648847e307 is a DOUBLE value. No other data type in MySQL can represent that numeric value (you could store a string with those characters, but that isn’t a numeric value, and you can’t do math on it).

    Since you have a DOUBLE in your expression, the result of the expression is also DOUBLE. This is due to MySQL’s implicit type conversion that happens in expressions.

    https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html says:

    If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.

    But the value resulting from your expression is too large to be represented by an IEEE DOUBLE or any other data type in MySQL.

    https://dev.mysql.com/doc/refman/8.0/en/numeric-type-syntax.html says:

    DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

    A normal-size (double-precision) floating-point number. Permissible
    values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0,
    and 2.2250738585072014E-308 to 1.7976931348623157E+308. These are the
    theoretical limits, based on the IEEE standard. The actual range might
    be slightly smaller depending on your hardware or operating system.

    MySQL does not implement a solution for unlimited precision math.

    It won’t help to use CAST() to convert these values to a string, because MySQL can’t do math on a string. It would have to evaluate your numeric expression before casting to string, and it can’t do that because of the overflow.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search