2

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.

2

Am I allowed to answer my own question? lol.

I was able to resolve the DB error for this specific smart group by changing the Operator criteria from "Empty" to "Null". So "IS EMPTY" became "IS NULL" and "NOT EMPTY" became "NOT NULL".

Also, I find it somewhat important to note now that whenever I attempted to edit this Smart Group's search criteria, I always had to re-select the "operator", so I wouldn't be surprised if the root of this issue has to do with the "Empty" operators.

EDIT: There is an open GitLab issue now: https://lab.civicrm.org/dev/core/issues/786

  • Yes - you are allowed to answer your own questions! Thanks for sharing what you found. – Aidan Mar 12 at 10:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.