Details
-
Type: Bug
-
Status: Done/Fixed
-
Priority: Major
-
Resolution: Fixed/Completed
-
Affects Version/s: 1.5
-
Fix Version/s: 1.5
-
Component/s: None
-
Labels:None
Description
Due to an error in the code, if $where returns empty, two AND's appear in the SQL. The attached patch fixes this issue. An error from this would look like:
Database Error Code: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND X_1.contact_id IS null' at line 15, 1064
error
Array ( [callback] => Array ( [0] => CRM_Core_Error [1] => handle )
[code] => -2 [message] => DB Error: syntax error [mode] => 16 [debug_info] => INSERT IGNORE INTO I_1 (email_id, contact_id) SELECT DISTINCT civicrm_email.id as email_id, civicrm_contact.id as contact_id FROM civicrm_contact contact_a LEFT JOIN civicrm_location ON (civicrm_location.entity_table = 'civicrm_contact' AND contact_a.id = civicrm_location.entity_id AND civicrm_location.is_primary = 1) LEFT JOIN civicrm_email ON (civicrm_location.id = civicrm_email.location_id AND civicrm_email.is_primary = 1) LEFT JOIN X_1 ON civicrm_contact.id = X_1.contact_id WHERE civicrm_contact.do_not_email = 0 AND civicrm_contact.is_opt_out = 0 AND civicrm_location.is_primary = 1 AND civicrm_email.is_primary = 1 AND civicrm_email.on_hold = 0 AND AND X_1.contact_id IS null [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND X_1.contact_id IS null' at line 15] [type] => DB_Error [user_info] => INSERT IGNORE INTO I_1 (email_id, contact_id) SELECT DISTINCT civicrm_email.id as email_id, civicrm_contact.id as contact_id FROM civicrm_contact contact_a LEFT JOIN civicrm_location ON (civicrm_location.entity_table = 'civicrm_contact' AND contact_a.id = civicrm_location.entity_id AND civicrm_location.is_primary = 1) LEFT JOIN civicrm_email ON (civicrm_location.id = civicrm_email.location_id AND civicrm_email.is_primary = 1) LEFT JOIN X_1 ON civicrm_contact.id = X_1.contact_id WHERE civicrm_contact.do_not_email = 0 AND civicrm_contact.is_opt_out = 0 AND civicrm_location.is_primary = 1 AND civicrm_email.is_primary = 1 AND civicrm_email.on_hold = 0 AND AND X_1.contact_id IS null [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND X_1.contact_id IS null' at line 15] [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT IGNORE INTO I_1 (email_id, contact_id) SELECT DISTINCT civicrm_email.id as email_id, civicrm_contact.id as contact_id FROM civicrm_contact contact_a LEFT JOIN civicrm_location ON (civicrm_location.entity_table = 'civicrm_contact' AND contact_a.id = civicrm_location.entity_id AND civicrm_location.is_primary = 1) LEFT JOIN civicrm_email ON (civicrm_location.id = civicrm_email.location_id AND civicrm_email.is_primary = 1) LEFT JOIN X_1 ON civicrm_contact.id = X_1.contact_id WHERE civicrm_contact.do_not_email = 0 AND civicrm_contact.is_opt_out = 0 AND civicrm_location.is_primary = 1 AND civicrm_email.is_primary = 1 AND civicrm_email.on_hold = 0 AND AND X_1.contact_id IS null [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND X_1.contact_id IS null' at line 15]"] )
Robin