CRM_Core_Error: DB Error: unknown error: Expression 3 of SELECT list is not in GROUP BY clause and contains nonaggregated column
Overview
The following error happens when trying to generate a Report from a Dashboard after selecting multiple choice field:
CRM_Core_Error: DB Error: unknown error: Expression 3 of SELECT list is not in GROUP BY clause and contains nonaggregated column
Reproduction steps
- Having Contact with custom group field set with the field type: Multiple choice option of type Integer Drop-down select list (Req=Yes)
- Go to the existing Dashboard page at: /civicrm/report/instance/%
- Select Multiple choice integer field (mentioned before)
- Got an error "Fatal error: DB error".
Current behaviour
The following error happens on clicking 'View results' on Dashboard page.
[message] => DB Error: unknown error
[function] => exceptionHandler
[class] => CRM_Core_Error
[type] => ::
[args] => Array
(
[0] => stdClass Object
(
[__CLASS__] => DB_Error
[error_message_prefix] =>
[mode] => 16
[level] => 1024
[code] => -1
[message] => DB Error: unknown error
[userinfo] => SELECT SQL_CALC_FOUND_ROWS civicrm_contact.id as civicrm_contact_civicrm_contact_contact_id , civicrm_contact.display_name as civicrm_contact_civicrm_contact_display_name , civicrm_value_custom_77.custom_455 as civicrm_value_custom_77_custom_455 FROM civicrm_contact civicrm_contact
LEFT JOIN civicrm_value_custom_77 civicrm_value_custom_77 ON civicrm_value_custom_77.entity_id = civicrm_contact.id WHERE ( ( civicrm_contact.contact_type IN ( 'Individual') ) ) AND ( ( civicrm_contact.contact_sub_type LIKE '%Foo%' ) OR ( civicrm_contact.contact_sub_type LIKE '%Bar%' ) ) AND ( civicrm_value_custom_77.custom_455 IS NOT NULL ) AND ( ( civicrm_contact.contact_type IN ( 'Individual') ) ) AND ( ( civicrm_contact.contact_sub_type LIKE '%Foo%' ) OR ( civicrm_contact.contact_sub_type LIKE '%Bar%' ) ) AND ( civicrm_value_custom_77.custom_455 IS NOT NULL ) GROUP BY civicrm_contact.id LIMIT 0, 50 [nativecode=1055 ** Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db.civicrm_value_custom_77.custom_455' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by]
...
modules/civicrm-ext/nz.co.fuzion.extendedreport/CRM/Extendedreport/Form/Report/ExtendedReport.php
[line] => 1737
[function] => buildRows
[class] => CRM_Report_Form
Expected behaviour
What should happen.
Environment information
- Browser: 112.0.5615.49
- CiviCRM: 5.39.1
- PHP: 7.3.33-8
- CMS: 8.9.20
- Database: MySQL 5.7.40
- Web Server: Apache/2.4.54 (Ubuntu)
Comments
Anything else you would like the reviewer to note.