SQLite

SQLite does not have a separate Boolean type. Instead, it interprets integer, floating-point, text, and blob values as true or false. But the rules for determining truth are not consistent.

The ambiguity arises with floating-point values. Some operations consider floating point values to be true if they are non-zero. Others consider floating point values to be true if their integer part os non-zero. Example:

CREATE TABLE t1(x ANY);
INSERT INTO t1 VALUES(0.5);
SELECT x OR (8==9), NOT NOT x, CASE WHEN x THEN 1 ELSE 0 END FROM t1 WHERE x;

The WHERE clause and the CASE statement both consider the value 0.5 to be true, but the OR and NOT operators consider 0.5 to be false.

This confusion dates back to before version 3.6.1 (2008-08-06). The problem was seen during internal source code analysis and has never caused problems in the wild, as far as we are aware.

The fix is to interpret floating-point values as true if they are non-zero, even if their integer part is zero.