Answers.Body: <p>Am I allowed to answer my own question? lol.</p>
  
  <p>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".</p>
  
  <p>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. </p>
  
  <p>EDIT: There is an open GitLab issue now: <a href="https://lab.civicrm.org/dev/core/issues/786" rel="nofollow noreferrer">https://lab.civicrm.org/dev/core/issues/786</a></p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-03-12T10:40:05.740Z
Answers.Text: Yes - you are allowed to answer your own questions!  Thanks for sharing what you found.
Answers.UserDisplayName: Aidan
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-03-07T19:11:43.540Z
Answers.LastActivityDate: 2019-03-13T16:25:09.950Z
Answers.LastEditDate: 2019-03-13T16:25:09.950Z
Answers.UserDisplayName: joegl
Body: <p>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:</p>
  
  <ul>
  <li>Contacts Primary Street Address is NOT EMPTY</li>
  <li>Contacts Primary Latitude IS EMPTY</li>
  </ul>
  
  <p>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.</p>
  
  <p>This is the query in question (FYI the ID of the group is 58):</p>
  
  <pre><code>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 
      ) 
  );
  </code></pre>
  
  <p>The resulting error is:</p>
  
  <pre><code>[nativecode=1292 ** Truncated incorrect DOUBLE value: '']
  </code></pre>
  
  <p>However, when I run the SELECT query alone in PhpMYAdmin without the "CREATE TEMPORARY TABLE" bit, the query works fine and displays results.</p>
  
  <p>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:</p>
  
  <pre><code>Ignoring exception thrown by nullHandler: -1, DB Error: unknown error
  </code></pre>
  
  <p>And I can't get a query in the logs because they are hidden in the traceroute. </p>
  
  <p>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.</p>
  
Tags: <contacts><error><database>
Title: Search Builder Smart Group with Latitude, Street Address criteria 1292 Truncated DB Error
ThreadText: # Search Builder Smart Group with Latitude, Street Address criteria 1292 Truncated DB Error
  
  (Asked by: username_0 on 2019-03-07)
  
  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.
  
  **Tags:** contacts, error, database
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_0 on 2019-03-07. Score: 3
  
  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>
  
  > **Comments:**
  > **username_1**: Yes - you are allowed to answer your own questions!  Thanks for sharing what you found.  
  
  ---
  
  