DB error in Mail Clickthroughs bar chart display
When viewing the Mail Clickthroughs report, the table view works but the bar chart view does not.
- Log into demo & view https://dmaster.demo.civicrm.org/civicrm/report/instance/35
- Select "Bar chart" display
- Click View
Error will read "Sorry, due to an error, we are unable to fulfill your request at the moment. You may want to contact your administrator or service provider with more details about what action you were performing when this occurred."
The SQL which fails is:
SELECT contact_civireport.id as civicrm_contact_id, contact_civireport.sort_name as civicrm_contact_sort_name, mailing_civireport.name as civicrm_mailing_mailing_name, mailing_civireport.name as civicrm_mailing_mailing_name_alias, email_civireport.email as civicrm_email_email, mailing_trackable_url_civireport.url as civicrm_mailing_trackable_url_url, COUNT(.id) as civicrm_mailing_click_count FROM civicrm_contact contact_civireport INNER JOIN civicrm_mailing_event_queue ON civicrm_mailing_event_queue.contact_id = contact_civireport.id LEFT JOIN civicrm_email email_civireport ON civicrm_mailing_event_queue.email_id = email_civireport.id INNER JOIN civicrm_mailing_event_trackable_url_open mailing_event_trackable_url_open_civireport ON mailing_event_trackable_url_open_civireport.event_queue_id = civicrm_mailing_event_queue.id INNER JOIN civicrm_mailing_trackable_url mailing_trackable_url_civireport ON mailing_event_trackable_url_open_civireport.trackable_url_id = mailing_trackable_url_civireport.id INNER JOIN civicrm_mailing_job ON civicrm_mailing_event_queue.job_id = civicrm_mailing_job.id INNER JOIN civicrm_mailing mailing_civireport ON civicrm_mailing_job.mailing_id = mailing_civireport.id AND civicrm_mailing_job.is_test = 0 WHERE ( 1 ) AND mailing_civireport.sms_provider_id IS NULL GROUP BY mailing_civireport.id
The error message is:
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') as civicrm_mailing_click_count
Looks like the culprit is generating
COUNT(.id) as civicrm_mailing_click_count
The report should show as a beautiful bar graph.
(I've never seen this bar chart, I'm reporting the issue on behalf of a community user.)
- CiviCRM: 5.19.3, verified on https://dmaster.demo.civicrm.org/
- PHP: 7.2
- CMS: Drupal 7
- Database: 10.1.43-MariaDB-0ubuntu0.18.04.1