Unit test failure with MySQL

[jwalits]

We're coming across this unit test failure in Moodle 3.9.12+ when running on MySQL/MariaDB servers. I have been able to replicate this error on a vanilla moodle installation as well

There was 1 error:

1) core_calendar_container_testcase::test_delete_module_delete_events
dml_write_exception: Error writing to database (You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'l3
      WHERE l3.level2id IN (SELECT l2.id
                              FROM p' at line 1
DELETE FROM phpu_openstudio_level3 l3
      WHERE l3.level2id IN (SELECT l2.id
                              FROM phpu_openstudio_level2 l2
                              JOIN phpu_openstudio_level1 l1 ON l1.id = l2.level1id AND l1.openstudioid = ?)

[array (
  0 => '471000',
)])

/var/www/navitas-sae-au/lib/dml/moodle_database.php:497
/var/www/navitas-sae-au/lib/dml/mysqli_native_moodle_database.php:1167
/var/www/navitas-sae-au/mod/openstudio/lib.php:213
/var/www/navitas-sae-au/course/lib.php:1054
/var/www/navitas-sae-au/calendar/tests/container_test.php:454
/var/www/navitas-sae-au/lib/phpunit/classes/advanced_testcase.php:80
phpvfscomposer:///var/www/navitas-sae-au/vendor/phpunit/phpunit/phpunit:60

To re-run:
 vendor/bin/phpunit "core_calendar_container_testcase" calendar/tests/container_test.php

Just wondering if you have seen this error previously?

System Info:
Moodle version: 3.9.12+ ($version = 2020061512.01;)
MySQL version 5.7.33
PHP version 7.4.27

[sammarshallou]

(OpenStudio is nothing to do with me but I saw the email so I'm giving a slightly helpful answer.)

This looks like a common issue where MySQL can't do a delete with a subquery. There is an API called $DB->delete_records_subquery which deals with this by doing it a different way on MySQL, so that's the change that should be made to this code.

[jaypha]

It's not the subquery that is the problem. MySQL does not allow aliases to be used the way it is being used.
See https://stackoverflow.com/questions/34353799/can-aliases-be-used-in-a-sql-delete-query