prefix table: 774 redundant rows, and 3 prefixes carrying another resource's base #124

The prefix table ships 1,275 rows that reduce to 489 distinct prefixes. Two separable problems: redundant rows, and prefixes with more than one expansion.

Identical in every build checked, so this is the shipped map rather than ontology content.

SELECT COUNT(*) AS rows, COUNT(DISTINCT prefix) AS distinct_prefixes,
       COUNT(DISTINCT prefix||' '||base) AS distinct_pairs FROM prefix;

1. Redundant rows: 774 of 1,275

Same prefix, same base, repeated.

SELECT SUM(extra) AS exact_duplicate_rows FROM
  (SELECT COUNT(*)-1 AS extra FROM prefix GROUP BY prefix, base HAVING COUNT(*)>1);

774. 247 prefixes appear exactly 4 times, one appears 8 times.

2. Twelve prefixes have two different expansions

SELECT prefix, COUNT(DISTINCT base) AS n_bases FROM prefix
GROUP BY prefix HAVING n_bases > 1 ORDER BY prefix;

Nine are alternate expansions of the same resource, which may be intended:

None collapse under case normalisation (still 12 with lower(base)).

3. Three prefixes carry a different resource's base

wikipathways, drugcentral and kegg.genome have no prefix rows of their own:

SELECT DISTINCT prefix, base FROM prefix
WHERE prefix IN ('wikipathways','drugcentral','kegg.genome');

Returns nothing. Each of those three resources exists in the table only as a base attached to a different prefix.

This resembles the symptom reported in #71 (a prefix picking up a neighbouring entry's expansion), which was closed by shipping a newer semsql through ODK rather than by changing the table. I am not claiming the same root cause, only that the shape is still present in current builds.

Relevant because the map is not configurable: #51 is still open.

Impact

Any lookup of the form SELECT base FROM prefix WHERE prefix = ? can return either expansion, decided by row order. For the three above it can return a different resource entirely.

Checked against

ncbitaxon.db (downloaded 2026-05-19) and envo.db (downloaded 2026-09-01) from the SemanticSQL CDN. Both give byte-identical prefix sets and the same 1,275 rows, so this is not specific to one ontology or one build date.