Manage User Connections > Microsoft 365 username search fails in PostgreSQL environments

[thebenkahn]

Hello, using the Microsoft 365 username search field on the Microsoft 365 Integration > Advanced > Manage User Connections page is presenting me with a DML error.

Moodle version 3.11.7 (Build: 20220509) DB: postgres (11.13) MS365 plugins : local_o365 3.11.4

Based on the debug it looks like a column alias 'o365username' is used in the HAVING clause. This works in MariaDB/MySQL. Apparently not allowed in PGSQL. https://stackoverflow.com/questions/7511064/postgresql-aliases-column-and-having

In a test query this can be resolved by replacing the alias with the full expression:

HAVING COALESCE(aotok.oidcusername, o365match.aadupn, objects.o365name)

Stack trace

Debug info: ERROR: column "o365username" does not exist
LINE 6: ... WHERE u.deleted = 0 AND u.username != $2 HAVING o365userna...
^
HINT: Perhaps you meant to reference the column "aotok.oidcusername".
SELECT count(1) from (SELECT u.id AS userid,u.firstname AS userfirstname,u.firstnamephonetic AS userfirstnamephonetic,u.lastname AS userlastname,u.lastnamephonetic AS userlastnamephonetic,u.middlename AS usermiddlename,u.alternatename AS useralternatename,u.auth AS userauth,aotok.oidcusername AS toko365username,o365match.aadupn AS matchedo365username,o365match.uselogin AS matcheduselogin,objects.o365name AS objectso365name,COALESCE(aotok.oidcusername, o365match.aadupn, objects.o365name) AS o365username
FROM mdl_user u
LEFT JOIN mdl_auth_oidc_token aotok ON aotok.userid = u.id
LEFT JOIN mdl_local_o365_connections o365match ON o365match.muserid = u.id
LEFT JOIN mdl_local_o365_objects objects ON objects.moodleid = u.id AND type = $1
WHERE u.deleted = 0 AND u.username != $2 HAVING o365username ILIKE $3 ESCAPE '\') a
[array (
0 => 'user',
1 => 'guest',
2 => '%smith%',
)]
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 977 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end()
line 1671 of /lib/dml/moodle_database.php: call to pgsql_native_moodle_database->get_records_sql()
line 1744 of /lib/dml/moodle_database.php: call to moodle_database->get_record_sql()
line 1954 of /lib/dml/moodle_database.php: call to moodle_database->get_field_sql()
line 149 of /local/o365/classes/feature/userconnections/table.php: call to moodle_database->count_records_sql()
line 2091 of /lib/tablelib.php: call to local_o365\feature\userconnections\table->query_db()
line 1965 of /local/o365/classes/page/acp.php: call to table_sql->out()
line 148 of /local/o365/classes/page/base.php: call to local_o365\page\acp->mode_userconnections()
line 35 of /local/o365/acp.php: call to local_o365\page\base->run()

[weilai-irl]

Hi @thebenkahn,

I acknowledge the issue. This was reported at #2083 a few days ago, and seems only affecting PostgreSQL database. It's likely to do with how PostgreSQL implements COALESCE function.

We have been mainly using MySQL in our environments, so haven't seen this issue ourselves. I'll try to setup an site using PostgreSQL database to reproduce the issue, and look for a solution.

Regards, Lai

[PetriAsi]

Subscribing to this, as this affecting us too.

[weilai-irl]

Hi all,

The fix to the issue has been included in the release from today. Please upgrade your plugins to the latest version to see the fix.

I'm going to close this issue.

Regards, Lai