Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
10.5.10, 10.3(EOL), 10.4(EOL), 10.5
Description
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:
SELECT h.id, gi.itemtype, gi.itemmodule, h.userid, h.rawgrade
|
FROM grade_grades_history h
|
JOIN (SELECT itemid, MAX(id) AS id
|
FROM grade_grades_history
|
WHERE userid = 131000
|
GROUP BY itemid) maxquery ON h.id = maxquery.id AND h.itemid = maxquery.itemid
|
JOIN grade_items gi ON gi.id = h.itemid
|
WHERE gi.courseid = 128000;
|
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:
id itemtype itemmodule userid rawgrade
|
330004 course NULL 131000 NULL
|
330003 mod assign 131000 50.00000
|
On MariaDB 10.5.10 it returns:
id itemtype itemmodule userid rawgrade
|
330004 course NULL 131000 NULL
|
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.
SELECT h.id, gi.itemtype, gi.itemmodule, h.userid, h.rawgrade
|
FROM grade_grades_history h
|
JOIN (SELECT itemid, MAX(id) AS id
|
FROM grade_grades_history
|
WHERE userid = 131000
|
GROUP BY itemid) maxquery ON h.id = maxquery.id AND h.itemid = maxquery.itemid
|
LEFT JOIN grade_items gi ON gi.id = h.itemid
|
Attaching the test file demo_sql_error_simplified.sql
Attachments
Issue Links
- is caused by
-
-
- Closed
-
- is duplicated by
-
-
- Closed
-
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-