DB error in Mail Clickthroughs bar chart display
Overview
When viewing the Mail Clickthroughs report, the table view works but the bar chart view does not.
Reproduction steps
- Log into demo & view https://dmaster.demo.civicrm.org/civicrm/report/instance/35
- Select "Bar chart" display
- Click View
Current behaviour
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
Expected behaviour
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.)
Environment information
- 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