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

SQL error in assignments module with Oracle Database II

    Details

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

      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';

        Gliffy Diagrams

          Issue Links

            Activity

            Hide
            salvetore 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
            salvetore 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 Damyon Wiese added a comment -

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

            Show
            damyon 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: