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

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

None

Activity

Show:
Bruce Hoff
March 5, 2021, 4:38 PM

Michael's example works now.

Bruce Hoff
February 19, 2021, 2:24 AM

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

Bruce Hoff
February 11, 2021, 8:35 PM

Table indices are not yet built on staging. I may need to wait 'til next week to validate.

Ziming Dong
February 3, 2021, 12:51 AM

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.

Ziming Dong
June 22, 2020, 10:10 PM
Edited

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 "[]"?)

Fixed

Assignee

Ziming Dong

Reporter

Michael Lee

Validator

Bruce Hoff

Priority

Major

Labels

None

Development Area

None

Sprint

None

Fix versions

Release Version History

None

Story Points

None

Epic Link

None

Slack Channel

None