Filtering on derived bioassays column in physical bioassays list gives unexpected results

[Nicklas Nordborg]

On one of our production servers we have 107 physical bioassays with. All except 7 of them have at least one child derived bioassay.

Filtering on the derived bioassays column gives unexpected results:

• '<>': returns 7 hits, all 7 have a child bioassay. The expected result was the 100 bioassays that have a child.
• '=': return 100 hits instead of 7. The result include bioassays both with and without a child. The result doesn't include the 7 that was returned by the other query.

Filtering with 'derived bioassays<>'

[Nicklas Nordborg]

This was a bit tricky. The original query seems to be fine when viewed as HQL.

The real problem is that the join between physical and derived bioassays goes through an intermediary table (ParentPhysicalBioassays) that also holds links between child dervied bioassays and their original physical bioassay. Eg. if we have one physical bioassay (P) that is the parent to one (root) derived bioassay (DR) which is the parent to a child derived bioassay (DC) the join table will hold links for both DR->P and DC->P relationships.

But the query on the list page only joins the root derived bioassays which is a filter on the second join which is creating 'ghost' results for the main query (eg. it will return part of the relation DC->P but not the DC child itself) which is interpreted as if P has no child item.

[Nicklas Nordborg]

A workaround is to use the following equivalent filters:

• '=%' instead of '<>'
• '<>%' instead of '='

[Nicklas Nordborg]

(In [6646]) Fixes #1895: Filtering on derived bioassays column in physical bioassays list gives unexpected results

The issue was solved by detecting this case (by setting '%' as prefix in the filter property) and re-writing the query to the equivalent queries in the previous comment.

[Nicklas Nordborg]

(In [6647]) References #1895: Filtering on derived bioassays column in physical bioassays list gives unexpected results

And the fix in the list page as well...

[Nicklas Nordborg]

(In [6830]) References #1932: Improve display of selection list in table filters

Fixes 'not-equal' filters to also return items with 'null' value unless 'null' was included in the filter. This should work for regular item properties and single-valued annotations. It's unclear what happens when searching in collections (including multi-valued annotations) since that probably require a more complex approach (see #1895).