SQLite Forum

When is "IS" used in SQLite?
The tests `IS TRUE` and `IS FALSE` test the ZERO flag.

If the ZERO flag is set in the CPU register when the value is loaded, then the value `IS FALSE`.  If the ZERO flag is not set when the value is loaded into the accumulator, then the value `IS TRUE`.

In other words, any value that is 0 **IS FALSE** and any non-zero value **IS TRUE**.

This has been the state of affairs for about a decade shy of a century.

How people do not know this I cannot fathom.

NULL is neither True nor False (it is NULL).  As a conditional, a NULL value evaluates as False.

This issue can probably be attributed to a lack of education and understanding of how digital computers work.

Also note that `cast(something as boolean)` does nothing because boolean is not an understood type (it means NUMERIC).  Therefore a NULL or INTEGER value is unchanged, so `cast(5 as boolean)` is nothing more than a CPU and RAM intensive way to say `5`.  

However, in platforms that *DO* have a boolean type (SQLite3 not being one of them), then a cast to boolean becomes either 0 or 1 depending on whether the ZERO flag is set or not.

Claiming that `cast(x as boolean)` when x is already either integer or float does anything at all other than waist CPU and MEMORY resources is demonstrating their lack of understanding.