Search Builder: Not empty with date or integer custom fields gives a sql warning
Mentioned by @bgm for a contact reference in mattermost https://chat.civicrm.org/civicrm/pl/49hcecoy5bb9mmsnrfzuzt9a4y and reproduced with a date custom field.
To reproduce :
- create a contactReference custom field or a date custom field
- populate some data on a few records
- use the Search Builder to search on that field, with condition "where [field] is not empty"
Here is an example of query that Mysql doesn't like :
CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache1062 (SELECT 16 as group_id, contact_a.id as id FROM civicrm_contact contact_a
LEFT JOIN civicrm_value_cfa_contact_2 ON civicrm_value_cfa_contact_2.entity_id = `contact_a`.id WHERE ( ( contact_a.contact_type = "individual" AND (NULLIF(civicrm_value_cfa_contact_2.date_de_validation_manuelle_23, '') 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 = 16 ) );
The warning is :
SHOW warnings;
+---------+------+------------------------------+
| Level | Code | Message |
+---------+------+------------------------------+
| Warning | 1292 | Incorrect datetime value: '' |
+---------+------+------------------------------+
1 row in set (0.00 sec)