SQLite Forum

When is "IS" used in SQLite?
For check constraints there is an additional implicit CAST to NUMERIC involved.
The outcome of this CAST expression decides whether a constraint is violated. In this case the value of the expression itself, which is NULL, is not directly used to determine result. The CAST value is interpreted in a specific way.

See https://www.sqlite.org/lang_createtable.html section 3.7:

"Each time a new row is inserted into the table or an existing row is updated, the expression associated with each CHECK constraint is evaluated and cast to a NUMERIC value in the same way as a CAST expression. If the result is zero (integer value 0 or real value 0.0), then a constraint violation has occurred. If the CHECK expression evaluates to NULL, or any other non-zero value, it is not a constraint violation."