Rejection report crashes with sql ONLY_FULL_GROUP_BY

The current sql query for the Validation Rejection Report is incompatible with ONLY_FULL_GROUP_BY on the sql server, resulting in a crash when you try to view the report

Steps to reproduce:

1. Enable ONLY_FULL_GROUP_BY mode on the SQL server ( enabled by default since MySQL 5.7.5 // SET sql_mode=(SELECT CONCAT(@@sql_mode,',ONLY_FULL_GROUP_BY')); )

2. Add some contributions to a Gift Aid batch where some are invalid (e.g. missing address on the Gift Aid declaration)

3. Validate the batch for submission, then on the validation error page, click to view the **Rejection report **

1. DB error crash

(Reproducible on D7 / 5.49.alpha1 / Mysql 5.7.37 using buildkit)

Technical details

The DB error is the result of an issue with the form of the current SQL query for the report.

The query GROUP(s) BY the civicrm_contact.id, but then also SELECTs field values from the civicrm_gift_aid_rejected_contributions table, assuming these are unique for each contact. With ONLY_FULL_GROUP_BY the sql server is complaining that this assumption is incorrect. I think it's right — there may be multiple records with different rejection_reasons the Rejected Contributions table for each contact.

Fix

This is best fixable by grouping by the civicrm_gift_aid_rejected_contributions.id . I've just updated my previously whackamole MR to reflect this !6 (merged)

For users without ONLY_FULL_GROUP_BY, this will yield multiple rows in a rejection report where there are multiple errors for a given contact (previously it would have just shown 1 row with a randomly-selected value for the rejection reason - arguably also a bug?).

NB: none of this bears on the all-important submission report that gets sent to HMRC, which is left untouched.