Why does WHERE kind=1 OR 2 include every kind?

Prepare the report input

Original selection permits kind 1 or 2 only. The literal 2 is true in SQLite Boolean context, so OR 2 makes every row pass, even an unknown kind. IN(1,2) states the intended comparisons.

Every record and report rule below is original and fictional. Reproduce the setup in a fresh SQLite test database; input previews include every table in this small fixture.

CREATE TABLE records(id TEXT PRIMARY KEY,kind INTEGER); INSERT INTO records VALUES('A',1),('B',2),('C',3),('D',NULL);

Compare misleading and corrected results

SELECT id,kind FROM records WHERE kind=1 OR 2 ORDER BY id;

SELECT id,kind FROM records WHERE kind IN(1,2) ORDER BY id;

The misleading result includes C=3 and D=NULL. The correction keeps A=1 and B=2; an excluded unknown row is not relabelled as any known kind.

Check a fresh boundary case

Change C from kind 3 to kind 2. It then joins the result on an actual matching value.

Recreate the original setup in another fresh database, then apply the change and corrected query below. For an empty result, the table displays a labelled empty-result marker rather than a data record.

UPDATE records SET kind=2 WHERE id='C';
SELECT id,kind FROM records WHERE kind IN(1,2) ORDER BY id;