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

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 https://github.com/INCATools/semantic-sql/issues/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: https://github.com/INCATools/semantic-sql/issues/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.

Beginnen Sie damit, die bereitgestellten Abfragen gegen ncbitaxon.db und envo.db zu reproduzieren, wobei Sie sich auf die prefix table sowie die aufgeführten doppelten und ressourcenübergreifenden Zuordnungen konzentrieren. Vergleichen Sie die generierten prefix-Daten mit der erwarteten Ressourcenzuordnung und dem erwarteten Expansionsverhalten und überprüfen Sie anschließend, dass doppelte Zeilen und unbeabsichtigte Zuordnungen in beiden Datenbanken fehlen.