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

"DB Error: syntax error" on Event Participant (list) report when "Group By: Event" is selected

    Details

    • Type: Bug
    • Status: Done/Fixed
    • Priority: Trivial
    • Resolution: Fixed/Completed
    • Affects Version/s: 3.4.6
    • Fix Version/s: 4.1.0
    • Component/s: CiviReport
    • Labels:
      None

      Description

      Story: If an admin level user wants to get an event participant report that is grouped by events and alpha by participant last name, and they try to use the "Event Participant (list) report" with the "group by: event" selected, they will get this error:
      DB Error: syntax error

      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 'ORDER BY contact_civireport.sort_name ASC LIMIT 0, 50' at line 15, 1064
      Additional Details:
      Array
      (
      [callback] => Array
      (
      [0] => CRM_Core_Error
      [1] => handle
      )

      [code] => -2
      [message] => DB Error: syntax error
      [mode] => 16
      [debug_info] => SELECT SQL_CALC_FOUND_ROWS sort_name as civicrm_contact_sort_name_linked, contact_civireport.id as civicrm_contact_id, participant_civireport.id as civicrm_participant_participant_record, participant_civireport.event_id as civicrm_participant_event_id, participant_civireport.status_id as civicrm_participant_status_id, participant_civireport.role_id as civicrm_participant_role_id
      FROM civicrm_participant participant_civireport
      LEFT JOIN civicrm_event event_civireport
      ON (event_civireport.id = participant_civireport.event_id ) AND
      (event_civireport.is_template IS NULL OR
      event_civireport.is_template = 0)
      LEFT JOIN civicrm_contact contact_civireport
      ON (participant_civireport.contact_id = contact_civireport.id )

      LEFT JOIN civicrm_address address_civireport
      ON contact_civireport.id = address_civireport.contact_id AND
      address_civireport.is_primary = 1
      LEFT JOIN civicrm_email email_civireport
      ON (contact_civireport.id = email_civireport.contact_id AND
      email_civireport.is_primary = 1) WHERE participant_civireport.is_test = 0 AND contact_civireport.is_deleted = 0 ORDER BY participant_civireport.event_id, contact_civireport.sort_name ORDER BY contact_civireport.sort_name ASC LIMIT 0, 50 [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 'ORDER BY contact_civireport.sort_name ASC LIMIT 0, 50' at line 15]
      [type] => DB_Error
      [user_info] => SELECT SQL_CALC_FOUND_ROWS sort_name as civicrm_contact_sort_name_linked, contact_civireport.id as civicrm_contact_id, participant_civireport.id as civicrm_participant_participant_record, participant_civireport.event_id as civicrm_participant_event_id, participant_civireport.status_id as civicrm_participant_status_id, participant_civireport.role_id as civicrm_participant_role_id
      FROM civicrm_participant participant_civireport
      LEFT JOIN civicrm_event event_civireport
      ON (event_civireport.id = participant_civireport.event_id ) AND
      (event_civireport.is_template IS NULL OR
      event_civireport.is_template = 0)
      LEFT JOIN civicrm_contact contact_civireport
      ON (participant_civireport.contact_id = contact_civireport.id )

      LEFT JOIN civicrm_address address_civireport
      ON contact_civireport.id = address_civireport.contact_id AND
      address_civireport.is_primary = 1
      LEFT JOIN civicrm_email email_civireport
      ON (contact_civireport.id = email_civireport.contact_id AND
      email_civireport.is_primary = 1) WHERE participant_civireport.is_test = 0 AND contact_civireport.is_deleted = 0 ORDER BY participant_civireport.event_id, contact_civireport.sort_name ORDER BY contact_civireport.sort_name ASC LIMIT 0, 50 [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 'ORDER BY contact_civireport.sort_name ASC LIMIT 0, 50' at line 15]
      [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT SQL_CALC_FOUND_ROWS sort_name as civicrm_contact_sort_name_linked, contact_civireport.id as civicrm_contact_id, participant_civireport.id as civicrm_participant_participant_record, participant_civireport.event_id as civicrm_participant_event_id, participant_civireport.status_id as civicrm_participant_status_id, participant_civireport.role_id as civicrm_participant_role_id
      FROM civicrm_participant participant_civireport
      LEFT JOIN civicrm_event event_civireport
      ON (event_civireport.id = participant_civireport.event_id ) AND
      (event_civireport.is_template IS NULL OR
      event_civireport.is_template = 0)
      LEFT JOIN civicrm_contact contact_civireport
      ON (participant_civireport.contact_id = contact_civireport.id )

      LEFT JOIN civicrm_address address_civireport
      ON contact_civireport.id = address_civireport.contact_id AND
      address_civireport.is_primary = 1
      LEFT JOIN civicrm_email email_civireport
      ON (contact_civireport.id = email_civireport.contact_id AND
      email_civireport.is_primary = 1) WHERE participant_civireport.is_test = 0 AND contact_civireport.is_deleted = 0 ORDER BY participant_civireport.event_id, contact_civireport.sort_name ORDER BY contact_civireport.sort_name ASC LIMIT 0, 50 [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 'ORDER BY contact_civireport.sort_name ASC LIMIT 0, 50' at line 15]"]

      STEPS TO REPRODUCE:
      1. From the reports menu, select the "Event Participants (list)" report
      2. Under "Report Criteria" select the check box in the "group by" section for "Events"
      3. Ensure that sorting by "last name, first name" is set.
      4. Preview the report

      You will be presented with the error above.

      It also seems that it is possible to modify the report with the group by setting (that causes the error) and then you will be UNABLE to edit the report criteria to remove the group by setting thus rendering the report useless (it will always return an error).

        Attachments

          Activity

            People

            • Assignee:
              deepak Deepak Srivastava
              Reporter:
              gregoryheller Gregory Heller
            • Votes:
              2 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: