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.
Actual result:
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.