Filtering using a custom query, unexpected handling of AND condition

aemondis

Perhaps my logic is failing me here, but I'm struggling with what I would deem, a very basic function - that is to filter on two boolean fields, whilst allowing NULL. In essence, I have two fields: isActive, isTrial. For my GET API, these fields are both parameters that allow null. My goal is quite simple - allowing a NULL on either field should allow a true or false value to return, whereas specifying a value should ensure only fields with that value is set.

My custom query:

The output as below. I would have expected there to be zero records returned, since there are no records with isActive = false in the database, instead I get ALL records since isTrial is NULL. Instead, it almost seems as if Xano is treating my conditional query as an OR, despite it being clearly configured as an AND? Both fields are in the same table and are not linked to any other tables.

aemondis

Anybody have an insight into this behaviour? Is this a bug, and if so - can it please be verified from somebody?

Sean Montgomery

@aemondis

Ignore empty values doesn't work properly with false booleans. That definitely is a bug.

The solution is to use an enum to represent your true/false values instead of a boolean, if you need to rely on the ignore empty values functionality.

aemondis

Thanks Sean, glad to hear I'm not losing my mind on this! This has been driving me insane for weeks now. I would prefer to avoid changing the DB fields (given there is already data there…), will have a think about how to achieve this.