One of the unittests in Moodle LMS started failing on MariaDB since testing docker image was upgraded to version 10.5.10.
The same unittest and the same query was passing on MariaDB 10.5.9. Also it passes on MySQL, Postgres, MsSQL and Oracle (all databases supported by Moodle).
I have created an SQL file to demonstrate the problem. It creates two database tables, fills them with the data and performs a query:
This query is slightly simplified from what we actually use in Moodle in order to demonstrate the problem.
On MariaDB 10.5.9 and all other databases it returns:
On MariaDB 10.5.10 it returns:
To make it even more interesting, the following query (using "LEFT JOIN") returns correct results. This is even more confusing because in the return values you can see that data in the grade_items table is present and it is actually an inner join.
Attaching the test file demo_sql_error_simplified.sql