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

Error when viewing participant's posts page

    Details

    • Database:
      Microsoft SQL
    • Testing Instructions:
      Hide

      Repeat the following for all supported databases, especially Oracle and Mssql!

      1. Log in as admin/teacher
      2. Navigate to a course with students who have posted to the forums
      3. Navigate to Navigation > CourseXXX > Participants
      4. Select a participant
      5. Navigate to Navigation > CourseXXX > Participants > ParticipantXXX > Forum posts > Posts
      6. VERIFY: the results are accurate and no errrors are returned
      7. Navigate to Navigation > CourseXXX > Participants > ParticipantXXX > Forum posts > Discussions
      8. VERIFY: the results are accurate and no errrors are returned
      Show
      Repeat the following for all supported databases, especially Oracle and Mssql! Log in as admin/teacher Navigate to a course with students who have posted to the forums Navigate to Navigation > CourseXXX > Participants Select a participant Navigate to Navigation > CourseXXX > Participants > ParticipantXXX > Forum posts > Posts VERIFY: the results are accurate and no errrors are returned Navigate to Navigation > CourseXXX > Participants > ParticipantXXX > Forum posts > Discussions VERIFY: the results are accurate and no errrors are returned
    • Affected Branches:
      MOODLE_22_STABLE
    • Fixed Branches:
      MOODLE_22_STABLE, MOODLE_23_STABLE
    • Pull Master Branch:
      wip-MDL-34527-m24

      Description

      There is an error when you view the Posts and Discussions pages related to a course participant. This seems to happen in MS SQL and Oracle but not MySQL or PostgreSQL.

      The problem is that we are using the DISTINCT qualifier on all fields from the forum table, which includes a long text field. We need to investigate what fields are actually needed from this query. I started tracing this back, but it will take some time to determine which fields are actually needed.

      Default exception handler: Error reading from database Debug: The ntext data type cannot be selected as DISTINCT because it is not comparable.
      SELECT DISTINCT f.*, cm.id AS cmid
                  FROM mdl_forum f
                  JOIN mdl_course_modules cm ON cm.instance = f.id
                  JOIN mdl_modules m ON m.id = cm.module
                  JOIN mdl_forum_discussions fd ON fd.forum = f.id
                          JOIN mdl_forum_posts fp ON fp.discussion = fd.id
                  WHERE m.name = 'forum' AND f.course = ? AND fp.userid = ?
      [array (
        0 => 2203,
        1 => '33713',
      )]
      * line 394 of \lib\dml\moodle_database.php: dml_read_exception thrown
      * line 255 of \lib\dml\mssql_native_moodle_database.php: call to moodle_database->query_end()
      * line 711 of \lib\dml\mssql_native_moodle_database.php: call to mssql_native_moodle_database->query_end()
      * line 740 of \lib\dml\mssql_native_moodle_database.php: call to mssql_native_moodle_database->get_recordset_sql()
      * line 7925 of \mod\forum\lib.php: call to mssql_native_moodle_database->get_records_sql()
      * line 8078 of \mod\forum\lib.php: call to forum_get_forums_user_posted_in()
      * line 121 of \mod\forum\user.php: call to forum_get_posts_by_user()

      Replication steps:

      1. Log in as admin/teacher
      2. Navigate to a course with students who have posted to the forums
      3. Navigate to Navigation > CourseXXX > Participants
      4. Select a participant
      5. Navigate to Navigation > CourseXXX > Participants > ParticipantXXX > Forum posts > Posts
      6. Navigate to Navigation > CourseXXX > Participants > ParticipantXXX > Forum posts > Discussions

      Expected result: The student's posts are reported

      Actual result: With MS SQL and Oracle, an error is shown

      The offending query is at around 8046 in /mod/forum/lib

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

                • Votes:
                  0 Vote for this issue
                  Watchers:
                  4 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:
                    Fix Release Date:
                    12/Nov/12