Column 'moodle_customcert.templateid' is invalid. Not contained in either an aggregate function or the GROUP BY clause.

[ZXRoxas]

Execute scheduled task: Issue certificates task (mod_customcert\task\issue_certificates_task)
... started 19:11:25. Current memory use 29.3 MB.
Debugging increased temporarily due to faildelay of 86400
... used 1 dbqueries
... used 2.8524348735809 seconds
Scheduled task failed: Issue certificates task (mod_customcert\task\issue_certificates_task),Error reading from database (SQLState: 42000

Error Code: 8120

Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column 'moodle_customcert.templateid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

SELECT c.id, c.templateid, c.course, c.requiredtime, 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 moodle_customcert c
JOIN moodle_customcert_templates ct
ON c.templateid = ct.id
JOIN moodle_course co
ON c.course = co.id
JOIN moodle_course_categories cat
ON co.category = cat.id
LEFT JOIN moodle_customcert_issues ci
ON c.id = ci.customcertid WHERE (c.emailstudents = '1'
OR c.emailteachers = '1'
OR LEN(c.emailothers) >= 3) AND co.visible = 1 AND cat.visible = 1 AND (co.enddate > '1697681485' OR (co.enddate = 0 AND ci.timecreated > '1697681485')) GROUP BY c.id, ct.id, ct.name, ct.contextid, co.id, co.fullname, co.shortname
[array (
0 => 1,
1 => 1,
2 => 1697681485,
3 => 1697681485,
)])
Debug info:
SQLState: 42000

Error Code: 8120

Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column 'moodle_customcert.templateid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

SELECT c.id, c.templateid, c.course, c.requiredtime, 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 moodle_customcert c
JOIN moodle_customcert_templates ct
ON c.templateid = ct.id
JOIN moodle_course co
ON c.course = co.id
JOIN moodle_course_categories cat
ON co.category = cat.id
LEFT JOIN moodle_customcert_issues ci
ON c.id = ci.customcertid WHERE (c.emailstudents = '1'
OR c.emailteachers = '1'
OR LEN(c.emailothers) >= 3) AND co.visible = 1 AND cat.visible = 1 AND (co.enddate > '1697681485' OR (co.enddate = 0 AND ci.timecreated > '1697681485')) GROUP BY c.id, ct.id, ct.name, ct.contextid, co.id, co.fullname, co.shortname
[array (
0 => 1,
1 => 1,
2 => 1697681485,
3 => 1697681485,
)]
Backtrace:

• line 331 of \lib\dml\sqlsrv_native_moodle_database.php: call to moodle_database->query_end()
• line 438 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->query_end()
• line 909 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->do_query()
• line 985 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->get_recordset_sql()
• line 94 of \mod\customcert\classes\task\issue_certificates_task.php: call to sqlsrv_native_moodle_database->get_records_sql()
• line 410 of \lib\classes\cron.php: call to mod_customcert\task\issue_certificates_task->execute()
• line 208 of \lib\classes\cron.php: call to core\cron::run_inner_scheduled_task()
• line 125 of \lib\classes\cron.php: call to core\cron::run_scheduled_tasks()
• line 186 of \admin\cli\cron.php: call to core\cron::run_main_process()

[ZXRoxas]

Plugin Version: 4.4.3 (2024042207). Moodle Version: 4.4.3+ (Build: 20240920).

[eiconsult]

Plugin Version: 4.4.3 (2024042207). Moodle Version: 4.4.3+ (Build: 20240920).

I was able to resolve this by doing so:

MSSQL is a bit strict with Group By functions and requires columns be listed in the group by statement if it is referenced in the select statement. In this case, the c.* retrieves all columns in the select statement.

Potential "quick" workaround

line 91: \mod\customcert\classes\task\issue_certificates_task.php

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

to:
$sql .= " GROUP BY c.id, ct.id, ct.name, ct.contextid, co.id, co.fullname, co.shortname, c.course,c.templateid,c.intro,c.introformat,c.requiredtime, c.name, ,c.verifyany,c.emailstudents,c.emailteachers,c.emailothers,c.protection,c.timecreated,c.timemodified,c.deliveryoption,c.language";

[mdjnelson]

@eiconsult can you try with the latest version which includes 4425055? I dont have access to an easily accessible SQL database without a bunch of docker configurations? If you can provide a diff, or even a PR that would be great.

[eiconsult]

Ah. You've got it. I see 2024042206 replaced with 2024042207. I'll drop a note when confirmed

…

-Eseosa

________________________________ From: Mark Nelson ***@***.***> Sent: Friday, October 18, 2024 11:56:25 AM To: mdjnelson/moodle-mod_customcert ***@***.***> Cc: Eseosa Iyare ***@***.***>; Mention ***@***.***> Subject: Re: [mdjnelson/moodle-mod_customcert] Column 'moodle_customcert.templateid' is invalid. Not contained in either an aggregate function or the GROUP BY clause. (Issue #649) @eiconsult<https://github.com/eiconsult> can you try with the latest version which includes 4425055<4425055>? I dont have access to an easily accessible SQL database without a bunch of docker configurations? If you can provide a diff, or even a PR that would be great. — Reply to this email directly, view it on GitHub<#649 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/BMHPYGRCSTJEHSHT3JZF3BDZ4EVSTAVCNFSM6AAAAABQGFS7LKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDIMRSG44DGNJTGM>. You are receiving this because you were mentioned.Message ID: ***@***.***>

[mdjnelson]

Hey @eiconsult, any update? :) Would like to fix this if it wasn't actually fixed by the patch I listed.

