SQLite, 'SELECT 0 OR NULL' vs 'SELECT 1 OR NULL', strange result

I've come across a very strange in behavior in SQLite with boolean expressions, or whatever is actually going on.

SELECT 1 OR NULL
-- Result: 1

SELECT 0 OR NULL
-- Result: NULL

It even interprets strings and performs the same strange evaluation:

SELECT '789abc' OR NULL
-- Result: 1

SELECT '000abc' OR NULL
-- Result: NULL

The order of the expression does not matter and if you add more similar expressions and operators it still evaluates the same. Can someone please explain the logic behind this strange asymmetry? What's going on behind the scenes?

Solution

SQLite uses type coercion, interpreting non-zero numbers as true, and zero or empty strings as false. But NULL does not mean false. It means unknown value, hence:

1 OR NULL returns 1 because 1 is true, and true OR anything is always true, regardless of 2nd value (in fact boolean optimization usually does not even bother the 2nd argument in such case, so unknown or not it simply does not matter at all).
0 OR NULL returns NULL because 0 is false, and false OR NULL means false OR unknown so the result is unknown. In this case unknown matters as it would affect the result if known.

The next case look bit more convoluted but are resolved using the same rules as above. The '789abc' OR NULL returns 1 because the first argument will be converted to numeric value first. The tricky part is that it will not be interpreted as hex string but instead only starting digits will remain (789) and anything starting from next non-digit character to the end of the string will be dropped:

> SELECT '100abc23' + 0;
100
> SELECT '00abc23' + 0;
0

So 789abc is 789 which is non-zero when converted and that means value is treated as true, so again true OR anything is true.

The last one is similar: The 000abc string again will be converted and again SQLite will drop anything starting at first non-digit character to the end of the string. So your000abc becomes 000 and that converts to 0 which is considered false. Therefore false OR NULL is false OR unknown so the result is unknown.

If these numeric strings are in fact hex and you want SQLite to treat them as such, you need to use hex() function:

> SELECT hex('000abc') OR NULL;
1