Database error when attempting to load view.php

[freeradicalx]

Hi there, in my Moodle I just fixed what appears to be a code issue in version 2020081901 of this plugin. When attempting to view a game assignment I get the following error output:

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=69 AND score > 0 GROUP BY userid ORDER BY score DESC
^
SELECT userid, MAX(score) as maxscore FROM mdl_game_attempts WHERE gameid=69 AND score > 0 GROUP BY userid ORDER BY score DESC
[array (
)]
Error code: dmlreadexception

Stack trace: 

    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 920 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()

By changing line 356 of view.php from: " GROUP BY userid". To: " GROUP BY userid,score".

I was able to resolve the error. If it matters, my database type is postgres and Moodle version is 2020061501.00 / 3.9.1 (Build: 20200713). Thanks!

[bdaloukas]

I don't aggree because the query have to return one row per userid. Try ORDER BY max(score) DESC I don't have postgres to test it, so do the test please and tell me if it works.

[freeradicalx]

Agreed, that is probably the actual correct solution! I've changed the query to your suggestion and it is working fine.

[bdaloukas]

SELECT userid, MAX(score) as maxscore". " FROM {$CFG->prefix}game_attempts ". " WHERE gameid={$game->id} AND score > 0". " GROUP BY userid". " ORDER BY max(score) DESC";