Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-25714

Join using derived with aggregation returns incorrect results

    XMLWordPrintable

    Details

      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

            Activity

              People

              Assignee:
              igor Igor Babaev
              Reporter:
              marinaglancy Marina Glancy
              Votes:
              1 Vote for this issue
              Watchers:
              14 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: