there is probably some trivial explanation for the following that escapes me… I would be grateful for any insight. Why, in my db (as shown below), I get a row where an empty checkinTime is greater than the provided integer?

➜  data sqlite3 --version
3.36.0 2021-06-18 18:58:49 d24547a13b6b119c43ca2ede05fecaa707068f18c7430d47fc95fb5a2232aapl
➜  data sqlite3 treatments.sqlite
sqlite> .schema
CREATE TABLE treatments (
    id INTEGER PRIMARY KEY,
   -- snipped a lot of irrelevant column defs --
    checkinTime INTEGER
);
sqlite> SELECT Count(*) FROM treatments;
649256
sqlite> SELECT Count(*) FROM treatments WHERE checkinTime >= 1645311600000;
Count(*)
--------
1
sqlite> SELECT id, checkinTime FROM treatments WHERE checkinTIME >= 1645311600000;
id      checkinTime
------  -----------
636351

Thank you for that.

First, the checkinTime for the selected row is not NULL, it is empty. But let’s assume that an empty column is treated as the NULL storage class. According to the notes you directed me to, the selected value should be considered less than any other value and hence, should be less than 1645311600000. By my logic, this row should not be selected for WHERE checkinTime >= 1645311600000.

What am I not grasping here?

The checkinTime is not an integer, nor is it null -- it is an empty text field.

select id, checkinTime, typeof(checkinTime) FROM treatments WHERE checkinTIME >= 1645311600000;

If it is an empty string, then note:

sqlite> select '' >= 1645311600000 ;
1

(and returns the same whatever number is used). My guess is you're inserting strings, but '123' gets coerced to the number 123 (because of the column's affinity, and therefore compares correctly), but '' remains as a string (which sort "above" numbers).

You could use 0 for unknown/missing times, although if you had something like "where checkinTime < 1645311600000", you'd get such rows turning up when you probably didn't want them to.

Better would be to insert NULL, since:

sqlite> select null < 1645311600000 ;

sqlite> select null > 1645311600000 ;

(that is, such tests return NULL, and the corresponding rows wouldn't be selected).

Thank you for this clear explanation as it definitely seems to explain my situation. I will check my code tomorrow to see if am indeed (inadvertently) inserting strings, and if yes, it will be (should be) trivial for me to cast them as numbers before doing the insert

(inserting 0 would not work for the reason you stated)

Text is always greater than an integer. There are no exceptions, unless the text happens to be the textual representation of a number (and numeric affinity has been applied).

sqlite> select ''>1000;
┌─────────┐
│ ''>1000 │
├─────────┤
│ 1       │
└─────────┘
sqlite> select ''<1000;
┌─────────┐
│ ''<1000 │
├─────────┤
│ 0       │
└─────────┘
sqlite> select 'numbnuts'>1;
┌──────────────┐
│ 'numbnuts'>1 │
├──────────────┤
│ 1            │
└──────────────┘
sqlite> select 'numbnuts'<1;
┌──────────────┐
│ 'numbnuts'<1 │
├──────────────┤
│ 0            │
└──────────────┘
sqlite>

This is documented behaviour.

https://sqlite.org/datatype3.html

When you compare a checkinTime against 1645311600000, the latter number has no affinity. Therefore, when you compare a text value with another number value (that does not have an affinity, so no affinity is applied to either the LHS or RHS arguments) then the text is always greater than the number ...

And/or use STRICT tables to find and avoid that issue in the first place.
Or add CHECK constraints (e.g. col int CHECK (typeof(col) = 'integer')) to regular tables.