ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in

Question

MySQL version: 5.5.38-0ubuntu0.14.04.1

Table structure:

CREATE TABLE `route_points2` (
 `dist` tinyint(4) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Data: 1 row with dist equal to 200

Sql:

SELECT * FROM route_points2 WHERE -1*dist < 1;

Error:

ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(<cache>(-(1)) * `db_name`.`route_points2`.`dist`)'

Why mysql casts -1*dist to BIGINT UNSIGNED?

The error above can be solved by casting dist to SIGNED:

SELECT * FROM route_points2 WHERE -1*CAST(dist AS SIGNED) < 1;

And this works fine.

But I do not understand why mysql chose BIGINT UNSIGNED for -1*dist

You can avoid the `CAST` with something like: ```SELECT * FROM `route_points2` WHERE -`dist` < 1;``` — wchiquito, Sep 11 '14 at 14:12
It's simplified example. In real world it's not only sign invertion. — Index, Sep 11 '14 at 18:44
like https://stackoverflow.com/questions/5605085/bigint-unsigned-value-is-out-of-range — qdinar, Jul 13 '17 at 09:35

Mikpa · Answer 1 · 2014-09-12T06:33:57.703

Since you are using an UNSIGNED number, and a comparision with a SIGNED number (-1), mysql needs to cast your UNSIGNED to a SIGNED. I guess it chooses BIGINT to be able to cope with all possible INTs.

The behavior changed with MySQL 5.5, look at this http://dev.mysql.com/doc/refman/5.5/en/out-of-range-and-overflow.html

To make this work, define your table as:

CREATE TABLE `route_points2` (
 `dist` tinyint NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

or if you need values > 127 use:

CREATE TABLE `route_points2` (
 `dist` smallint NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

score 0 · Answer 2 · answered Mar 19 '15 at 10:08

MySQL does not (anymore) by default produce signed results from subtraction of unsigned operands, and neither from mixed-signedness multiplication.

You have to enable the MySQL mode MODE_NO_UNSIGNED_SUBTRACTION to restore the old (quite reasonable) default behaviour for subtraction. There doesn't seem to be a parallel promotion mode for multiplication.

The solution, as you've already posted, is to use CAST operators on both operands of a subtraction, and on any unsigned operands in a potentially mixed multiplication.

Aside: it doesn't matter what width the types are. On my first encounter I thought it would only affect BIGINT operands. As there's no wider signed type to go to, and large values could not be represented correctly, I would understand an error condition in that case, but as you're seeing, this error occurs all the way down to the smallest integer types (e.g. values pulled from TINYINT UNSIGNED columns).

ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in

2 Answers2