ORA-00979: not a GROUP BY

[kdima71]

Moodle 4.1.13+
Customcert Version 2022112813 Release 4.1.6

Scheduled task failed: Issue certificates task (mod_customcert\task\issue_certificates_task
ORA-00979: not a GROUP BY expression SELECT c., ct.id as templateid, ct.name as templatename, ct.contextid, co.id as courseid, co.fullname as coursefullname, co.shortname as courseshortname FROM m_customcert c JOIN m_customcert_templates ct ON c.templateid = ct.id JOIN m_course co ON c.course = co.id JOIN m_course_categories cat ON co.category = cat.id LEFT JOIN m_customcert_issues ci ON c.id = ci.customcertid WHERE (c.emailstudents = :o_emailstudents OR c.emailteachers = :o_emailteachers OR LENGTH(c.emailothers) >= 3) AND co.visible = 1 AND cat.visible = 1 AND (co.enddate > :o_enddate OR (co.enddate = 0 AND ci.timecreated > :o_enddate2)) GROUP BY c.id, ct.id, ct.name, ct.contextid, co.id, co.fullname, co.shortname [array ( 'o_emailstudents' => 1, 'o_emailteachers' => 1, 'o_enddate' => 1696624757, 'o_enddate2' => 1696624757, )]) Debug info: ORA-00979: not a GROUP BY expression SELECT c., ct.id as templateid, ct.name as templatename, ct.contextid, co.id as courseid, co.fullname as coursefullname, co.shortname as courseshortname FROM m_customcert c JOIN m_customcert_templates ct ON c.templateid = ct.id JOIN m_course co ON c.course = co.id JOIN m_course_categories cat ON co.category = cat.id LEFT JOIN m_customcert_issues ci ON c.id = ci.customcertid WHERE (c.emailstudents = :o_emailstudents OR c.emailteachers = :o_emailteachers OR LENGTH(c.emailothers) >= 3) AND co.visible = 1 AND cat.visible = 1 AND (co.enddate > :o_enddate OR (co.enddate = 0 AND ci.timecreated > :o_enddate2)) GROUP BY c.id, ct.id, ct.name, ct.contextid, co.id, co.fullname, co.shortname [array ( 'o_emailstudents' => 1, 'o_emailteachers' => 1, 'o_enddate' => 1696624757, 'o_enddate2' => 1696624757, )] Backtrace: * line 277 of \lib\dml\oci_native_moodle_database.php: call to moodle_database->query_end() * line 1169 of \lib\dml\oci_native_moodle_database.php: call to oci_native_moodle_database->query_end() * line 94 of \mod\customcert\classes\task\issue_certificates_task.php: call to oci_native_moodle_database->get_records_sql() * line 263 of \lib\cronlib.php: call to mod_customcert\task\issue_certificates_task->execute() * line 120 of \lib\cronlib.php: call to cron_run_inner_scheduled_task() * line 73 of \lib\cronlib.php: call to cron_run_scheduled_tasks() * line 81 of \admin\cron.php: call to cron_run()

[mdjnelson]

Can you tell me if the following SQL helps with this?

SELECT 
    c.id, 
    c.templateid, 
    c.course, 
    c.emailstudents, 
    c.emailteachers, 
    c.emailothers,
    ct.id AS templateid, 
    ct.name AS templatename, 
    ct.contextid, 
    co.id AS courseid, 
    co.fullname AS coursefullname, 
    co.shortname AS courseshortname 
FROM 
    m_customcert c
JOIN 
    m_customcert_templates ct ON c.templateid = ct.id
JOIN 
    m_course co ON c.course = co.id
JOIN 
    m_course_categories cat ON co.category = cat.id
LEFT JOIN 
    m_customcert_issues ci ON c.id = ci.customcertid
WHERE 
    (c.emailstudents = :o_emailstudents 
    OR c.emailteachers = :o_emailteachers 
    OR LENGTH(c.emailothers) >= 3)
    AND co.visible = 1
    AND cat.visible = 1
    AND (co.enddate > :o_enddate 
        OR (co.enddate = 0 AND ci.timecreated > :o_enddate2))
GROUP BY 
    c.id, 
    c.templateid, 
    c.course, 
    c.emailstudents, 
    c.emailteachers, 
    c.emailothers, 
    ct.id, 
    ct.name, 
    ct.contextid, 
    co.id, 
    co.fullname, 
    co.shortname

It's basically just changing the fields that are selected.

[kdima71]

GROUP BY is used only when some group function in SELECT is used, for example COUNT, SUM, etc.

Therefore, it is necessary to remove the GROUP BY from the SQL query.

[mdjnelson]

Would you like to do a PR I can have a look at and contribute you to the code change?

[kdima71]

Unfortunately, I do not have the opportunity to participate.

At the moment, I have commented out this line in the «issue_certificates_task.php» class code as a temporary solution (workaround) and am awaiting the release of an official fix.

// $sql .= " GROUP BY c.id, ct.id, ct.name, ct.contextid, co.id, co.fullname, co.shortname";

[mdjnelson]

Pushed a fix to all branches, this should be solved now. :)

[kdima71]

Thank you very much!
Where can I download the fix?

[mdjnelson]

I've released a new version on the plugins DB. :)

[mdjnelson]

This is still broken. On SQL server as well, see #649. Setting up Oracle and MSSQL docker images to test this against.

[kdima71]

Most likely, your task in this sql query is to arrange (sort) the data, instead of aggregating, so use ORDER BY instead of GROUP BY.