Why does a literal underscore search return other codes?

Prepare the report input

The fictional source permits uppercase ASCII letters, digits and underscores only; the report needs the literal prefix A_. In LIKE, underscore is a one-character wildcard, so A_% also matches A12 and AB1. The escaped pattern reserves only the final percent sign for an open suffix.

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 codes(code TEXT NOT NULL PRIMARY KEY CHECK(code NOT GLOB '*[^A-Z0-9_]*')); INSERT INTO codes VALUES('A_1'),('AB1'),('A12');

Compare misleading and corrected results

SELECT code FROM codes WHERE code LIKE 'A_%' ORDER BY code;

SELECT code FROM codes WHERE code LIKE 'A!_%' ESCAPE '!' ORDER BY code;

The misleading result has three codes; the correction retains A_1 only. The original code remains untouched. Escape handling is needed when constructing a pattern from literal input.

Check a fresh boundary case

Add A_ with an empty suffix. Percent permits zero following characters, so both A_ and A_1 qualify.

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 codes VALUES('A_');
SELECT code FROM codes WHERE code LIKE 'A!_%' ESCAPE '!' ORDER BY code;