Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-84978

Forum last post user and date is incorrect

XMLWordPrintable

      Forums have an overview page that shows the author and date of the first / last post in each discussion. We noticed that the last post author and date is often incorrect for discussions with multiple posts.

      We tracked the issue to commit https://github.com/moodle/moodle/commit/0f5a96ff3e3aa1ab44b43b8b9a001e520acda764, which was part of https://tracker.moodle.org/browse/MDL-80848

      Steps to reproduce:
      1. Create a course
      2. Enrol two users to the course: A and B
      3. Create a regular forum in this course
      4. Create multiple discussions in this forum
      5. Add multiple replies to these discussions as users A and B
      6. Observe the forum overview page. At some point you might notice that the "Last post" (or sometimes even the "Started by") user is incorrect.

      Unfortunately, this problem is tricky to reproduce, as it relies on the DB returning records in non-deterministic order. This is why you need multiple discussions in the same forum, the problem doesn't seem to appear with only one discussion. But again, this depends heavily on how the DB engine decides to execute the query. We use PostgreSQL.

      But conceptually the issue is simple. This was the query in the get_latest_posts_for_discussion_ids method in mod/forum/classes/local/vaults/post.php:

      SELECT posts.*
      		 
      FROM mdl_forum_posts posts
      		 
      JOIN (
      		 
          SELECT p.discussion, MAX(p.id) as latestpostid
      		 
          FROM mdl_forum_posts p
      		 
          JOIN (
      		 
              SELECT mp.discussion, MAX(mp.created) AS created
      		 
              FROM mdl_forum_posts mp
      		 
              WHERE mp.discussion IN :discussionids
      		 
              GROUP BY mp.discussion
      		 
          ) lp ON lp.discussion = p.discussion AND lp.created = p.created
      		 
      GROUP BY p.discussion
      		 
      ) plp on plp.discussion = posts.discussion AND plp.latestpostid = posts.id;

      Which got replaced by this:

      SELECT p.*
      		 
      FROM mdl_forum_posts p
      		 
      JOIN (
      		 
          SELECT mp.discussion, mp.created, MAX(mp.id) AS latestpostid
      		 
          FROM mdl_forum_posts mp
      		 
          WHERE mp.discussion IN :discussionids
      		 
          GROUP BY mp.discussion, mp.created
      		 
          HAVING mp.created = MAX(mp.created)
      		 
      ) lp on lp.discussion = p.discussion AND lp.latestpostid = p.id;

      (Code formatted and simplified for readability.)

      The second version tried to simplify the logic into one subselect instead of doubly nested subselects, but misunderstands how HAVING works. (The aggregating functions like MAX are computed per group, so HAVING column = MAX(column) is always satisfied if the column is part of the GROUP BY.)

      So, while the original query returned one post per discussion (the latest), the new query basically returns all the posts in the discussions (it only filters out posts if there are multiple posts in a discussion with exactly the same created), and now it matters in which orders the records are returned.

        1. 84978 sql breakdown.png
          84978 sql breakdown.png
          57 kB
        2. 84978 peer review - manual test.png
          84978 peer review - manual test.png
          187 kB

            olivabigyo olivabigyo
            olivabigyo olivabigyo
            Andrew Gosali Andrew Gosali
            Mihail Geshoski Mihail Geshoski
            Votes:
            23 Vote for this issue
            Watchers:
            27 Start watching this issue

              Created:
              Updated:

                Estimated:
                Original Estimate - 0 minutes
                0m
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 6 hours, 45 minutes
                6h 45m

                  Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.