MySQL numeric operation oddities - overflow?

Question

What is wrong with this first query on MySQL?

The expected answer for both columns is '718042670591505846'

SELECT 5002622390 + (5102 * 140737488355328) new_iid_hard_way,
     CAST(5002622390 + (5102 * POWER(2,47)) AS UNSIGNED) new_iid_WRONG_way

It returns:

'718042670591505846', '718042670591505792'

Meanwhile, this query works:

SELECT 5002622390 + (5102 * 140737488355328) new_iid_hard_way,
   5002622390 + (5102 * CAST(POWER(2,47) AS UNSIGNED)) new_iid_CORRECT_way

It returns : '718042670591505846', '718042670591505846'

My hunch is that it is a datatype overflow / wrapping issue... but even with Strict mode enabled on MySQL 5.6.15, I don't get any errors.

Interestingly, sqlfiddle gets it wrong in all 4 cases:

returning 718042670591505800

Is this an overflow issue? If yes, why isn't MySQL throwing an error when strict mode is enabled?

From my experience, Mysql prefers to do the wrong thing and return no error when possible. I wonder if the same issue comes up on other DB's — Twelfth, Jul 02 '14 at 20:39
I think you get an implicit data type conversion by the expression (5102 * POWER(2,47) to an floating point type and lose precision in the addition before you do the cast. Have a look at `SELECT CAST(5102 * POWER(2, 47) AS UNSIGNED), 5102 * POWER(2, 47);` — VMai, Jul 02 '14 at 20:52
And there's no overflow because there's no column with a defined data type. — VMai, Jul 02 '14 at 20:53
See my answer here: http://stackoverflow.com/questions/24442089/difference-between-real-calculation-number-and-the-one-i-calculate-with-javascri/24442144#24442144 It's for Javascript, but I suspect a similar issue is involved here. — Barmar, Jul 02 '14 at 20:59
@Barmar It's interesting that different machines get different results. On sqlfiddle with MySQL 5.6.6 (as it said) the cast doesn't make any difference at all, at my Windows 8.1 64-bit computer with MySQL 5.6.16 (no great difference at all) I can reproduce the behavior in the question. — VMai, Jul 02 '14 at 21:03
I think sqlfiddle does its own post-processing of the output. E.g. if you have a `date` column, it displays it as a `datetime`. So it could be using floating point for everything before displaying it. — Barmar, Jul 02 '14 at 21:05

Rick James · Answer 1 · 2015-04-26T20:17:00.047

Some operations are done in DECIMAL, some are done in DOUBLE. This should explain why the numbers differ after about the 16th significant digit.

Here is another way to do it:

5002622390 + (5102 << 47)

<< is binary shift, so it has the same effect as *POW(2, ...)

Using DECIMAL gets the right answer of 718042670591505846:

SELECT CAST(5002622390 AS DECIMAL) + (5102 * 140737488355328);
SELECT CAST(5002622390 AS DECIMAL) + (5102 << 47)

1 Answers1