Query result has zero rows when using list-type columns and facet values uses UNDEFINED_NULL_NOTSET
Description
It appears that the backend is not processing the org.sagebionetworks.UNDEFINED_NULL_NOTSET value correctly for list-type columns when using that value in the facetValues query request field. It is incorrectly returning zero results.
Steps to replicate issue
Visit my demo project here - https://www.synapse.org/#!Synapse:syn22041545/tables/query
Under 'veggie list' click 'Not set'
Zero rows are returned instead of the two containing 'apple, pear' and 'strawberry'
One thing to note-
I noticed this issue arises when there are two or more list-type columns, with a single list type column there is no issue
Environment
Activity
Michael's example works now.
I tried to look at Michael's example table and got a stack trace, could you please have a look? https://www.synapse.org/#!Synapse:syn22041545/tables/query
Table indices are not yet built on staging. I may need to wait 'til next week to validate.
For validation, any rows with empty list values ("[]") should have been converted to NULL and should appear in a WHERE myCol is NULL query. A default value of "[]" in a ColumnModel should also be automatically set to null when stored.
From a quick glance, the SQL generated from the facet is correct, and there's even tests in the backend that test for that specific case.
In your schema, one column has a default value of "[]" while the other does not. The null value works as expected while empty list does not.
So the problem is that the backend is not able to differentiate null and empty lists when reporting facet counts, which involves exploding the list. I think we should disallow the empty list case, much like how we don't allow an empty list in Annotations. (or we automatically convert empty lists into nulls since some clients might be defaulting to "[]"?)
