Fatal db error in Activity Summary report when Sorting uses Section Header (without ONLY_FULL_GROUP_BY)
A fatal db error occurs in Activity Summary report when sorting by a field that has not been selected in Columns, if Section Header is checked. This occurs with or without ONLY_FULL_GROUP_BY.
Steps to replicate:
- From the standard pre-defined Contact Reports, go to Activity Summary.
- Leave Columns as default, with Contact Name not checked.
- Leave Grouping as default (Activity Type + Activity Status).
- In Sorting, select Contact Name and check "Section Header / Group By".
- Click "Refresh results".
Expected result: Report runs.
DB Error: value count on row Database Error Code: Column count doesn't match value count at row 1, 1136 [debug_info] => INSERT INTO civicrm_activity_temp_319632dc20cdeda8063b99ed6caaec62 ( civicrm_contact_id,civicrm_activity_activity_type_id,civicrm_activity_status_id,civicrm_activity_duration,civicrm_activity_id_count ) SELECT contact_civireport.id as civicrm_contact_id, activity_civireport.activity_type_id as civicrm_activity_activity_type_id, activity_civireport.status_id as civicrm_activity_status_id, activity_civireport.duration as civicrm_activity_duration, COUNT(DISTINCT(activity_civireport.id)) as civicrm_activity_id_count , contact_civireport.sort_name as civicrm_contact_sort_name FROM civicrm_activity activity_civireport LEFT JOIN civicrm_activity_contact target_activity ON activity_civireport.id = target_activity.activity_id AND target_activity.record_type_id = 3 LEFT JOIN civicrm_activity_contact assignment_activity ON activity_civireport.id = assignment_activity.activity_id AND assignment_activity.record_type_id = 1 LEFT JOIN civicrm_activity_contact source_activity ON activity_civireport.id = source_activity.activity_id AND source_activity.record_type_id = 2 LEFT JOIN civicrm_contact contact_civireport ON target_activity.contact_id = contact_civireport.id LEFT JOIN civicrm_contact civicrm_contact_assignee ON assignment_activity.contact_id = civicrm_contact_assignee.id LEFT JOIN civicrm_contact civicrm_contact_source ON source_activity.contact_id = civicrm_contact_source.id LEFT JOIN civicrm_option_value ON ( activity_civireport.activity_type_id = civicrm_option_value.value ) LEFT JOIN civicrm_option_group ON civicrm_option_group.id = civicrm_option_value.option_group_id LEFT JOIN civicrm_case_activity ON civicrm_case_activity.activity_id = activity_civireport.id LEFT JOIN civicrm_case ON civicrm_case_activity.case_id = civicrm_case.id LEFT JOIN civicrm_case_contact ON civicrm_case_contact.case_id = civicrm_case.id WHERE civicrm_option_group.name = "activity_type" AND activity_civireport.is_test = 0 AND activity_civireport.is_deleted = 0 AND activity_civireport.is_current_revision = 1 GROUP BY activity_civireport.activity_type_id, activity_civireport.status_id ORDER BY contact_civireport.sort_name ASC LIMIT 0, 50 [nativecode=1136 ** Column count doesn't match value count at row 1]
Occurs in Civi 5.5.1 with MariaDB 10.1.34 or 10.1.37; also in current dmaster with MySQL 5.7.23 .
Note that dev/core/issues/428 reported an error in similar circumstances for the Activity Detail report but the error details are different and the error occurs at a different stage: here in INSERT INTO civicrm_activity_temp_XXX, there in a SELECT query.