Adding tree ranks to queries will incorrectly filter valid records

[realVinayak]

Related to code I pushed a while ago. Wanted to make this issue before but just got time. Introduced in #5036.

There's a bug below

specify7/specifyweb/stored_queries/query_construct.py

Line 93 in b69dac3

new_filters = [*query.internal_filters, getattr(node, treedef_column).in_(defs_to_filter_on)]

It'll always apply the in predicate. That means if you make a query like Determination -> Taxon (species) -> Fullname, with "any" filter, determinations without a taxon will be absent. This is not correct, and a regression

I haven't tested this anywhere yet (since sp7demofish still seems to run 7.9.6.2). If someone on testing could verify this, it'll be good.

Instructions

1. Make a collectionobject. Assign taxon (species) to it. You could technically assign anything, but for simplicity just assign species OR remember what rank was assigned.
2. Make another collectionobject, Do not assign taxon to it.

Run the following query. Basically Determination -> Taxon (species) -> Fullname

You'll notice that only collectionobject from first step is returned, and from second step is not returned (even though it should, just blank for species field).

This bug is on production, and not on 7.9.6.2, so couldn't test it myself.

The correct fix would be to change the predicate to be

specify7/specifyweb/stored_queries/query_construct.py

Line 93 in b69dac3

new_filters = [*query.internal_filters, getattr(node, treedef_column).in_(defs_to_filter_on)]

import sqlalchemy
sqlalchemy.or_(getattr(node, treedef_column).in_(defs_to_filter_on), getattr(node, treedef_column) == None)

Technically you could push None outside, but query optimizers should optimize that out anyways

Not related to #5402. 🤯