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

      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").

        Gliffy Diagrams

          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: