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

[turbomam]

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;

rows	distinct_prefixes	distinct_pairs
1275	489	501

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:

prefix	expansions
CHEMINF	obo/CHEMINF_, semanticscience.org/resource/CHEMINF_
FMA	obo/FMA_, purl.org/sig/ont/fma/fma
MESH	id.nlm.nih.gov/mesh/, identifiers.org/mesh/
RESID	obo/RESID_, proteininformationresource.org/cgi-bin/resid?id=
SWO	obo/SWO_, ebi.ac.uk/swo/SWO_
dbpedia	dbpedia.org/, dbpedia.org/resource/
qudt	qudt.org/schema/qudt#, qudt.org/schema/qudt/
reactome.biopax	reactome.org/biopax/77/48887#, .../81/48887#
schema	http://schema.org/, https://schema.org/

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

3. Three prefixes carry a different resource's base

prefix	own base	also mapped to
complexportal	obo/complexportal_	obo/wikipathways_
drugbank	obo/drugbank_	obo/drugcentral_
pathbank	obo/pathbank_	obo/kegg.genome_

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.