IS NULL
,IS NOT NULL
, truthiness among other assertions on MySQL and PostgreSQL
People like to complain about JavaScript, how can one thing be equal to the other, i.e. null == undefined
evaluates to true
unless you use the triple equals ===
. Other dynamic typed languages have its peculiarities, such as Ruby where 0 == true
evaluates to true
, the reason for Ruby is that considers 0
as a value and any value evaluates to true
.
How about SQL? The answer is… it depends.
Which database are you using?
Some may be more forgiving, like MySQL doing casts for you all over the place, or more strict like PostgreSQL where you can only compare the truthiness of something of the same type.
Assertion | MySQL | PostgreSQL |
---|---|---|
('A' = TRUE) IS TRUE |
0 | invalid input syntax for type boolean: “A” |
('A' IS TRUE) IS TRUE |
0 | invalid input syntax for type boolean: “A” |
(1 = TRUE) IS TRUE |
1 | operator does not exist: integer = boolean |
(1 IS TRUE) IS TRUE |
1 | argument of IS TRUE must be type boolean, not type integer |
('1' IS TRUE) IS TRUE |
1 | 1 |
('0' IS FALSE) IS TRUE |
1 | 1 |
(1 = '1') IS TRUE |
1 | 1 |
(0 = '0') IS TRUE |
1 | 1 |
Beyond the implications of wrong type comparison, as you can see on lines 1 through 4 where MySQL evaluates values as true
or false
, you should also worry about what the fact of a column being NULL
may imply when comparing two columns.
Suddenly knowing if it is true
or false
doesn’t matter because you cornered yourself with a third possible value: NULL
. By definition NULL
is not a value neither a state, it should be considered “garbage”, and no column where you know its value and type would be garbage.
In this front both databases operate the same way:
Assertion | SQL |
---|---|
(NULL = NULL) IS TRUE |
0 |
(NULL = NULL) IS FALSE |
0 |
(NULL IS NULL) IS TRUE |
1 |
(0 = NULL) IS NULL |
1 |
(1 = NULL) IS NULL |
1 |
('A' = NULL) IS NULL |
1 |
(TRUE = NULL) IS NULL |
1 |
(FALSE = NULL) IS NULL |
1 |
(0 IS NULL) IS TRUE |
0 |
(0 IS NOT NULL) IS TRUE |
0 |
(0 = NULL) IS TRUE |
0 |
(0 = NULL) IS FALSE |
0 |
(0 = NULL) IS NOT TRUE |
1 |
(0 = NULL) IS NOT FALSE |
1 |
('NULL' = NULL) IS TRUE |
0 |
('NULL' IS NULL) IS TRUE |
0 |
The highlighted parts are usually assumptions that people expect to behave differently, for example that zero is not equal to NULL
. That catches many people by surprise. It shouldn’t catch you by surprise though, because 0 is a value. The default behavior of MySQL of casting NULL
to 0
, on a INTEGER NOT NULL
column without a DEFAULT
value taught a whole generation of developers that this assumption is true. The same applies when casting a string-based column that has no default value on a NOT NULL
column to empty string.
Is that a spaceship operator?
While writing this post and taking a peek into MySQL documentation, I never noticed that a NULL
-safe equal operator, <=>
, existed, unfortunately the MySQL website only shows the documentation from 5.5 to 8.0, so I can’t be certain if this existed in prior releases.
SELECT 1 IS NOT NULL, 1 = NULL, 1 <=> NULL;
Assertion | MySQL |
---|---|
1 IS NOT NULL |
1 |
1 = NULL |
NULL |
1 <=> NULL |
0 |
The case where MySQL thinks it is PHP
Another behavior I discovered when doing some JOIN
s were the string and integer comparison. Both databases get SELECT 1 = '1';
as true
, however, MySQL takes a step further:
SELECT 1 = '1a', '1' * 3, '1abc' + 4;
Assertion | MySQL | PostgreSQL |
---|---|---|
1 = '1a' |
1 | invalid input syntax for integer: “1a” |
'1' * 3 |
3 | 3 |
'1abc' + 4 |
5 | invalid input syntax for integer: “1abc” |
Thus behaving like PHP. PHP is expected to change this behavior soon with this RFC. PostgreSQL, in this case, is forgiving in casting a string as an integer, only if there is an integer inside of the quotes. If you mix the integer with other characters, it throws an error as you can see above.
I Know!
No, I don’t. I thought I knew enough SQL. Apparently, I was wrong.
Independent of the language you are using, you should be aware that assertions that are true in your language may not be true in SQL and the other way as well.
The <=> operator has been around since 3.23.0. See https://downloads.mysql.com/docs/refman-4.1-en.a4.pdf page 731 (yes, I know it’s not all that easy to find the old manuals).
Cheers,
Jesper
LikeLike
Great post!
It is a pity that MySQL doesn’t show old versions documentation online. But very old manuals are still around for historic searches:
http://www.csb.yale.edu/userguides/databases/mysql/Docs/manual_toc.html
You may be interested in knowing that PostgreSQL also has a “spaceship operator”: IS [NOT] DISTINCT FROM. And SQLite has col1 IS [NOT] col2.
LikeLike
Nice Post, and I have learn something new from this post, thanks for sharing this informative content.
LikeLiked by 1 person