Answers.Body: <p><strong>Quick Note</strong> The issue/solution I describe below only addresses broken Groups/Searches using the "on hold" email field. It's possible the Group/Search is broken through another field/value and the below info will only help specific cases. </p>
  
  <p><strong>Another Quick Note</strong> I solved a different Smart Group/Saved Search issue by switching "Empty" operators to "Null" operators in the Search criteria. StackOverflow post here: <a href="https://civicrm.stackexchange.com/questions/28802/search-builder-smart-group-with-latitute-street-address-criteria-1292-truncated">Search Builder Smart Group with Latitute, Street Address criteria 1292 Truncated DB Error</a></p>
  
  <p>We are having a similar issue and I have traced the error down to this WHERE condition: <code>civicrm_email.on_hold IN ("")</code>. I have also opened up an issue with CiviCRM: <a href="https://lab.civicrm.org/dev/core/issues/731" rel="nofollow noreferrer">https://lab.civicrm.org/dev/core/issues/731</a></p>
  
  <p><s>I do not have a fix yet, and would have left this as a comment and not an answer, but alas I do not have enough reputation yet.</s></p>
  
  <p><s>EDIT: Per Petednz's help and some investigation, it appears the process for generating these Smart Group queries for contact record was overhauled somewhere between 5.9 and 5.10 (specifically the CRM/Contact/BAO/Query.php went from 4000+ lines to ~100, where the "on_hold" condition was hard-coded in). I have not confirmed an upgrade to 5.10.2 will fix it, however I am operating on the assumption it will and can confirm it once I have done so.</s></p>
  
  <p>EDIT 2: Sterndata is right, I was looking at the wrong file. The existing offending code still exists in 5.10.2. A temporary workaround I have got working is to find this chunk of code CRM\Contact\BAO\Query.php (lines 1637-1642):</p>
  
  <pre><code>  elseif ($id == 'email_on_hold') {
      if ($onHoldValue = CRM_Utils_Array::value('email_on_hold', $formValues)) {
        $onHoldValue = (array) $onHoldValue;
        $params[] = array('on_hold', 'IN', $onHoldValue, 0, 0);
      }
    }
  </code></pre>
  
  <p>And then I add one line to check for and re-write the bogus value (the isset line):</p>
  
  <pre><code>  elseif ($id == 'email_on_hold') {
      if ($onHoldValue = CRM_Utils_Array::value('email_on_hold', $formValues)) {
        $onHoldValue = (array) $onHoldValue;
        if(isset($onHoldValue['on_hold']) &amp;&amp; !$onHoldValue['on_hold']) $onHoldValue = array(0);
        $params[] = array('on_hold', 'IN', $onHoldValue, 0, 0);
      }
    }
  </code></pre>
  
  <p>As far as I can tell, this value is hard-coded into the form_values column on the civicrm_saved_search table in a serialized array like so:</p>
  
  <pre><code>s:13:"email_on_hold";a:1:{s:7:"on_hold";s:0:"";}
  </code></pre>
  
  <p>The following code commit in 5.9.0 changed how the field works, by I believe, changing it to allow both a checkbox type OR a select list, depending on the values of what looks like a "Bulk Email" settings option: <a href="https://github.com/civicrm/civicrm-core/pull/12942/files#diff-1be8d2488755f023664ac3652a8d5de2R58" rel="nofollow noreferrer">https://github.com/civicrm/civicrm-core/pull/12942/files#diff-1be8d2488755f023664ac3652a8d5de2R58</a></p>
  
  <p>It doesn't appear there was a process for updating these values in the database to work with the new fields, but the upgrade did include a message stating that Smart Groups might be broken, and instructs to re-create the broken searches/smart groups. </p>
  
  <p><strong>Hopefully Last Edit</strong>
  I wrote some Drupal 7 specific PHP code (sorry sterndata I know you're on Wordpress) to find these saved searched rows in the table with the bad email_on_hold field data and update the data to work. I have tested it on my 5.9.1 site and it has worked fantastically for us. I ran it once and hopefully should never have to think about this again!</p>
  
  <p>However, per best practice please modify the code to suit your needs and then run it on one record first, preferably in a test environment!</p>
  
  <pre><code>  $query = db_select('civicrm_saved_search', 'ss');
    $query-&gt;fields('ss');
    $query-&gt;condition('ss.form_values','%' . db_like('email_on_hold') . '%', 'LIKE');
    // this condition might be too specific
    // $bad_data_string = '{s:7:"on_hold";s:0:"";';
    // $query-&gt;condition('ss.form_values','%' . db_like($bad_data_string) . '%', 'LIKE');
    // uncomment to only alter one specific record at a time
    // $query-&gt;condition('ss.id', 47);
    // $query-&gt;range(0,1);
    $result = $query-&gt;execute()-&gt;fetchAllAssoc('id');
    if(!empty($result)) {
      foreach($result as $record_id =&gt; $record) {
        $form_values = unserialize($record-&gt;form_values);
        if(isset($form_values['email_on_hold']) &amp;&amp; is_array($form_values['email_on_hold'])) {
          $email_on_hold = $form_values['email_on_hold'];
          if(isset($email_on_hold['on_hold']) &amp;&amp; !$email_on_hold['on_hold']) $email_on_hold = array(0);
        }
        // update only if different
        if($email_on_hold != $form_values['email_on_hold']) {
          $form_values['email_on_hold'] = $email_on_hold;
          $new_form_values = serialize($form_values);
          $update_on_hold = db_update('civicrm_saved_search');
          $update_on_hold-&gt;fields(array(
            'form_values' =&gt; $new_form_values,
          ));
          $update_on_hold-&gt;condition('id', $record_id);
          $update_on_hold-&gt;execute();
        }
      }
    }
  </code></pre>
  
  <p>Lastly, if you do run these fixes on the database, the one line I had added to the Query.php file is no longer necessary.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-02-15T18:54:36.627Z
Answers.Text: Thanks for posting the gitlab issue Joegl - will probably help other folk to look at this if you can confirm the issue persists in 5.10.2 and / or master
Answers.UserDisplayName: petednz - fuzion
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-02-15T18:57:37.907Z
Answers.Text: +1'd both your answers so you are > 50 so you can do comments now ;-)
Answers.UserDisplayName: petednz - fuzion
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-02-15T19:20:37.077Z
Answers.Text: Hey Petdnz! Nice to see you here too! Thanks for the +1's, I will make sure to update my answer.
Answers.UserDisplayName: joegl
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-02-15T22:05:58.267Z
Answers.Text: I'm using 5.10.2. It's still there.
Answers.UserDisplayName: sterndata
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-02-18T16:59:09.943Z
Answers.Text: Thanks @sterndata I've looked again and edited my answer.
Answers.UserDisplayName: joegl
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-02-18T20:45:47.337Z
Answers.Text: Thanks. I just unheld all emails (I'll re-hold them alter) and updated each smart group.  (never mind-- that didn't fix it. I'll look more closely at your code.)
Answers.UserDisplayName: sterndata
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-02-15T18:35:32.223Z
Answers.LastActivityDate: 2019-03-07T19:33:01.173Z
Answers.LastEditDate: 2019-03-07T19:33:01.173Z
Answers.UserDisplayName: joegl
Answers.Body: <p>The issue I was having, which I think is related to this same update, was that I couldn't go to a Smart Group and click "Contacts" and actually get the result. It gave an error:</p>
  
  <blockquote>
    <p>Sorry, due to an error, we are unable to fulfill your request at the moment. You may want to contact your administrator or service provider with more details about what action you were performing when this occurred.
    DB Error: unknown error</p>
  </blockquote>
  
  <p>This also didn't provide me with the usual "Edit Smart Group Criteria" link at the top of the page because it was a fatal error page. I did track down a way to do that, and update the smart group despite not actually changing anything, making it functional again without having to do anything directly in the database:</p>
  
  <ul>
  <li><p>Click "Settings" beside the Smart Group</p></li>
  <li><p>In the popup at the bottom you'll find "Edit Smart Group Criteria" and clicking on it will take you to the functional Smart Group page that lists the contacts/results.</p></li>
  <li><p>Select "All X results" and in the actions menu, choose "Group - Update Smart Group" (even though you haven't changed anything)</p></li>
  <li><p>Select "Update Smart Group" on the verification screen</p></li>
  </ul>
  
  <p>Now when you go to the Groups page, the Smart Groups will work as expected.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-03-07T18:41:15.813Z
Answers.Text: This is good advice as my solution only addresses a specific field. However, I wasn't able to fix another of ours which was broken in this manner.
Answers.UserDisplayName: joegl
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-03-07T21:16:36.293Z
Answers.Text: What happened in that one when you tried?
Answers.UserDisplayName: Laryn
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-03-07T22:21:56.280Z
Answers.Text: Laryn the smart group was still broken after I tried updating. However, I did eventually fix it and made a different post here: https://civicrm.stackexchange.com/questions/28802/search-builder-smart-group-with-latitude-street-address-criteria-1292-truncated
Answers.UserDisplayName: joegl
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-02-28T17:41:09.963Z
Answers.LastActivityDate: 2019-02-28T17:41:09.963Z
Answers.UserDisplayName: Laryn
Body: <p>My smart groups show unknown counts. Debugging shows this when I click "update smart group counts".</p>
  
  <p>Wordpress 5.0.3, Civi 5.9.0, PHP 7.2.14</p>
  
  <pre><code>#0 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Error.php(190): CRM_Core_Error::backtrace()
  #1 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(921): CRM_Core_Error::handle(Object(DB_Error))
  #2 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB.php(985): PEAR_Error-&gt;__construct("DB Error: unknown error", -1, 16, (Array:2), "CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...")
  #3 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(575): DB_Error-&gt;__construct(-1, 16, (Array:2), "CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...")
  #4 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(223): PEAR-&gt;_raiseError(Object(DB_mysqli), NULL, -1, 16, (Array:2), "CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...", "DB_Error", TRUE)
  #5 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1907): PEAR-&gt;__call("raiseError", (Array:7))
  #6 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB/mysqli.php(933): DB_common-&gt;raiseError(-1, NULL, NULL, "CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...", "1292 ** Truncated incorrect DOUBLE value: ''")
  #7 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB/mysqli.php(403): DB_mysqli-&gt;mysqliRaiseError()
  #8 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1216): DB_mysqli-&gt;simpleQuery("CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...")
  #9 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(2415): DB_common-&gt;query("CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...")
  #10 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(1607): DB_DataObject-&gt;_query("CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...")
  #11 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(438): DB_DataObject-&gt;query("CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...")
  #12 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(1413): CRM_Core_DAO-&gt;query("CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...", TRUE)
  #13 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/GroupContactCache.php(597): CRM_Core_DAO::executeQuery("CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...")
  #14 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(3106): CRM_Contact_BAO_GroupContactCache::load(Object(CRM_Core_DAO))
  #15 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(3037): CRM_Contact_BAO_Query-&gt;addGroupContactCache((Array:1), NULL, "contact_a", "IN")
  #16 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(1822): CRM_Contact_BAO_Query-&gt;group((Array:5))
  #17 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(2044): CRM_Contact_BAO_Query-&gt;whereClauseSingle((Array:5), NULL)
  #18 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(531): CRM_Contact_BAO_Query-&gt;whereClause(NULL)
  #19 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(491): CRM_Contact_BAO_Query-&gt;initialize(NULL)
  #20 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(4478): CRM_Contact_BAO_Query-&gt;__construct((Array:1), (Array:1), (Array:21), TRUE, FALSE, 1, TRUE, TRUE, FALSE, NULL, "AND", NULL, "1")
  #21 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/GroupContactCache.php(226): CRM_Contact_BAO_Query::apiQuery((Array:1), (Array:1), NULL, NULL, 0, 0, FALSE)
  #22 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/GroupContactCache.php(208): CRM_Contact_BAO_GroupContactCache::add((Array:3))
  #23 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Group/Page/Group.php(129): CRM_Contact_BAO_GroupContactCache::loadAll()
  #24 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Page/Basic.php(160): CRM_Group_Page_Group-&gt;browse(NULL, NULL)
  #25 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(309): CRM_Core_Page_Basic-&gt;run((Array:2), NULL)
  #26 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(84): CRM_Core_Invoke::runItem((Array:13))
  #27 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:2))
  #28 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm.php(1240): CRM_Core_Invoke::invoke((Array:2))
  </code></pre>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-02-15T14:45:03.753Z
Comments.Text: I'm still stuck on this, now with 5.10.2.
  
  How can I get a full debug on  the 2nd line:
  CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache1218 (SELECT 14 as gro...")
  
  that inner select.
Comments.UserDisplayName: sterndata
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-02-15T14:51:28.957Z
Comments.Text: Additional info at https://pastebin.com/Y3Z62PTf
  
  The key error seems to be  "
  Database Error Code: Truncated incorrect DOUBLE value: '', 1292"
Comments.UserDisplayName: sterndata
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-02-18T17:33:29.813Z
Comments.Text: I am seeing this on multiple sites -- I can't get a list of contacts in a smart group by clicking on "Contacts" as usual. It gives a fatal error similar to above. If I rebuild the group by using "Edit Smart Group Criteria" and resaving, then it works again.
Comments.UserDisplayName: Laryn
Tags: <error><smartgroups>
Title: Can't update smart group counts
ThreadText: # Can't update smart group counts
  
  (Asked by: username_0 on 2019-01-30)
  
  My smart groups show unknown counts. Debugging shows this when I click "update smart group counts".
  
  Wordpress 5.0.3, Civi 5.9.0, PHP 7.2.14
  
  ```
  #0 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Error.php(190): CRM_Core_Error::backtrace()
  #1 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(921): CRM_Core_Error::handle(Object(DB_Error))
  #2 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB.php(985): PEAR_Error->__construct("DB Error: unknown error", -1, 16, (Array:2), "CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...")
  #3 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(575): DB_Error->__construct(-1, 16, (Array:2), "CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...")
  #4 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -1, 16, (Array:2), "CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...", "DB_Error", TRUE)
  #5 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1907): PEAR->__call("raiseError", (Array:7))
  #6 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-1, NULL, NULL, "CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...", "1292 ** Truncated incorrect DOUBLE value: ''")
  #7 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
  #8 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery("CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...")
  #9 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(2415): DB_common->query("CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...")
  #10 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(1607): DB_DataObject->_query("CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...")
  #11 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(438): DB_DataObject->query("CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...")
  #12 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(1413): CRM_Core_DAO->query("CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...", TRUE)
  #13 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/GroupContactCache.php(597): CRM_Core_DAO::executeQuery("CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...")
  #14 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(3106): CRM_Contact_BAO_GroupContactCache::load(Object(CRM_Core_DAO))
  #15 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(3037): CRM_Contact_BAO_Query->addGroupContactCache((Array:1), NULL, "contact_a", "IN")
  #16 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(1822): CRM_Contact_BAO_Query->group((Array:5))
  #17 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(2044): CRM_Contact_BAO_Query->whereClauseSingle((Array:5), NULL)
  #18 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(531): CRM_Contact_BAO_Query->whereClause(NULL)
  #19 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(491): CRM_Contact_BAO_Query->initialize(NULL)
  #20 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(4478): CRM_Contact_BAO_Query->__construct((Array:1), (Array:1), (Array:21), TRUE, FALSE, 1, TRUE, TRUE, FALSE, NULL, "AND", NULL, "1")
  #21 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/GroupContactCache.php(226): CRM_Contact_BAO_Query::apiQuery((Array:1), (Array:1), NULL, NULL, 0, 0, FALSE)
  #22 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/GroupContactCache.php(208): CRM_Contact_BAO_GroupContactCache::add((Array:3))
  #23 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Group/Page/Group.php(129): CRM_Contact_BAO_GroupContactCache::loadAll()
  #24 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Page/Basic.php(160): CRM_Group_Page_Group->browse(NULL, NULL)
  #25 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(309): CRM_Core_Page_Basic->run((Array:2), NULL)
  #26 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(84): CRM_Core_Invoke::runItem((Array:13))
  #27 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:2))
  #28 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm.php(1240): CRM_Core_Invoke::invoke((Array:2))
  
  ```
  
  **Tags:** error, smartgroups
  
  > **Comments:**
  > **username_0**: I'm still stuck on this, now with 5.10.2.  How can I get a full debug on  the 2nd line: CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache1218 (SELECT 14 as gro...")  that inner select.  
  > **username_0**: Additional info at https://pastebin.com/Y3Z62PTf  The key error seems to be  " Database Error Code: Truncated incorrect DOUBLE value: '', 1292"  
  > **username_1**: I am seeing this on multiple sites -- I can't get a list of contacts in a smart group by clicking on "Contacts" as usual. It gives a fatal error similar to above. If I rebuild the group by using "Edit Smart Group Criteria" and resaving, then it works again.  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_2 on 2019-02-15. Score: 4
  
  **Quick Note** The issue/solution I describe below only addresses broken Groups/Searches using the "on hold" email field. It's possible the Group/Search is broken through another field/value and the below info will only help specific cases.
  
  **Another Quick Note** I solved a different Smart Group/Saved Search issue by switching "Empty" operators to "Null" operators in the Search criteria. StackOverflow post here: [Search Builder Smart Group with Latitute, Street Address criteria 1292 Truncated DB Error](https://civicrm.stackexchange.com/questions/28802/search-builder-smart-group-with-latitute-street-address-criteria-1292-truncated)
  
  We are having a similar issue and I have traced the error down to this WHERE condition: `civicrm_email.on_hold IN ("")`. I have also opened up an issue with CiviCRM: <https://lab.civicrm.org/dev/core/issues/731>
  
  ~~I do not have a fix yet, and would have left this as a comment and not an answer, but alas I do not have enough reputation yet.~~
  
  ~~EDIT: Per username_3's help and some investigation, it appears the process for generating these Smart Group queries for contact record was overhauled somewhere between 5.9 and 5.10 (specifically the CRM/Contact/BAO/Query.php went from 4000+ lines to ~100, where the "on\_hold" condition was hard-coded in). I have not confirmed an upgrade to 5.10.2 will fix it, however I am operating on the assumption it will and can confirm it once I have done so.~~
  
  EDIT 2: username_0 is right, I was looking at the wrong file. The existing offending code still exists in 5.10.2. A temporary workaround I have got working is to find this chunk of code CRM\Contact\BAO\Query.php (lines 1637-1642):
  
  ```
    elseif ($id == 'email_on_hold') {
      if ($onHoldValue = CRM_Utils_Array::value('email_on_hold', $formValues)) {
        $onHoldValue = (array) $onHoldValue;
        $params[] = array('on_hold', 'IN', $onHoldValue, 0, 0);
      }
    }
  
  ```
  
  And then I add one line to check for and re-write the bogus value (the isset line):
  
  ```
    elseif ($id == 'email_on_hold') {
      if ($onHoldValue = CRM_Utils_Array::value('email_on_hold', $formValues)) {
        $onHoldValue = (array) $onHoldValue;
        if(isset($onHoldValue['on_hold']) && !$onHoldValue['on_hold']) $onHoldValue = array(0);
        $params[] = array('on_hold', 'IN', $onHoldValue, 0, 0);
      }
    }
  
  ```
  
  As far as I can tell, this value is hard-coded into the form\_values column on the civicrm\_saved\_search table in a serialized array like so:
  
  ```
  s:13:"email_on_hold";a:1:{s:7:"on_hold";s:0:"";}
  
  ```
  
  The following code commit in 5.9.0 changed how the field works, by I believe, changing it to allow both a checkbox type OR a select list, depending on the values of what looks like a "Bulk Email" settings option: <https://github.com/civicrm/civicrm-core/pull/12942/files#diff-1be8d2488755f023664ac3652a8d5de2R58>
  
  It doesn't appear there was a process for updating these values in the database to work with the new fields, but the upgrade did include a message stating that Smart Groups might be broken, and instructs to re-create the broken searches/smart groups.
  
  **Hopefully Last Edit**
  I wrote some Drupal 7 specific PHP code (sorry username_0 I know you're on Wordpress) to find these saved searched rows in the table with the bad email\_on\_hold field data and update the data to work. I have tested it on my 5.9.1 site and it has worked fantastically for us. I ran it once and hopefully should never have to think about this again!
  
  However, per best practice please modify the code to suit your needs and then run it on one record first, preferably in a test environment!
  
  ```
    $query = db_select('civicrm_saved_search', 'ss');
    $query->fields('ss');
    $query->condition('ss.form_values','%' . db_like('email_on_hold') . '%', 'LIKE');
    // this condition might be too specific
    // $bad_data_string = '{s:7:"on_hold";s:0:"";';
    // $query->condition('ss.form_values','%' . db_like($bad_data_string) . '%', 'LIKE');
    // uncomment to only alter one specific record at a time
    // $query->condition('ss.id', 47);
    // $query->range(0,1);
    $result = $query->execute()->fetchAllAssoc('id');
    if(!empty($result)) {
      foreach($result as $record_id => $record) {
        $form_values = unserialize($record->form_values);
        if(isset($form_values['email_on_hold']) && is_array($form_values['email_on_hold'])) {
          $email_on_hold = $form_values['email_on_hold'];
          if(isset($email_on_hold['on_hold']) && !$email_on_hold['on_hold']) $email_on_hold = array(0);
        }
        // update only if different
        if($email_on_hold != $form_values['email_on_hold']) {
          $form_values['email_on_hold'] = $email_on_hold;
          $new_form_values = serialize($form_values);
          $update_on_hold = db_update('civicrm_saved_search');
          $update_on_hold->fields(array(
            'form_values' => $new_form_values,
          ));
          $update_on_hold->condition('id', $record_id);
          $update_on_hold->execute();
        }
      }
    }
  
  ```
  
  Lastly, if you do run these fixes on the database, the one line I had added to the Query.php file is no longer necessary.
  
  > **Comments:**
  > **username_3**: Thanks for posting the gitlab issue username_2 - will probably help other folk to look at this if you can confirm the issue persists in 5.10.2 and / or master  
  > **username_3**: +1'd both your answers so you are > 50 so you can do comments now ;-)  
  > **username_2**: Hey Petdnz! Nice to see you here too! Thanks for the +1's, I will make sure to update my answer.  
  > **username_0**: I'm using 5.10.2. It's still there.  
  > **username_2**: Thanks @username_0 I've looked again and edited my answer.  
  > **username_0**: Thanks. I just unheld all emails (I'll re-hold them alter) and updated each smart group.  (never mind-- that didn't fix it. I'll look more closely at your code.)  
  
  ---
  
  ### Answer by username_1 on 2019-02-28. Score: 1
  
  The issue I was having, which I think is related to this same update, was that I couldn't go to a Smart Group and click "Contacts" and actually get the result. It gave an error:
  
  > Sorry, due to an error, we are unable to fulfill your request at the moment. You may want to contact your administrator or service provider with more details about what action you were performing when this occurred.
  > DB Error: unknown error
  
  This also didn't provide me with the usual "Edit Smart Group Criteria" link at the top of the page because it was a fatal error page. I did track down a way to do that, and update the smart group despite not actually changing anything, making it functional again without having to do anything directly in the database:
  
  * Click "Settings" beside the Smart Group
  * In the popup at the bottom you'll find "Edit Smart Group Criteria" and clicking on it will take you to the functional Smart Group page that lists the contacts/results.
  * Select "All X results" and in the actions menu, choose "Group - Update Smart Group" (even though you haven't changed anything)
  * Select "Update Smart Group" on the verification screen
  
  Now when you go to the Groups page, the Smart Groups will work as expected.
  
  > **Comments:**
  > **username_2**: This is good advice as my solution only addresses a specific field. However, I wasn't able to fix another of ours which was broken in this manner.  
  > **username_1**: What happened in that one when you tried?  
  > **username_2**: username_1 the smart group was still broken after I tried updating. However, I did eventually fix it and made a different post here: https://civicrm.stackexchange.com/questions/28802/search-builder-smart-group-with-latitude-street-address-criteria-1292-truncated  
  
  ---
  
  