1

I tried the following to decrease a counter after a post was deleted by using GREATEST() so it does not result a negative value:

SELECT GREATEST(0, posts - 1)
FROM users
WHERE id = 123

but it returns:

[Err] 1690 - BIGINT UNSIGNED value is out of range in '(`db1`.`users`.`posts` - 1)'

posts returns 0:

SELECT posts
FROM users
WHERE id = 123

An the following returns 0 as expected:

SELECT GREATEST(0, 0 - 1)

So what I'm doing wrong?

2

If your posts column is BIGINT UNSIGNED then unsigned values have to be 0 or more, so -1 is out of range.

MariaDB [test]> desc tbl1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | bigint(20) unsigned | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
1 row in set (0.01 sec)

MariaDB [test]> select id -1 from tbl1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`tbl1`.`id` - 1)'
  • I know -1 is out of range. That is the reason why I used GREATEST(0, -1) resulting 0 but I see in your example that id - 1 returns an error as well?! Isn't it possible to use the value of id for calculation only without letting MySQL checking the data type? – mgutt Feb 1 '17 at 11:47
  • 5
    Ok, found a solution by using SELECT GREATEST(0, CAST(posts AS SIGNED) - 1) – mgutt Feb 1 '17 at 11:55
  • 2
    @mgutt correct! You could also use CASE WHEN points > 0 THEN points - 1 ELSE 0 END instead of GREATEST(...). It also voids the error in the (unlikely) event that points is in the very high range and cannot be converted to SIGNED. – ypercubeᵀᴹ Feb 1 '17 at 12:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.