How to update an unsigned record safely against negative value?-CodePudding

I have a trigger like this which keeps the total value in the parent table:

UPDATE posts p SET total_comments = GREATEST(total_comments - 1, 0) WHERE old.post_id = p.id

I've used GREATEST() to avoid negative numbers, but still, I get this error if the current value of total_comments is 0 and then the trigger executes:

#1690 - BIGINT UNSIGNED value is out of range in (mydb.p.total_comments - 1)

Any solution?

CodePudding user response：

What about using where clause to avoid updating it if it's already 0. Try this:

UPDATE posts p
SET total_comments = total_comments - 1
WHERE old.post_id = p.id
  AND total_comments > 0

CodePudding user response：

The problem is that mysql is expecting the expression total_comments - 1 to return a BIGINT UNSIGNED, but if total_comments = 0 then there's gonna be an overflow.

One way to fix this would be to use a case when condition for when total_comments = 0:

UPDATE posts p 
SET total_comments = 
    case when total_comments = 0 then 0 else total_comments - 1 end
WHERE old.post_id = p.id

CodePudding user response：

From Out-of-Range and Overflow Handling:

Subtraction between integer values, where one is of type UNSIGNED, produces an unsigned result by default. If the result would otherwise have been negative, an error results

In your case total_comments is defined as UNSIGNED and if its value is 0 then the evaluation of the expression total_comments - 1 throws an error.

A workaround would be to compare total_comments to 1 inside GREATEST() and after that do the subtraction:

UPDATE posts p 
SET total_comments = GREATEST(total_comments, 1) - 1 
WHERE old.post_id = p.id