Moodle
  1. Moodle
  2. MDL-34661

SQL error in assignments module with Oracle Database II

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.3.1
    • Fix Version/s: STABLE backlog
    • Component/s: Assignment
    • Labels:
    • Affected Branches:
      MOODLE_23_STABLE
    • Rank:
      43110

      Description

      We start using moodle 2.3.1 on a new install (not upgrading) with Oracle 10.2 database.

      We found a error in SQL statements in assignments módules.

      After creating a assignment, when teacher click in the link to see the works sent by students the application display a error about the SQL statement (with params replaced):

      SELECT *
      FROM
        (SELECT u.id,
          u.picture,
          u.firstname,
          u.lastname,
          u.imagealt,
          u.email,
          u.id           AS userid,
          u.firstname    AS firstname,
          u.lastname     AS lastname,
          s.status       AS status,
          s.id           AS submissionid,
          s.timecreated  AS firstsubmission,
          s.timemodified AS timesubmitted,
          g.id           AS gradeid,
          g.grade        AS grade,
          g.timemodified AS timemarked,
          g.timecreated  AS firstmarked,
          g.mailed       AS mailed,
          g.locked       AS locked
        FROM mduser u
        LEFT JOIN mdassign_submission s
        ON u.id          = s.userid
        AND s.assignment = 23
        LEFT JOIN mdassign_grades g
        ON u.id          = g.userid
        AND g.assignment = 23
        WHERE u.id       = 14
        )
      WHERE rownum <= 10
      

      Looking at sentence we found that there are two columns renamed with the "AS" keyword that will have the same name of the other columns not renamed: firstname, lastname.

      Solution (for now):
      We replaced the alias for the columns with "firstnamex" and "lastnamex" offline with offlinex in the file <moodlehome>/mod/assign/gradingtable.php:

      $fields = user_picture::fields('u') . ', u.id as userid, u.firstname as firstname, u.lastname as lastname, ';
      $fields .= 's.status as status, s.id as submissionid, s.timecreated as firstsubmission, s.timemodified as timesubmitted, ';
      $fields .= 'g.id as gradeid, g.grade as grade, g.timemodified as timemarked, g.timecreated as firstmarked, g.mailed as mailed, g.locked as locked';
      $from = '{user} u LEFT JOIN {assign_submission} s ON u.id = s.userid AND s.assignment = :assignmentid1' .
              ' LEFT JOIN {assign_grades} g ON u.id = g.userid AND g.assignment = :assignmentid2';
      

        Issue Links

          Activity

          Hide
          Michael de Raadt added a comment -

          Thanks for reporting that.

          I think the problem is that these "firstname" and "lastname" fields are appearing in the select more than once and they don't need to.

          Show
          Michael de Raadt added a comment - Thanks for reporting that. I think the problem is that these "firstname" and "lastname" fields are appearing in the select more than once and they don't need to.
          Hide
          Damyon Wiese added a comment -

          This is a duplicate and the linked bug is now fixed.

          Show
          Damyon Wiese added a comment - This is a duplicate and the linked bug is now fixed.

            People

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

              Dates

              • Created:
                Updated:
                Resolved: