Uploaded image for project: 'CiviCRM'
  1. CiviCRM
  2. CRM-1204

Query for queue fails if $where is empty due to duplicate AND's

    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

        Attachments

          Activity

            People

            • Assignee:
              lobo Donald A. Lobo
              Reporter:
              mozillaman Robin Monks
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Time Tracking

                Estimated:
                Original Estimate - 15 minutes
                15m
                Remaining:
                Remaining Estimate - 15 minutes
                15m
                Logged:
                Time Spent - Not Specified
                Not Specified