Filtering against NULL vs empty string causes confusion

[sunnysideup]

Module version(s) affected

5.x-dev

Description

See original ticket here: #11363 (comment)

To sum up what's being discussed in the Silverstripe User's Slack at the moment:

• it appears the SS is doing some "magic" to set an empty string (in this case) as null to represent an "empty" value. There's some debate as to whether this is desired, but it is currently the case
• the problem that is occurring at the moment is a disconnect between the filtering behaviour and the storage behaviour - if I can set '' and get null, I should be able to filter for '' and return null results. It is this inconsistency that is the core of the issue
  so any proposed changes should either:
  a. lean into the magic, and have the filter understand it
  b. lean away from the magic, and have things stored as they are put in

Proposed solution: If you filter for NULL or EMPTY STRING it should yield the same result, unless you set some "strict" setting, in which case it would go for NULL or EMPTY STRING only.

How to reproduce

foreach(MyDataObject::get() as $myObject) {
    $myObject->MyField = '';
    $myObject->write();
}
echo 
    MyDataObject::get()->count() .
    '=' .
    MyDataObject::get()->filter(['MyField' => ''])->count()
;

This results in xyz=0
where xyz is the number of records and 0 is always zero!

Possible Solution

see: #11363

Additional Context

No response

Validations

• Check that there isn't already an issue that reports the same bug
• Double check that your reproduction steps work in a fresh installation of silverstripe/installer (with any code examples you've provided)

[GuySartorelli]

What would the strict setting look like? Would you set it globally or per datalist or per filter?

If we're leaning into the magic, it probably should apply only for the DBField implementations we know use that magic. I don't know off the top of my head if the filter uses any introspection about the fields it's filtering against though.