Moodle
  1. Moodle
  2. MDL-29928

Sorting submitted assignments by status makes all assignments disappear - again

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Blocker Blocker
    • Resolution: Duplicate
    • Affects Version/s: 1.9.13, 2.0.5, 2.1.2
    • Fix Version/s: None
    • Component/s: Assignment (2.2)
    • Labels:
      None
    • Environment:
      Debian testing (with Moodle from moodle.org, not Debian package)
    • Database:
      MySQL
    • Affected Branches:
      MOODLE_19_STABLE, MOODLE_20_STABLE, MOODLE_21_STABLE
    • Rank:
      19462

      Description

      The problem described in MDL-10846 appears again in Moodle 1.9 (tested with 1.9.13+ (Build: 20110921)), with exactly the same cause:

      When looking at the list of assignments (by "View ... submitted assignments"), press "status" column title to sort by status. All records disappear. Reason: SQL query generated is invalid, it contains "order by status" but the status field doesn't exist.

      It seems that the fix for MDL-10846 no longer exists in 1.9.x source code. It seems that a fix exists in the latest 2.x source code, although I didn't check does it actually work. So I'm sure this bug affects 1.9.x branch, and I do not know if it affects 2.0.x or 2.1.x.

      I'm attaching a patch that fixes it simply by adding back the status field, but this patch may be suitable only for some databases (like MySQL), for other databases it may break MDL-8164 ("MS SQL cannot return Boolean data type in query results, any code using this returns no results when using Sql 2005").

        Issue Links

          Activity

          Hide
          Charles Fulton added a comment -

          The status line was removed entirely by MDL-27638 because the COALESCE() clause was buggy on MySQL. I'm not sure when COALESCE() was added to the original patch from MDL-10846. Taking the line out wasn't considered a problem because status is calculated later, but it isn't calculated for the purposes of a sort.

          Show
          Charles Fulton added a comment - The status line was removed entirely by MDL-27638 because the COALESCE() clause was buggy on MySQL. I'm not sure when COALESCE() was added to the original patch from MDL-10846 . Taking the line out wasn't considered a problem because status is calculated later, but it isn't calculated for the purposes of a sort.
          Hide
          Marina Glancy added a comment -

          This bug reproduces in all current versions. It definitely needs to be fixed ASAP.

          This is what happens: If users clicks header 'Status' on assignment grading page, there is a DB error displayed (see below)
          Since the sort order is remembered in session, user IS NOT ABLE to view any assignment grading page any more.

          I found how to return the page: click 'Back' and sort by other column. Error repeats because there is a stack of last two sorted columns in session.
          Click 'Back' again and sort by another good column. Error disappears.
          If 'Back' is not accessible any more, you can just add &tsort=lastname to the url, and for second attempt &tsort=firstname

          -------

          Error message in 1.9

          Unknown column 'status' in 'order clause'

          SELECT u.id, u.firstname, u.lastname, u.picture, u.imagealt, s.id AS submissionid, s.grade, s.submissioncomment, s.timemodified, s.timemarked FROM mdl_user u LEFT JOIN mdl_assignment_submissions s ON u.id = s.userid AND s.assignment = 1 WHERE u.id IN (3,4,8,10,12,14,16,18,9,11,13,15,17) ORDER BY status ASC, firstname ASC
          line 687 of lib/dmllib.php: call to debugging()
          line 967 of lib/dmllib.php: call to get_recordset_sql()
          line 1184 of mod/assignment/lib.php: call to get_records_sql()
          line 524 of mod/assignment/lib.php: call to assignment_base->display_submissions()
          line 43 of mod/assignment/submissions.php: call to assignment_base->submissions()

          -------------------

          Error message in HEAD

          Debug info: Unknown column 'status' in 'order clause'
          SELECT u.id,u.picture,u.firstname,u.lastname,u.imagealt,u.email,
          s.id AS submissionid, s.grade, s.submissioncomment,
          s.timemodified, s.timemarked FROM mdl_user u LEFT JOIN mdl_assignment_submissions s ON u.id = s.userid
          AND s.assignment = 4 WHERE u.id IN (3,4,11) ORDER BY status ASC, lastname ASC
          [array (
          )]
          Stack trace:
          line 394 of /lib/dml/moodle_database.php: dml_read_exception thrown
          line 809 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
          line 1325 of /mod/assignment/lib.php: call to mysqli_native_moodle_database->get_records_sql()
          line 649 of /mod/assignment/lib.php: call to assignment_base->display_submissions()
          line 57 of /mod/assignment/submissions.php: call to assignment_base->submissions()

          Show
          Marina Glancy added a comment - This bug reproduces in all current versions. It definitely needs to be fixed ASAP. This is what happens: If users clicks header 'Status' on assignment grading page, there is a DB error displayed (see below) Since the sort order is remembered in session, user IS NOT ABLE to view any assignment grading page any more . I found how to return the page: click 'Back' and sort by other column. Error repeats because there is a stack of last two sorted columns in session. Click 'Back' again and sort by another good column. Error disappears. If 'Back' is not accessible any more, you can just add &tsort=lastname to the url, and for second attempt &tsort=firstname ------- Error message in 1.9 Unknown column 'status' in 'order clause' SELECT u.id, u.firstname, u.lastname, u.picture, u.imagealt, s.id AS submissionid, s.grade, s.submissioncomment, s.timemodified, s.timemarked FROM mdl_user u LEFT JOIN mdl_assignment_submissions s ON u.id = s.userid AND s.assignment = 1 WHERE u.id IN (3,4,8,10,12,14,16,18,9,11,13,15,17) ORDER BY status ASC, firstname ASC line 687 of lib/dmllib.php: call to debugging() line 967 of lib/dmllib.php: call to get_recordset_sql() line 1184 of mod/assignment/lib.php: call to get_records_sql() line 524 of mod/assignment/lib.php: call to assignment_base->display_submissions() line 43 of mod/assignment/submissions.php: call to assignment_base->submissions() ------------------- Error message in HEAD Debug info: Unknown column 'status' in 'order clause' SELECT u.id,u.picture,u.firstname,u.lastname,u.imagealt,u.email, s.id AS submissionid, s.grade, s.submissioncomment, s.timemodified, s.timemarked FROM mdl_user u LEFT JOIN mdl_assignment_submissions s ON u.id = s.userid AND s.assignment = 4 WHERE u.id IN (3,4,11) ORDER BY status ASC, lastname ASC [array ( )] Stack trace: line 394 of /lib/dml/moodle_database.php: dml_read_exception thrown line 809 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end() line 1325 of /mod/assignment/lib.php: call to mysqli_native_moodle_database->get_records_sql() line 649 of /mod/assignment/lib.php: call to assignment_base->display_submissions() line 57 of /mod/assignment/submissions.php: call to assignment_base->submissions()
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Hi, I'm closing this as duplicate of MDL-29529, where it seems we are advancing and near fix the regression.

          Thanks all, ciao

          Show
          Eloy Lafuente (stronk7) added a comment - Hi, I'm closing this as duplicate of MDL-29529 , where it seems we are advancing and near fix the regression. Thanks all, ciao

            People

            • Votes:
              10 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: