completion settings cause database read error

[thebenkahn]

Hello,
I have found that using the completion settings for mod ouwiki can cause a DML read error due to the formatting of one of the queries used in the plugin.

Version tested: 3_11 branch (2020050501)
Moodle version: 3.11.11+ (Build: 20221216)
Replication steps:
In an empty course, add a new OU Wiki activity
Under Activity Completion, set "Show activity as complete when conditions are met" and tick the "User must create new pages" box
Save the OU Wiki activity and view the course page

Error with developer debugging

Error reading from database

[More information about this error](https://docs.moodle.org/311/en/error/moodle/dmlreadexception)

Debug info: execute command denied to user 'moodledude'@'localhost' for routine 'evilcorp.MIN'
SELECT COUNT(1)
FROM mdl_ouwiki_versions v
INNER JOIN mdl_ouwiki_pages p ON p.id = v.pageid
INNER JOIN mdl_ouwiki_subwikis s ON s.id = p.subwikiid
WHERE v.userid = ? AND v.deletedat IS NULL AND s.wikiid = ? AND (SELECT MIN (id)
FROM mdl_ouwiki_versions
WHERE pageid = p.id AND deletedat IS NULL) = v.id
[array (
0 => 2,
1 => '1',
)]
Error code: dmlreadexception×Dismiss this notification
Stack trace:
line 486 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 1273 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->query_end()
line 1671 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->get_records_sql()
line 1744 of /lib/dml/moodle_database.php: call to moodle_database->get_record_sql()
line 4061 of /mod/ouwiki/locallib.php: call to moodle_database->get_field_sql()
line 43 of /mod/ouwiki/classes/completion/custom_completion.php: call to ouwiki_get_completion_state_lib()
line 1177 of /lib/completionlib.php: call to mod_ouwiki\completion\custom_completion->get_state()
line 1047 of /lib/completionlib.php: call to completion_info->get_other_cm_completion_data()
line 70 of /completion/classes/cm_completion_details.php: call to completion_info->get_data()
line 275 of /completion/classes/cm_completion_details.php: call to core_completion\cm_completion_details->__construct()
line 947 of /course/renderer.php: call to core_completion\cm_completion_details::get_instance()
line 836 of /course/renderer.php: call to core_course_renderer->course_section_cm()
line 1051 of /course/renderer.php: call to core_course_renderer->course_section_cm_list_item()
line 885 of /course/format/renderer.php: call to core_course_renderer->course_section_cm_list()
line 57 of /course/format/topics/format.php: call to format_section_renderer_base->print_multiple_section_page()
line 278 of /course/view.php: call to require()

It looks to me that there is an unneeded space in the "SELECT MIN (id)" portion of the SQL that is incompatible with default MySQL/MariaDB, unless the "IGNORE_SPACE" variable is enabled.

I can replicate the error running the query directly in the MySQL console.

As a note, I tried enabling IGNORE_SPACE on my MySQL server and that allowed me to run the query directly from the MySQL console. However I still get the same error in Moodle, though I am not sure why. In any case I think the space is not needed and enabling the white space variable could cause other issues with reserved words.

problem setting:

full page error

ignore space off, get the error

ignore space on, error resolved