SQL error during update to 2024022200

[abias]

During the upgrade of an installation of local_recompletion 2023112600 to 2024022200 on a test machine running Moodle 4.3 / PHP 8.2 / PGSQL I ran into a fatal SQL error within the Moodle upgrade wizard saying

Error writing to database

Debug info: ERROR: column "sst.course" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2: SELECT userid, scormid, attempt, course FROM m_local...
^
INSERT INTO m_local_recompletion_sa (userid, scormid, attempt, courseid)
SELECT userid, scormid, attempt, course FROM m_local_recompletion_sst sst GROUP BY userid,scormid,attempt
[array (
)]
Error code: dmlwriteexception Stack trace:

    line 497 of /lib/dml/moodle_database.php: dml_write_exception thrown
    line 293 of /lib/dml/moodle_read_slave_trait.php: call to moodle_database->query_end()
    line 358 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->read_slave_query_end()
    line 863 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end()
    line 1049 of /local/recompletion/db/upgrade.php: call to pgsql_native_moodle_database->execute()
    line 803 of /lib/upgradelib.php: call to xmldb_local_recompletion_upgrade()
    line 1983 of /lib/upgradelib.php: call to upgrade_plugins()
    line 726 of /admin/index.php: call to upgrade_noncore()

The culprit sql statement seems to be on https://github.com/danmarsden/moodle-local_recompletion/blob/MOODLE_403_STABLE/db/upgrade.php#L1047-L1049

I did not investigate the reasons for this error as I was working just on a test machine with another goal, but I report this here anyway as the upgrade step in local_recompletion might be built in a fragile or non-PGSQL-compatible way.

Cheers, Alex

[danmarsden]

Thanks Alex - we'll get that sorted - I missed that the upgrade sql there was quite different to the one in mod/scorm that does a similar thing.