SQLite

View Ticket
Login
Ticket Hash: 36fae083b450e3af857a459e20d1fcd05cbff41c
Title: Inconsistent interpretation of truth
Status: Fixed Type: Code_Defect
Severity: Important Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2018-02-26 15:28:06
Version Found In:
User Comments:
drh added on 2018-02-26 15:14:41:

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.


drh added on 2018-02-26 15:28:06:

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