Why does an exact-tag report admit a profile with extra tags?

Prepare the report input

Original report needs exactly required X/Y, with no extras. Matching all required tags proves subset coverage only. Two EXCEPT checks establish that neither required nor profile set has unmatched members. Profile C has no tags.

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 profiles(id TEXT PRIMARY KEY); CREATE TABLE required(tag TEXT PRIMARY KEY); CREATE TABLE tags(profile TEXT NOT NULL,tag TEXT NOT NULL,PRIMARY KEY(profile,tag)); INSERT INTO profiles VALUES('A'),('B'),('C'); INSERT INTO required VALUES('X'),('Y'); INSERT INTO tags VALUES('A','X'),('A','Y'),('B','X'),('B','Y'),('B','Z');

Compare misleading and corrected results

SELECT t.profile AS id FROM tags t JOIN required r ON r.tag=t.tag GROUP BY t.profile HAVING COUNT(*)=(SELECT COUNT(*) FROM required) ORDER BY id;

SELECT p.id FROM profiles p WHERE NOT EXISTS(SELECT tag FROM required EXCEPT SELECT tag FROM tags WHERE profile=p.id) AND NOT EXISTS(SELECT tag FROM tags WHERE profile=p.id EXCEPT SELECT tag FROM required) ORDER BY p.id;

Bad output includes A and B; B has extra Z. Correct output is A. Both sets must have no unmatched members; checking required coverage alone leaves B’s extra Z undetected.

Check a fresh boundary case

Make required set empty. Only empty profile C is an exact match; nonempty A/B do not 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.

DELETE FROM required;
SELECT p.id FROM profiles p WHERE NOT EXISTS(SELECT tag FROM required EXCEPT SELECT tag FROM tags WHERE profile=p.id) AND NOT EXISTS(SELECT tag FROM tags WHERE profile=p.id EXCEPT SELECT tag FROM required) ORDER BY p.id;