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:

http://sqlfiddle.com/#!9/d1bb6/1

returning 718042670591505800

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

Answer 1

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)