2

I just found out that if I try to subtract an unsigned integer from another unsigned, MySQL automatically assumes the result will be unsigned, which in turn generates the error BIGINT UNSIGNED value is out of range whenever the actual result of the operation is negative.

I tried googling the reason for this design decision, but didn't find any explanation.

What are the motives for such a decision? does it make sense to assume an unsigned result when subtracting two unsigned integers?

Improve this question

1 Answer 1

Reset to default
1

Catch-22.
I'll use TINYINT to make the numbers easier. SIGNED range is -128..127; UNSIGNED is 0..255

Your question, as I see it, is "Why not make the result SIGNED?"

Your case is (10) - (12) = (-2), which cannot be represented as UNSIGNED.

A different case is (200) - (1) = (199), which cannot be represented as SIGNED.

Either decision leads to some arguments producing an value that is "out of range".

(I don't know the history of the decision. Also, I don't know if there is an SQL standard that is involved.)

Improve this answer
2
  • I see, I hadn't thought about cases where unsigned values where high enough for SIGNED to not be able to represent them. Thanks, now it makes sense!
    – Sebastianb
    Commented Sep 29, 2017 at 16:35
  • Logically, if you want something unsigned, presumably you've done it for a reason - quantity_on_hand can never be negative, so if you try and fulfill an order in a warehouse that has more items in it than are available, it makes sense to throw an error!
    – Vérace
    Commented Sep 29, 2017 at 16:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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