SQLite Forum

Bug when converting string to boolean?
Login
SQLite3 does not have a boolean type.  It has NULL, BLOB, TEXT, INTEGER, and REAL (INTEGER and REAL can be called NUMERIC, which is a Schrodinger's type -- you don't know if it is INTEGER or REAL until after you have looked in the box).

For the purpose of comparison (greater than, less than, equals, not equals) or assignment, then TRUE is the INTEGER 1 and FALSE is the INTEGER 0.

For the purpose of IS (a test of being), IS TRUE means "not null and not zero" and IS FALSE means "not null and one of 0, 0.0 or -0.0".

TRUE and FALSE are "magical words" unless there is a column in one of the tables in the query named TRUE or FALSE, in which case it is that column and not the magical value.

Something which evaluates to a non-zero and non-null numeric value IS TRUE, and something which evaluates to a zero value IS FALSE.  If you want to know if something IS TRUE or IS FALSE then it must be converted to numeric first -- you have no choice in the matter.  

However testing whether something equals 1 or 0 does not require any conversions whatsoever unless the affinity conversion rules apply.  And constants do not have affinity.  A text constant is *never* equal to an integer constant.