Smart Group DB Errors post-upgrade, specifically 1292 Truncated incorrect DOUBLE value
CiviCRM 5.9.1 and Drupal 7. Recently upgraded from 4.7.17.
Lots of smart groups are failing now with DB errors (can't use them or edit them). This is a relevant StackExchange I found: https://civicrm.stackexchange.com/questions/28268/cant-update-smart-group-counts
Here are two specific MySQL queries which fail:
CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache449 (SELECT 9 as group_id, contact_a.id as id FROM civicrm_contact contact_a LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1) LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id WHERE ( civicrm_email.on_hold IN ("") AND (civicrm_contribution.receive_date >= '20110701000000' ) AND (civicrm_contribution.receive_date <= '20120630235959' ) AND civicrm_contribution.financial_type_id IN ("5", "1", "4", "12", "11", "6", "9", "2", "8", "10") AND civicrm_contribution.financial_type_id = 1 ) 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 = 9 ) ); [nativecode=1292 ** Truncated incorrect DOUBLE value: '']
AND
CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache1477 (SELECT 208 as group_id, contact_a.id as id FROM civicrm_contact contact_a LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1) LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id LEFT JOIN civicrm_membership ON civicrm_membership.contact_id = contact_a.id LEFT JOIN civicrm_contribution_recur ccr ON ( civicrm_membership.contribution_recur_id = ccr.id ) WHERE (civicrm_email.on_hold IN ("") AND contact_a.contact_type IN ("Individual", "Household") AND civicrm_contribution.is_test = 0 AND civicrm_membership.is_test = 0 AND civicrm_membership.status_id IN ("1")) 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 = 208 ) ); [nativecode=1292 ** Truncated incorrect DOUBLE value: '']
I am not very familiar with working with temporary tables. However, when I run the SELECT query inside the parentheses without the "CREATE TEMPORARY TABLE" portion, it returns successfully. However, PHPMyAdmin does not like it when I add the "CREATE TEMPORARY TABLE" bit. The MySQL has user proper permissions and it's an InnoDB MySQL database.
EDIT: I've tried to post the queries with indentations and line breaks but they don't stay so I apologize for the formatting.