Ran into a strange issue with a MySQL query.
Here is a table I got:
CREATE TABLE `vote_counts` (
`list_id` int(10) unsigned NOT NULL,
`node_id` int(10) unsigned NOT NULL,
`num_up_votes` int(10) unsigned DEFAULT '0',
`num_down_votes` int(10) unsigned DEFAULT '0',
PRIMARY KEY (`list_id`,`node_id`),
The main point to notice is that there are two UNSIGNED columns:
I tried to run this query:
SELECT vc.num_up_votes - vc.num_down_votes AS votes_diff
FROM vote_counts vc
WHERE vc.list_id = 298553 AND vc.node_id = 1643746;
and MySQL gave me this error:
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '('0' - '18')'
Could not understand why MySQL threw error for this. Of course, the result is negative, but I am not trying to store anything into an UNSIGNED column.
A google search led me to the MySQL manual’s out of range and overflow page.
where it states
Subtraction between integer values, where one is of type UNSIGNED, produces an unsigned result by default.
This is a really strange behavior!
For now, there are two solutions:
Use CAST with SIGNED as follows in the SQL itself:
PgSQL12345SELECT CAST(vc.num_up_votes AS SIGNED)- CAST(vc.num_down_votes AS SIGNED)AS votes_diffFROM vote_counts vcWHERE vc.list_id = 298553 AND vc.node_id = 1643746;
If you are using an interactive client, you can set
sql_modelike this:PgSQL1SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
which then allows subtraction between unsigned column values when the difference is negative.