Fix INDB Filter with NULLs to match behavior of In-Memory Filter

[fpinchon]

Unlike In-Memory Filter tool, the IN-DB Filter Tool does not process the NULL unless by default specified.

This results in the loss of data without warning.

I suggest you either fix it to match the in-memory filter, or display a warning somewhere, this creates many data quality issues...

NULL entry is NOT included!NULL entry is included

[SeanAdams]

hey @fpinchon 

cc: @bora_perusic 

I agree with your thinking - that null should behave the same whether you filter in-DB or not.

That being said - there is a reason (even if this doesn't satisfy your request which is completely reasonable).    Within SQL databases - null should fail all comparisons by definition because Null is essentially "not defined".   So null would fail the = filter; and technically null should also fail the != filter.   This is one of the things that always catches folk in SQL because it's not intuitive.

I've copied Bora on this - Bora has been looking at how to bring the InDB and the on-canvas tooling together for a while - he may have some thoughts on this one.

[bora_perusic]

Thanks a lot, @SeanAdams . 

Yes, it's the small issues like this that make the convergence of the standard tools and In-DB tools both desirable and difficult. 

There is no easy way to change the behavior of the In-DB tools for such things; in the In-DB tools the filters and formulas are "SQL language" while for normal tools they are in "Alteryx language"; and although they might look deceptively similar, their behavior might be different.

We are indeed thinking of a better way of translating the Alteryx workflows into SQL, but we have not really moved beyond PoC state yet. 

I will think about what we can do here. Thank you, @fpinchon , for the idea.

Bora

[fpinchon]

Don't get me wrong, I LOVE INDB tools, they are major productivity boosters. Even just a warning would be nice, because when I first transitioned from in-memory workflow, that issue tripped me so bad before I finally figured out what was going on...

[CristonS]

At this time we are unable to include this idea in our near future roadmap due to competing priorities; as such we're updating it to Not Planned. However, should this change or we become able to return to this idea in the future we'll be sure to update the status.