SQLite Forum

Bug when converting string to boolean?
> SQLite3 does not have a boolean type. It has NULL, BLOB, TEXT, INTEGER, and REAL

Hmm, is NULL a type?  It's a value in a ternary logic system.  It's a marker for missing information.  But it's not type.  

It's definitely not a value, despite the common use of "NULL value".  It's a nonvalue, the absence of a value.  It's a property of a place where a value would otherwise be.  That's why columns are declared with a type *and* NULL or NOT NULL: two distinct attributes of the attribute.  

You can't cast NULL to or from anything.  You can try, and the syntax permits it.  But nullity trumps all: 

sqlite> select typeof(cast(NULL as int)) as hello;

SQLite's output here is shorthand, the product of inexpressibility.  The first parameter to CAST is a value; because values may be missing, CAST accepts NULL in place of a value. (You cannot, for example, CAST(REAL as TEXT) because "REAL" is a type, and CAST wants a value.)  The output of TYPEOF is a type, or would be, if type there was.  But there is not; the missing value has no type.  

What TYPEOF is really saying is that the output of CAST in this case, when combined with something else, will be treated as NULL.  Forced by the syntax to provide a one-word answer, it couldn't (more honestly) say *missing integer*.  But that's what it means.