Why does a yes flag fail while the text 1 passes?

Prepare the report input

The fictional source uses exact text yes for confirmed selection. no is unselected; 1 is an unexpected raw code. WHERE flag_text invokes SQLite numeric truth conversion rather than the source’s text meaning.

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 flags(id TEXT PRIMARY KEY,flag_text TEXT); INSERT INTO flags VALUES('A','yes'),('B','no'),('C','1');

Compare misleading and corrected results

SELECT id,flag_text FROM flags WHERE flag_text ORDER BY id;

SELECT id,flag_text FROM flags WHERE flag_text='yes' ORDER BY id;

The misleading query returns only C=1, while the correct confirmed-selection list is A=yes. Unexpected values remain in the input for separate review.

Check a fresh boundary case

Add NULL flag D. It is not a confirmed yes and does not join the corrected list.

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.

INSERT INTO flags VALUES('D',NULL);
SELECT id,flag_text FROM flags WHERE flag_text='yes' ORDER BY id;