v_ahb_diff: id_path-based JOIN produces false adds/deletes when segments are inserted

[hf-mrdachner]

Problem

v_ahb_diff joins old and new version on id_path. The id_path contains positional indices (e.g. @00005-00001-). When a new segment is inserted in a newer format version, all subsequent positional indices shift — causing the JOIN to fail and producing false added + deleted rows for lines that haven't actually changed.

Example: UTILMD/44001, FV2510 → FV2604

• FV2510: 213 lines
• FV2604: 218 lines (5 genuinely new)
• v_ahb_diff reports: 69 added, 64 deleted, 27 modified — but most of those 69+64 are the same lines with shifted id_path

For example, DTM "Beginn zum" has id_path SG4>DTM>@00005-00001- in FV2510 but a different index in FV2604 because IMD segments were inserted before it. The view sees this as a delete + add.

Root cause

id_path is a positional index within a version, not a stable semantic identifier across versions. The v_ahb_diff view uses it as a cross-version JOIN key, which breaks on insertions.

Possible fix

Use a semantic key for the JOIN instead of the positional id_path. A stable identifier could be built from the structural path + qualifying qualifiers (the "front" qualifiers that identify which segment it is, not the value qualifiers):

• SG4>DTM+92 = "Beginn zum" (Qualifier 92 identifies the segment)
• SG4>DTM+93 = "Ende zum" (Qualifier 93 identifies the segment)
• SG4>STS+7 = Transaktionsgrund

This would be stable across versions as long as the MIG structure doesn't fundamentally change (which it doesn't — segments get added but the order stays the same).

Impact

ahb-tabellen's compare feature shows the same inflated numbers. Downstream consumers (like our MCP server) have to implement content-fingerprint matching as a workaround to filter the noise.

Workaround (current)

In mcp-bdew-mako we post-process the v_ahb_diff output with two-pass fingerprint matching:

1. Strict match (identity + content identical) → filter as noise
2. Loose match (identity only, via segment_code + data_element + qualifier) → reclassify as modified

This reduces UTILMD/44001 from 160 "changes" to ~30 real ones.

[hf-mrdachner]

Finding: (path, segment_code) is a stable, unique key

Tested on UTILMD/44001 (FV2510 → FV2604):

Key	Unique in FV2510?	Unique in FV2604?
id_path	✅ 0 dupes	✅ 0 dupes
id_path without @-positions	❌ 59 dupes	❌ 59 dupes
path alone	❌ 2 dupes	❌ 2 dupes
(path, segment_code)	✅ 0 dupes	✅ 0 dupes

Result comparison

JOIN key	added	deleted	modified	unchanged
id_path (current)	69	64	27	122
(path, segment_code)	5	0	1	212

The (path, segment_code) join correctly identifies:

• 5 genuinely new lines (IMD Identifikationslogik, STS Lieferbeginn, etc.)
• 1 modified line
• 0 deleted lines
• 212 unchanged lines

This matches the expected "mini small" format change for FV2604.

Why it works

• path is the human-readable hierarchy path (e.g. "MP-ID Absender > MP-ID Absender > Identifikation des Beteiligten > MP-ID") — it carries parent context (Absender vs Empfänger) that disambiguates repeated structures
• segment_code disambiguates the 2 remaining cases where path alone collides (SEQ vs SG9 under same parent name)
• Unlike id_path, this key doesn't contain positional indices, so insertions don't break the JOIN

Proposed fix

Replace old_tbl.id_path = new_tbl.id_path with old_tbl.path = new_tbl.path AND old_tbl.segment_code IS new_tbl.segment_code in v_ahb_diff.

[hf-kklein]

The id_path contains positional indices (e.g. @00005-00001-)

IIRC those positional things were thought as a tie-breaker mechanism to make id-paths that otherwise wouldn't be unique, truely unique. I think there's even a test for it. The main idea of the id_path is, that it guarantees:

1. together with formatversion+prüfi it forms a unique triple across the database
2. you can join on id-path alone (when comparing same prüfi and different formatversion) without considering other columns.

I'd like to keep both concepts, because they are very useful and simplify everything downstream.
That being said: I also think it's not perfect and the behaviour you described for the 44001 comparison is a clear bug. So yes, we need to to something about it.

When a new segment is inserted in a newer format version, all subsequent positional indices shift — causing the JOIN to fail and producing false added + deleted rows for lines that haven't actually changed.

yeah, i see the issue and couldn't find away around it so far. The core questions is: When do you consider a line that occurs in 2 versions to be the same thing (with the same id path) to have changed (modified)? and when is it a new thing (with a new id-path) that replaces an old thing (with an old id-path) in an add/delete manner? if you check ahb-tabellen you get a feeling how it works so far (including the bug for 44001 you mentioned).

I think any new approach to define something like an ID for ahb lines (aka id_path) should first answer this question and ideally it's clearly answered in the code for any future maintainer and reader.

Now about the fix suggested:

Use a semantic key for the JOIN instead of the positional id_path. A stable identifier could be built from the structural path + qualifying qualifiers (the "front" qualifiers that identify which segment it is, not the value qualifiers):

SG4>DTM+92 = "Beginn zum" (Qualifier 92 identifies the segment)
SG4>DTM+93 = "Ende zum" (Qualifier 93 identifies the segment)
SG4>STS+7 = Transaktionsgrund

I like it and I think I even tried exactly that. But somehow I didn't get to a satisfying result in the end... Did you check this would be truely unique or do we still need a tie-breaker then?
The unique-constraints on the DB shuold be kept as they are. If any new code/id-path algorithm gives the same guarantees as the existing id-path, I'm happy to merge it :)

[hf-kklein]

Fix implemented in #260: instead of changing the diff view JOIN key (as proposed in #257), this fixes id_path construction itself in materialize_ahb_view.sql.

The approach injects semantic qualifiers from EDIFACT code values into the id_path during the recursive CTE (e.g., SG4>DTM+92> instead of SG4>DTM>@00004-00001-). This keeps id_path as a single unique column while making it stable across format versions.

Thanks to @hf-mrdachner for the thorough bug report and analysis that made this fix possible.