Incompatible with PostgreSQL

[nrosenquist]

Hi there, I'm getting the following front end error on a Postgres-backed Moodle when attempting to view a mod_game activity:

Debug info: ERROR: column "mdl_game_attempts.score" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...gameid=187 AND score > 0 GROUP BY userid ORDER BY score DESC
^
SELECT userid, MAX(score) as maxscore FROM mdl_game_attempts WHERE gameid=187 AND score > 0 GROUP BY userid ORDER BY score DESC
[array (
)]
Error code: dmlreadexception 

    line 486 of /lib/dml/moodle_database.php: dml_read_exception thrown
    line 329 of /lib/dml/pgsql_native_moodle_database.php: call to moodle_database->query_end()
    line 977 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end()
    line 359 of /mod/game/view.php: call to pgsql_native_moodle_database->get_records_sql()
    line 332 of /mod/game/view.php: call to game_highscore()

Troubleshooting on the command line, this does appear to be a discrepancy between the way MySQL and Postgres handle GROUP BY:

=> select userid, max(score) from mdl_game_attempts where gameid = 187 and score > 0 group by userid order by score desc;
ERROR:  column "mdl_game_attempts.score" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...meid = 187 and score > 0 group by userid order by score desc...

This discrepancy is explained more fully on this StackExchange page: https://stackoverflow.com/questions/1769361/postgresql-group-by-different-from-mysql

Furthermore it looks like Moodle has some helper functions in it's database API that might ease cross-compatibility here: https://docs.moodle.org/dev/Data_manipulation_API#Cross-DB_compatibility

Moodle version: 3.9.4 Postgres version: 11.8 mod_game version: 2020-08-19 (2020081901)

Thanks!

[nrosenquist]

Turns out fixing compatibility with postgres was very easy, just had to group by max(score) instead of just score. Pull request created.