Error when searching with text and "All"

[tibbakoi]

When searching using some text and choosing All fields, an invalid syntax type error is displayed. For example with this search I got the following debug info:

Debug info: ERROR: invalid input syntax for type bigint: "throwaway" CONTEXT: unnamed portal parameter $1 = '...' SELECT * FROM mdl_user WHERE id = $1 OR username LIKE $2 OR firstname LIKE $3 OR lastname LIKE $4 OR email LIKE $5 OR idnumber LIKE $6 ORDER BY lastname, firstname [array ( 0 => 'throwaway', 1 => '%throwaway%', 2 => '%throwaway%', 3 => '%throwaway%', 4 => '%throwaway%', 5 => '%throwaway%', )] Error code: dmlreadexception

Stack trace: line 494 of /lib/dml/moodle_database.php: dml_read_exception thrown line 293 of /lib/dml/moodle_read_slave_trait.php: call to moodle_database->query_end() line 341 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->read_slave_query_end() line 1027 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end() line 136 of /admin/tool/mergeusers/lib/mergeusersearch.php: call to pgsql_native_moodle_database->get_records_sql() line 165 of /admin/tool/mergeusers/index.php: call to MergeUserSearch->search_users()

I presume this is because one of the fields wants a bigint rather than string, as searching All with a number and searching First name with text is successful.

I'm on Moodle 4.1.13 (Build: 20240902), plugin version 2023040402

[jpahullo]

Thanks @tibbakoi for your feedback!

It reminds me another issue really similar, and it is related to how each database manages parameters on queries. I do not remember if it is still open.

For the stacktrace I can see you are using Postgres database too. So, maybe some specific parsing, or using $DB API should be addressed in this part of the search.

If you want to contribute a patch, it will be quicker to resolve.

Thanks a lot for the feedback!

Jordi Jordi

[leonstr]

This looks like a duplicate of #275. There's a proposed fix in leonstr/275-pgsqlsearch.

@tibbakoi I think you can just replace admin/tool/mergeusers/lib/mergeusersearch.php with the one from the above branch.

[jpahullo]

Hi @tibbakoi ,

Included on MOODLE_405_STABLE branch ONLY.

Closing as fixed.

Thanks a lot for your comments.

Jordi