Fatal db error in Activity Summary report for some Sorting fields (with ONLY_FULL_GROUP_BY)
A fatal db error occurs in Activity Summary report for some Sorting fields. This occurs only with ONLY_FULL_GROUP_BY set in sql_mode.
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.
- Click "Refresh results".
Expected result: Report runs.
Actual result:
DB Error: unknown error
Database Error Code: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'dmasterciv_t1nir.contact_civireport.sort_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by, 1055
[debug_info] => INSERT INTO civicrm_activity_temp_a10856f163bac9e54ba4f5f5538d1cbf (civicrm_activity_duration_total)
SELECT SUM(activity_civireport.duration) as civicrm_activity_duration_total
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_contact contact_civireport
ON target_activity.contact_id = contact_civireport.id
WHERE
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=1055 ** Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'dmasterciv_t1nir.contact_civireport.sort_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by]
The error also happens for various other combinations of Columns, Grouping & Sorting.
Occurs in current dmaster with MySQL 5.7.23, when ONLY_FULL_GROUP_BY set in sql_mode.