Be Careful While Using UNSIGNED Data Type in the Routine Body, Part 2

In one of our posts, in the Be Careful While Using UNSIGNED Data Type in the Routine Body one, we’ve explained how to get invalid data in routine body when using UNSIGNED data type and that in this case MySQL does not throw any exceptions.

One of the possible solutions of this problem is explicit setting of the NO_UNSIGNED_SUBTRACTION mode as it is shown in an example in the MySQL documentation.

Let’s modify a routine script:

And try to execute it in dbForge Studio for MySQL:

Overflow: in the result we will get a negative number

Overflow: in the result we will get a negative number

As you can see from the results, in this case MySQL handles assigning number incorrectly.

Let’s modify a routine script again. But in this case we will assign to the v_TINYINT variable a value that exceeds the maximum possible value allowed for this data type:

Here is the result of the routine execution:

Сutting: in the result we will get a maximal possible number for this type

Сutting: in the result we will get a maximal possible number for this type

As you can see, in this case the routine was also executed without any errors or warnings.
So that’s why try to use the UNSIGNED data type in routine bodies as seldom as possible.

What do you think about this? Do you use the UNSIGNED data type in your routines?

Leave a Comment