Scheduled Reminders generates faulty SQL

Question

CiviCRM 4.7.27 Usually, our CiviCRM guru handles these, but I figured I would ask a wider audience.

When choosing date instead of an interval, the scheduled reminder fails with a DB syntax error. The error is in the " DATE_SUB(, INTERVAL hour))" part of the SQL.

The first argument to the function is missing and the time component for the interval is missing: (see below in bold). I would imagine this is a bug.. The SQL from the logfile:

Here's the SQL that is created by CiviCRM and fails:

SELECT e.contact_id as contact_id, e.id as entity_id, "civicrm_membership" as entity_table, 3 as action_schedule_id, MAX as latest_log_time FROM civicrm_membership e INNER JOIN civicrm_contact c ON c.id = e.contact_id AND c.is_deleted = 0 AND c.is_deceased = 0 INNER JOIN civicrm_action_log reminder ON reminder.contact_id = e.contact_id AND reminder.entity_id = e.id AND reminder.entity_table = 'civicrm_membership' AND reminder.action_schedule_id = 3 WHERE (e.membership_type_id IN ("21")) AND (( e.is_override IS NULL OR e.is_override = 0 )) AND (e.status_id IN (5, 2, 9, 10, 4, 8)) AND ("20171108110009" <= DATE_SUB(, INTERVAL hour)) GROUP BY reminder.contact_id, reminder.entity_id, reminder.entity_table HAVING (TIMESTAMPDIFF) >= TIMESTAMPDIFF))

NOTE: There are missing parameters in the DATE_SUB function, the SQL fails and the scheduled reminder does not go out.

Answer 1

My friendly neighborhood Civi expert solved this problem. The issue is with the "repeat" option in the scheduled reminder form.

If you specify, when to repeat without the "Until" piece, it generates faulty SQL. Our expert modified the form so you can't leave that value blank if you click the repeat radio box.