How using boolean values in SQL can lead to unintended results

We all trip over the small details, sometimes…

First, let’s start with a quick quiz. Given the following SQL and pseudocode, how many rows do you expect will be affected?

/* Using MySQL 5.6.23 */
CREATE TABLE t (
    x VARCHAR(32),
    y INT
);
INSERT INTO t (x, y)
VALUES ('a', 1), ('b', 2), ('c', 3);
$value = 'b';
$statement = $db->prepare('UPDATE t SET y = 123 WHERE x = :value')
$statement->bind(':value', $value)
$statement->execute()

If you answered 1, then congratulations: you’re right. Easy, right?

Someone call Staples, ‘cuz that was easy.

So, what’s the issue with using boolean values?

Using the same SQL, but with this slight change in the pseudocode, how many rows do you expect will be affected?

$value = false;
$statement = $db->prepare('UPDATE t SET y = 123 WHERE x = :value')
$statement->bind(':value', $value)
$statement->execute()

If you answered 0, then you, like me, are wrong.

“But, no row in the table has a value for x that equals false, so how can this be,” you ask?

Tell me about it, Debbie. I had no idea! (The song is actually completely unrelated.)

Here is where you, and I, learn how wrong we are.

The answer is: type coercion (aka “implicit type conversion”).

More specifically, it has to do with MySQL’s BOOLEAN type, and how it’s implemented:

BOOL, BOOLEAN

These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true:

wat

Are you thoroughly confused now? Good, you should be. That was a mouthful of gibberish.

While the documentation is perfectly clear and succinctly explains the situation, it might be non-obvious as to why we observed what we saw.

(Oh, and don’t get me started about how “gibberish” is spelled wrong based on how it’s commonly pronounced: it should be spelled “jibberish.” But, anyway…)

Under the hood, MySQL implements BOOLEAN types as TINYINT(1) values, and more specifically, where TRUE is 1 and FALSE is 0. Okay, fine, but still, no value of x in our table was equal to 0 … or, were they? 😈

This is where type coercion comes into play: when comparing a string, in this case, our x which is defined as VARCHAR(32), with a BOOLEAN value, in other words, a TINYINT(1) value of 0, exactly what happens? Let’s see:

mysql> SELECT CAST('a' AS UNSIGNED) AS a,
              CAST('b' AS UNSIGNED) AS b,
              CAST('c' AS UNSIGNED) AS c;
+---+---+---+
| a | b | c |
+---+---+---+
| 0 | 0 | 0 |
+---+---+---+
1 row in set, 3 warnings (0.04 sec)
mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'a' |
| Warning | 1292 | Truncated incorrect INTEGER value: 'b' |
| Warning | 1292 | Truncated incorrect INTEGER value: 'c' |
+---------+------+----------------------------------------+
3 rows in set (0.04 sec)

Oh, damn. MySQL, in its effort to be helpful and “user friendly,” will quietly, but not silently, as evidenced by the warnings, transform our string values into integer 0 values. So, how many rows would be affected? All 3 of them!

mysql> UPDATE t SET y = 123 WHERE x = FALSE;
Query OK, 3 rows affected, 3 warnings (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 3

You said it, Kendrick. DAMN.

Interestingly enough, if we had made a similar “mistake” and used NULL instead of FALSE, the outcome would have been more in line with our expectations:

mysql> INSERT INTO t (x, y) VALUES (NULL, 4);
Query OK, 1 row affected (0.00 sec)
mysql> UPDATE t SET y = 123 WHERE x = NULL;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

This is because, in SQL, nothing “equals” NULL. Values can be NULL, but NULL is never equal to anything, even NULL. To compare values to NULL, SQL provides the IS NULL and IS NOT NULL operators for this very purpose:

mysql> UPDATE t SET y = 123 WHERE x IS NULL;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> UPDATE t SET y = 123 WHERE x IS NOT NULL;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3  Changed: 0  Warnings: 0

Conclusion

Even in strongly typed languages like SQL, conveniences like type coercion can create dangerous pitfalls. Imagine if that was a DELETE statement instead of an UPDATE statement — poof, there go all your rows where the key’s resulting type conversion results in a 0 value.

Have you been hurt by accidentally using a boolean value in a MySQL query and affected more rows than you intended? Share your stories in the comments below. It’s okay, you’re not alone.

Did you learn something from this post? Share it with your friends and colleagues who might benefit from it, too!