Search Builder: Empty Operators create DB Errors; switching to NULL Operators fixes
StackOverflow here: https://civicrm.stackexchange.com/questions/28802/search-builder-smart-group-with-latitute-street-address-criteria-1292-truncated
We have upgraded from 4.7.17 to 5.9.1. This was initially a Smart Group DB Error issue, but I believe it has more to do with the Empty vs Null operators. The Smart Group/Contact Group in question is built on two simple search builder criteria:
Contacts Primary Street Address is NOT EMPTY
Contacts Primary Latitude IS EMPTY
The resulting error from trying to update this Smart Group is:
[nativecode=1292 ** Truncated incorrect DOUBLE value: '']
Additionally, when I attempt to create a new Search Builder with the same criteria, I wouldn't get a hard DB Error page fail, but I still saw this in the logs:
Ignoring exception thrown by nullHandler: -1, DB Error: unknown error
When I switched both the operators from EMPTY to NULL in the criteria, I had no problems with search and was able to successfully update the Smart Group.
One thing which I think is important/pertinent is when I initially loaded the "Edit Search Criteria" page for the existing Smart Group, the Search did not remember the operators and I had to re-select them -- this leads me to believe the field storage methodology for the operator lists changed at some point, and the options we had selected in this search were not carried over properly in an upgrade, but I am not certain.