Can someone explain why Mysql runs into BIGINT overflow when below sql is executed
select 100-0^222;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(100 - (0 ^ 222))'
But runs fine with
select 0^222;
Asked
Active
Viewed 74 times
1
Sravan
- 69
- 5
-
Runs perfect in my management studio. – Thanos Markou Dec 01 '15 at 13:56
-
Runs perfect too with result 186 on management studio! – Sebastian Brosch Dec 01 '15 at 13:58
-
I can confirm also that the first statement does not produce an error. I get result: 18446744073709551494. This is from MySQL Workbench. – Russ Dec 01 '15 at 13:58
-
talking about Mysql server? – Sravan Dec 01 '15 at 13:59
-
i am run it on mysql server and get error – Andrey Vorobyev Dec 01 '15 at 14:01
-
The version of MySQL being run might be important to provide, as that could explain why some get the error and some do not. – Russ Dec 01 '15 at 14:02
-
I am running it on with Mysql cli (server 5.5.20-log) on Windows 64 bit. – Sravan Dec 01 '15 at 14:02
-
my version is 5.5.40 debian, and i got error – Andrey Vorobyev Dec 01 '15 at 14:03
-
error goes from select 100 - 0 ^ 101; – Andrey Vorobyev Dec 01 '15 at 14:06
-
nice finding Andrey :) – Sravan Dec 01 '15 at 14:07
-
http://stackoverflow.com/questions/5743069/bigint-out-of-range-error-since-mysql-5-5?rq=1 – Andrey Vorobyev Dec 01 '15 at 14:22
-
The different behavior is because automatic casting from unsigned to signed is [optional](http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_unsigned_subtraction). – Vatev Dec 01 '15 at 14:23
-
See [12.12 Bit Functions :: Bitwise XOR](http://dev.mysql.com/doc/refman/5.7/en/bit-functions.html#operator_bitwise-xor): `The result is an unsigned 64-bit integer.`. Try: `SELECT 100-CAST(0^222 AS SIGNED);` – wchiquito Dec 01 '15 at 14:24
-
`unsigned` = default, non negative numbers. That's why. Cast it to `signed`. Error tells you exactly what's wrong.. – Mjh Dec 01 '15 at 14:26