MySQL Returns Wrong Value Only When Number Is Larger

Question

I am writing a bot that has stored values. One of which is a number of 18 places. I am able to store the values properly into my database, MySQL, as a BIGINT[20]. The values being stored are correct.

The problem comes when trying to fetch that number. The value stored inside of the database is 502033097630416897, however, the actual value returned by mysql is this 502033097630416900. For comparison, here they are again:

502033097630416900
502033097630416897

It seems as if the return value is either being rounded up or changed. I wanted to test this theory, so I changed the value stored in MySQL to a smaller number, 123, 1234567, and so on. The values being returned are actually correct. For some reason, the values returned are incorrect if the numbers stored in MySQL are too large. I would post my code for storing, but I don't think that is relevant since the actual value being stored is correct.

As for calling, I have a function that can get multiple return new

Promise((success, fail) => {
    con.query(`SELECT * FROM serversettings WHERE serverId = ${serverID}`, (err, rows) => {
        switch(type){
            case "onjoin":
                success(rows[0].onjoin);

Where type is the data I want to pull. Any insight would be helpful, thank you!

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Nov 06 '18 at 23:14
Are you sure that's the value returned by mysql? or is that just what node.js is telling you the number is. (hint, try `console.log(502033097630416897)`) — Kevin B, Nov 06 '18 at 23:17
I directly logged the value returned of rows[0].onjoin (there is only one entry) so it should be the exact values returned. @KevinB — Vince, Nov 06 '18 at 23:19
@Vince Right, but try logging that yourself, using the console.log i showed as an example. Javascript does have limits, and the numbers you are using are past said limits. (`console.log(Number.MAX_SAFE_INTEGER)`) — Kevin B, Nov 06 '18 at 23:20
To test mysql's result, run this query in some other context, not javascript. The value you have should be safe as a BIGINT, so mysql should be returning the correct value. It's javascript that can't handle it. — Kevin B, Nov 06 '18 at 23:22

score 0 · Accepted Answer · answered Nov 06 '18 at 23:59

Javascript does arithmetic using 64-bit IEEE 754 floating point numbers. In that kind of arithmetic, the two numbers in your example are equal to each other; IEEE 754 only offers about 16 decimal digits of precision.

To handle these numbers in Javascript, you must either work with them as if they were text strings or use a package for long numbers.

If you change your query to

SELECT CAST(bigint_column AS CHAR) bigint_column, other, else, etc
  FROM serversettings 
  ...

You'll get the character strings.

MySQL Returns Wrong Value Only When Number Is Larger

1 Answers1