Filter parameter and inconsistent outputs

[oganm]

Filter terms separated by and should return more and more specialised results as more terms are added but this doesn't seem to be the case for reasons unknown.

I am using the presence of the dataset 549 as the indicator in the examples below, but results are consistent with examining the entire output via offset

Case 1

• filter = allCharacteristics.valueUri = http://purl.obolibrary.org/obo/UBERON_0000955 and id = 549
• https://dev.gemma.msl.ubc.ca/rest/v2/datasets/?filter=allCharacteristics.valueUri%20%3D%20http%3A%2F%2Fpurl.obolibrary.org%2Fobo%2FUBERON_0000955%20and%20id%20%3D%20549&offset=0&limit=20&sort=%2Bid
• Total elements without id = 549 is 749
• Empty result

Case 2

• filter = allCharacteristics.valueUri = http://purl.obolibrary.org/obo/UBERON_0000955 and allCharacteristics.valueUri = http://purl.obolibrary.org/obo/DOID_14330 and id = 549
• https://dev.gemma.msl.ubc.ca/rest/v2/datasets/?filter=allCharacteristics.valueUri%20%3D%20http%3A%2F%2Fpurl.obolibrary.org%2Fobo%2FUBERON_0000955%20and%20allCharacteristics.valueUri%20%3D%20http%3A%2F%2Fpurl.obolibrary.org%2Fobo%2FDOID_14330%20and%20id%20%3D%20549&offset=0&limit=20&sort=%2Bid
• Total elements without id = 549 is 163
• 549 is returned

Dataset 549 doesn't have the term UBERON_0000955 or any of the associated terms that it propagates to looking at the filter term in the outputs. The closest thing it has is UBERON_0002038 which is a region of the brain. It does have the other term DOID_14330

[arteymix]

Propagation is indeed broken, I filed a separate issue for this: #603.

I'll investigate why the conjunction of characteristics filters don't work.

[oganm]

This is the issue we talked about today. The first example case above now returns 549. The second example case above now returns... nothing. I suspect the second case is because DOID isn't loaded on dev.

[oganm]

Popping back to confirm that this issue still appear to exist albeit in a different form so I am adding up to date examples and with a list of grievances below along with the assumptions I am making when expecting the results in case there is a mismatch there

Case 1, incomplete inheritance

• Assumption: a search by an ontology term should return matches with it's children. The search below attempts to use "http://purl.obolibrary.org/obo/UBERON_0000955" to get all brain samples including different brain regions. Note that this test is done on the dev server since filtering by characteristics isn't yet supported on production
• filter = allCharacteristics.valueUri = http://purl.obolibrary.org/obo/UBERON_0000955
• this search only returns 719 experiments.
• Looking at the "filter" in the output, it seems like only http://purl.obolibrary.org/obo/UBERON_0000955, http://www.ebi.ac.uk/efo/EFO_0002518, http://www.ebi.ac.uk/efo/EFO_0002520 were considered

Case 2, failure to return overlap of two queries of allCharacteristics.valueUri or allCharacteristics.value when "and" is used

• Assumption: using "and" within filter should return the intersection of the two queries. The original example in this issue was a case where an additional experiment snuck in when using "and". When trying to replicate this however I was unable to get any results when querying for two different "allCharacteristics.valueUris". I tried this with a few different valueURIs that I knew to be present within one of the searches but nothing seemed to help.
• filter = allCharacteristics.valueUri = http://purl.obolibrary.org/obo/MONDO_0005180 returns 167 elements, 16 of which are also returned by filter = allCharacteristics.valueUri = http://purl.obolibrary.org/obo/UBERON_0000955. Experiment 49 is an example that appears on first page for both searches.
• filter = allCharacteristics.valueUri = http://purl.obolibrary.org/obo/MONDO_0005180 and allCharacteristics.valueUri = http://purl.obolibrary.org/obo/UBERON_0000955 returns nothing
• Same issue can be replicated using allCharacteristics.value. filter = allCharacteristics.value = "brain" and allCharacteristics.value = "parkinson disease" will return nothing while both filter = allCharacteristics.value = "brain" and filter = allCharacteristics.value = "parkinson disease" will return, for example, experiment 49

Case 3 duplicated results when querying for ids.

• Assumption: A valid search result should be returned once.
• filter = id = 23391 and filter id = 23730 will return themselves twice. This also happens if filter isn't used and a single dataset is requested directly. This is a rare case

[arteymix]

Just to be clear, if you submit multiple filter parameters, the last one take precedence which is consistent with just performing the second query.

The and return no result because of how the SQL is generated. I do a jointure on the characteristics so having multiple conjunctive clauses on the same attribute will not work.

I need to adjust these queries to use subqueries instead of jointures.

[oganm]

if you submit multiple filter parameters

None of the examples do this do they? Or are we talking about using "and"?

If it's case 3 those are separate calls

[arteymix]

I've investigated this, and it turns out that resolving this require significant work.

At the very fundamental level, we cannot express a conjunction on a one-to-many relation with a single jointure. The conjunction will actually turn into a contradiction.

c.id = 1 and c.id = 2

which is why you get zero results. Instead, we need to either create one jointure per clause

c1.id = 1 and c2.id = 2

or use subqueries

id in (select ... join c on c.id = 1) and id in (select ... join c on c.id = 2)

I think I will ultimately opt for expressing those using subqueries since it appears to be the simplest and most flexible approach.

This is being looked at in #708.

[arteymix]

allCharacteristics.valueUri = http://purl.obolibrary.org/obo/MONDO_0005180 and allCharacteristics.valueUri = http://purl.obolibrary.org/obo/UBERON_0000955 now returns the expected 16 intersecting results.

[arteymix]

How curious that results appear duplicated when filtered by ID. I'll investigate that...

[arteymix]

I know what's going on: the ACL entries are joined in the query, but it's lacking a group by because we deemed it unnecessary. I'll add an extra check for that.

[arteymix]

That only leaves the propagation. I'll investigate it now.

[oganm]

The propagation still seems a bit incomplete.

Using the annotation/search/datasets endpoint I ran a search for http://purl.obolibrary.org/obo/UBERON_0000955. This returns 3950 results

Whereas datasets endpoint by filter returns 1796 results.

These calls aren't exactly equivalent so I examined some of the missing cases. Experiment 18 is such an example. It is annotated by frontal cortex and frontal lobe both of which should be children of brain (http://purl.obolibrary.org/obo/UBERON_0000955) yet the experiment isn't returned.

[arteymix]

These two endpoints should return exactly the same thing. I'm deploying the fix for #729 so you can test it on the dev.

Otherwise that means that the inference is behaving differently from prod to dev. This might be expected because we use slimmer ontologies.