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 JOINs 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.

• Article