[eiconsult]

:) client owns the MSSQL service. It will take a few to get this situated. Thank you -Eseosa

…

________________________________ From: Mark Nelson ***@***.***> Sent: Monday, October 21, 2024 10:20:38 AM To: mdjnelson/moodle-mod_customcert ***@***.***> Cc: Eseosa Iyare ***@***.***>; Mention ***@***.***> Subject: Re: [mdjnelson/moodle-mod_customcert] Column 'moodle_customcert.templateid' is invalid. Not contained in either an aggregate function or the GROUP BY clause. (Issue #649) Hey @eiconsult<https://github.com/eiconsult>, any update? :) Would like to fix this if it wasn't actually fixed by the patch I listed. — Reply to this email directly, view it on GitHub<#649 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/BMHPYGX4ETH2W3ETFDABBP3Z4UETNAVCNFSM6AAAAABQGFS7LKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDIMRWHAZDOOBWGU>. You are receiving this because you were mentioned.Message ID: ***@***.***>

[mdjnelson]

Some time on the weekend ill get Moodle docker setup and run it against MSSQL and Oracle. I've just been too lazy to do that. 😅

[rlowry]

Unfortunately the Oracle patch does not fix this for SQL Server. With v4.4.3 installed I still get the following error:

`Execute scheduled task: Issue certificates task (mod_customcert\task\issue_certificates_task)
... started 00:48:54. Current memory use 38.0 MB.
Debugging increased temporarily due to faildelay of 86400
... used 1 dbqueries
... used 0.41620993614197 seconds
Scheduled task failed: Issue certificates task (mod_customcert\task\issue_certificates_task),Error reading from database (SQLState: 42000

Error Code: 8120

Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column 'mdl_customcert.templateid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

SELECT c.id, c.templateid, c.course, c.requiredtime, 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 mdl_customcert c
JOIN mdl_customcert_templates ct
ON c.templateid = ct.id
JOIN mdl_course co
ON c.course = co.id
JOIN mdl_course_categories cat
ON co.category = cat.id
LEFT JOIN mdl_customcert_issues ci
ON c.id = ci.customcertid WHERE (c.emailstudents = '1'
OR c.emailteachers = '1'
OR LEN(c.emailothers) >= 3) AND co.visible = 1 AND cat.visible = 1 AND (co.enddate > '1699231734' OR (co.enddate = 0 AND ci.timecreated > '1699231734')) GROUP BY c.id, ct.id, ct.name, ct.contextid, co.id, co.fullname, co.shortname
[array (
0 => 1,
1 => 1,
2 => 1699231734,
3 => 1699231734,
)])
Debug info:
SQLState: 42000

Error Code: 8120

Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column 'mdl_customcert.templateid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

SELECT c.id, c.templateid, c.course, c.requiredtime, 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 mdl_customcert c
JOIN mdl_customcert_templates ct
ON c.templateid = ct.id
JOIN mdl_course co
ON c.course = co.id
JOIN mdl_course_categories cat
ON co.category = cat.id
LEFT JOIN mdl_customcert_issues ci
ON c.id = ci.customcertid WHERE (c.emailstudents = '1'
OR c.emailteachers = '1'
OR LEN(c.emailothers) >= 3) AND co.visible = 1 AND cat.visible = 1 AND (co.enddate > '1699231734' OR (co.enddate = 0 AND ci.timecreated > '1699231734')) GROUP BY c.id, ct.id, ct.name, ct.contextid, co.id, co.fullname, co.shortname
[array (
0 => 1,
1 => 1,
2 => 1699231734,
3 => 1699231734,
)]
Backtrace:

• line 331 of \lib\dml\sqlsrv_native_moodle_database.php: call to moodle_database->query_end()
• line 438 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->query_end()
• line 909 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->do_query()
• line 985 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->get_recordset_sql()
• line 94 of \mod\customcert\classes\task\issue_certificates_task.php: call to sqlsrv_native_moodle_database->get_records_sql()
• line 410 of \lib\classes\cron.php: call to mod_customcert\task\issue_certificates_task->execute()
• line 208 of \lib\classes\cron.php: call to core\cron::run_inner_scheduled_task()
• line 125 of \lib\classes\cron.php: call to core\cron::run_scheduled_tasks()
• line 186 of \admin\cli\cron.php: call to core\cron::run_main_process()`

[arborrow]

Mark - FYI, I am also experiencing this on the JRS Moodle server. Peace - Anthony

[arborrow]

Mark - From what I can tell the latest update does in fact resolve this issue.

[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.