SQLite WHERE AlwaysFalseField=False returns zero results

Why does this query:

SELECT * 
FROM MyTable 
WHERE AlwaysFalseBooleanField = False 
ORDER BY Index DESC;

return zero rows, but this one here:

SELECT * 
FROM MyTable 
WHERE AlwaysFalseBooleanField <> True 
ORDER BY Index DESC;

works as it should?

AlwaysFalseBooleanField not is actually almost always False, but any way... SQLite has BOOL alias and table successfully created and filled with it,but contrary to documentation BOOL is TEXT (or it is cast to TEXT because True and False in data treated as TEXT not a macro as expected according to doc)!

In short

1. false, is the value 0, and

2. true is the value not 0 <<<<<<<< IMPORTANT.

   • Although true itself is defined as 1 most non-zero values are considered as true when doing a comparison (see https://www.sqlite.org/lang_expr.html#boolean_expressions for a more comprehensive explanation)

So (typically)

1. = false, is = 0, and

2. = true, is <> 0, and

3. <> true is = 0, furthermore

4. <> false is <> 0

   • see link above for a more comprehensive explanation

Perhaps consider the following (copy-able) SQL and the subsequent results:-

/* Cleanup just in case */
DROP TABLE IF EXISTS mytable;
/* Create the demo table*/
CREATE TABLE IF NOT EXISTS mytable (afbf);
/* Load some data are various types and amounts*/
INSERT INTO mytable VALUES
    /* INTEGERs */
    (1),(0),(99),(-99),
    /* REALs */
    (1.0),(0.0),(99.0),(-99),
    /* TEXT */
    ('1'),('0'),('A'),('a'),('ABCDEF'),('abcdef'),
    /* BLOB */
    (x'00'),(x'01'),(x'0102030405060708090a0b0c0d0e0f'),
    /* NULL */
    (null)
;
SELECT
    /* Show the type of the value (note typically irrespective of the column type) */
    typeof(afbf) AS the_storage_type, 
    /* Show the actual value */
    afbf, 
    /* bit of a waste but show the value of TRUE and then FALSE */
    true AS t,
    false AS f,
    /* Show various logicalcomparisons */
    afbf = true AS equals_true,
    /* Equivalent to TRUE (not 0) and FALSE (0)  */
    1 = afbf AS x1,
    0 = afbf AS x2,
    afbf = false AS equals_false,
    afbf <> true AS not_equals_true,
    afbf <> false AS not_equals_false, 
    not afbf AS not_,
    /* NULL comparisons */
    afbf IS NULL AS is_null,
    afbf IS NOT NULL as is_not_null
FROM mytable;
/* Cleanup*/
DROP TABLE IF EXISTS mytable;

When run :-

i.e. <> true IS NOT THE SAME AS = false as can be seen in the respective columns