CiviCRM 5.9.1. Issue has occurred since an upgrade from version 4.7.17. 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
We can edit the criteria, and go to the initial list of contacts. But trying to run the search/query, view the contacts, or update the group creates a hard DB error. I have attempted to re-create the group and encountered the same exact issue.
This is the query in question (FYI the ID of the group is 58):
CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache1621 ( SELECT 58 as group_id, contact_a.id as id FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 ) WHERE(( (NULLIF(civicrm_address.geo_code_1, '') IS NULL) AND(NULLIF(civicrm_address.street_address, '') IS NOT NULL) )) AND (contact_a.is_deleted = 0) AND contact_a.id NOT IN ( SELECT contact_id FROM civicrm_group_contact WHERE civicrm_group_contact.status = 'Removed' AND civicrm_group_contact.group_id = 58 ) );
The resulting error is:
[nativecode=1292 ** Truncated incorrect DOUBLE value: '']
However, when I run the SELECT query alone in PhpMYAdmin without the "CREATE TEMPORARY TABLE" bit, the query works fine and displays results.
EDIT: When I try to run a Search Builder with the same criteria outside of a group, I am actually able to retrieve the results, yet the LOG is still showing a DB Error:
Ignoring exception thrown by nullHandler: -1, DB Error: unknown error
And I can't get a query in the logs because they are hidden in the traceroute.
EDIT: I put this in the answer as well, but I find it somewhat important to note now that whenever I attempted to edit this existing Smart Group's search criteria, I always had to re-select the operator.