The hidden risks of SQL MODE

MySQL 5.0 introduces improved SQL modes, which can fine tune the way your server behaves. If you are a long term MySQL user, you may be familiar with the speed for accuracy trade-off. MySQL has a default for each field, and guesses a value when you don't provide an appropriate one when inserting or updating. If this behavior is not acceptable to you, you can now tell the server to be less permissive. Check out an article by Robin Schumacher, where this concept is explained thoroughly.

If you look at the manual, though, you will see that the SQL modes are quite a few, and you may be tempted to combine some of them to control every tiny part of the server input.
Beware, though. There are some pitfalls that you should be aware of. Let's walk through an example.

SET SQL_MODE='';
select @@sql_mode, cast(1 as unsigned) - cast(2 as unsigned);
+------------+-------------------------------------------+
| @@sql_mode | cast(1 as unsigned) - cast(2 as unsigned) |
+------------+-------------------------------------------+
|            |                      18446744073709551615 |
+------------+-------------------------------------------+

What is this? It is a subtraction between two unsigned values. There is an overflow in the result, and then the result of 1-2 becomes the highest BIGINT value minus one.
We can control this behavior, and use a specific SQL_MODE, NO_UNSIGNED_SUBTRACTION, to tell the server that it should not allow a subtraction between unsigned values, and treat them as signed instead.

SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';
select @@sql_mode, cast(1 as unsigned) - cast(2 as unsigned);
+-------------------------+-------------------------------------------+
| @@sql_mode              | cast(1 as unsigned) - cast(2 as unsigned) |
+-------------------------+-------------------------------------------+
| NO_UNSIGNED_SUBTRACTION |                                        -1 |
+-------------------------+-------------------------------------------+

Fine. Now we know that we can take control of subtractions. But there is something more to know. The SQL MODE sticks to each procedure, function, or trigger, meaning that each routine is executed using the SQL mode that was active at creation time. This could lead to surprising results.

set sql_mode='';
drop function if exists subtraction;
create function subtraction(x int unsigned, y int unsigned)
returns int
deterministic
return x - y;

SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';

select @@sql_mode, subtraction(1,2), cast(1 as unsigned) - cast(2 as unsigned);
+-------------------------+------------------+-------------------------------------------+
| @@sql_mode              | subtraction(1,2) | cast(1 as unsigned) - cast(2 as unsigned) |
+-------------------------+------------------+-------------------------------------------+
| NO_UNSIGNED_SUBTRACTION |       2147483647 |                                        -1 |
+-------------------------+------------------+-------------------------------------------+

Look here. We set the SQL_MODE to NO_UNSIGNED_SUBTRACTION, because we want to avoid that unpleasant effect, but the subtraction function was created with a different SQL_MODE.
Therefore, the operations inside such function will be affected by the stored SQL_MODE, regardless of the one that is active at the moment.

Whenever your result depends on a specific SQL_MODE, always check which mode is associated with the stored routines or triggers that you are using.

SELECT
  routine_name,. sql_mode
FROM
  information_schema.routines
WHERE routine_schema='test'
  AND routine_name='subtraction'
  AND routine_type='function';
+--------------+----------+
| ROUTINE_NAME | SQL_MODE |
+--------------+----------+
| subtraction  |          |
+--------------+----------+

There is also a more complex example involving triggers.