Clinvar-based filtration leads to duplicate lines

[holtgrewe]

Describe the bug
When using the "Clinvar pathogenic" preset duplicate lines are possible.

To Reproduce
Steps to reproduce the behavior:

1. Filter the NA12878 trio with "Clinvar pathogenic" filter.
2. See the result:
   

Expected behavior
Only one line per variant should be shown.

Screenshots
See above.

Additional context

• Further follow-up: Clinvar records are always joined to the filter results #627

[holtgrewe]

Root Cause Analysis
We are using a LEFT JOIN in the SQL. If there are multiple Clinvar records (there is one per disease) this generates multiple output records.

[your-highness]

Dear @holtgrewe ,

I think our problem is linked to this issue but not dependent on the "Clinvar pathogenic" preset:

In our Athenea 1.2.1 installation when analysing NA-12878 for the reported CYP2C19:c.681G>A variant with all filters relaxed and "Gene Allowlist" set to "CYP2C19" duplicated lines are observed, e.g. for chr10:96,535,124.A>G 57 entries where displayed. I commented on one of the entries and all duplicates where shown as "commented" which indicates that the same entry is shown multiple times.

We checked if the imported TSV has multiple variant entries but it was not the case.

Config:

Results (Snippet):

Best,

[holtgrewe]

@your-highness We always join with clinvar. This also occurs outside of the clinvar filter but there the problem is obvious whereas the variants are not so obviously spotted outside.

[holtgrewe]

Further Root Cause Analysis
Originally, we had only one clinvar record in our internal tables. This changed in the last data release. We did not properly catch this change in VarFish Server.

[holtgrewe]

Resolution Proposal
Adjust ExtendQueryPartsClinvarJoin to use a lateral join and merge all records.

Affected Components
VarFish Server

Affected Modules/Files
variants.queries

Required Architectural Changes
None

Required Database Changes
None

Backport Possible?
Yes

Resolution Sketch

• Adjust ExtendQueryPartsClinvarJoin with the proposed change.
• Also, we need to adjust ExtendQueryPartsClinvarJoinAndFilter to still work correctly.

[holtgrewe]

• main PR is 565 clinvar based filtration leads to duplicate lines #630
• backport is Create single result row even if multiple clinvar entries (#565) #631