Opened 7 years ago

Closed 7 years ago

Last modified 7 years ago

#1895 closed defect (fixed)

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

Reported by: Nicklas Nordborg Owned by: everyone
Priority: minor Milestone: BASE 3.4
Component: web Version:
Keywords: Cc:

Description

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<>'

Change History (5)

comment:1 Changed 7 years ago by 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.

So filtering on '=' will return those items that has no child at all and all items that also has a non-root child. Filtering on '<>' will only return those items that only has a root child without grandchildren.

Last edited 7 years ago by Nicklas Nordborg (previous) (diff)

comment:2 Changed 7 years ago by Nicklas Nordborg

A workaround is to use the following equivalent filters:

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

comment:3 Changed 7 years ago by Nicklas Nordborg

Resolution: fixed
Status: newclosed

(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.

comment:4 Changed 7 years ago by 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...

comment:5 Changed 7 years ago by 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).

Note: See TracTickets for help on using tickets.