Moodle
  1. Moodle
  2. MDL-34660

SQL error in assignments module with Oracle Database I

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.3.1, 2.4
    • Fix Version/s: 2.3.3
    • Component/s: Assignment
    • Labels:
    • Rank:
      43109

      Description

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

      We found two erros in SQL statements in assignments modules.

      1 - 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 a.id       AS assignment,
        a.nosubmissions AS offline,
        g.timemodified  AS timemarked,
        g.grader        AS grader,
        g.grade         AS grade,
        s.status        AS status
      FROM mdassign a
      LEFT JOIN mdassign_grades g
      ON g.assignment = a.id
      AND g.userid    = '3'
      LEFT JOIN mdassign_submission s
      ON s.assignment = a.id
      AND s.userid    = '3'
      AND a.id        = '23'
      

      Looking at the sentence we found that the word "offline" is a reserved word in Oracle environment.

      Solution (for now):
      We replaced the word offline with offlinex in the file <moodlehome>/mod/assign/lib.php in then sentence:

      // get all user submissions, indexed by assignment id
      $mysubmissions = $DB->get_records_sql("SELECT a.id AS assignment, a.nosubmissions AS offline, g.timemodified AS timemarked, g.grader AS grader, g.grade AS grade, s.status AS status
                                  FROM {assign} a LEFT JOIN {assign_grades} g ON g.assignment = a.id AND g.userid = ? LEFT JOIN {assign_submission} s ON s.assignment = a.id AND s.userid = ?
                                  AND a.id $sqlassignmentids", array_merge(array($USER->id, $USER->id), $assignmentidparams));
      

        Issue Links

          Activity

          Hide
          Michael de Raadt added a comment -

          Perhaps some other meaningful word could be used to replace "offline" instead of "offlinex". How about "nosubmissions".

          Show
          Michael de Raadt added a comment - Perhaps some other meaningful word could be used to replace "offline" instead of "offlinex". How about "nosubmissions".
          Hide
          Mauri added a comment -

          Hi. nosubmissions sounds good. The offlinex was added for to solve the problem in the production system and we'll wait the fix in the code to update the files with a patch.

          Show
          Mauri added a comment - Hi. nosubmissions sounds good. The offlinex was added for to solve the problem in the production system and we'll wait the fix in the code to update the files with a patch.
          Hide
          Raymond Antonio added a comment -

          Hi Damyon, Michael and Mauri,

          This is a patch for this bug using 'nosubmissions' as suggested and it sits on my github repo : MDL-34660
          https://github.com/raymondAntonio/moodle/tree/MDL-34660

          and here is the diff:

          https://github.com/raymondAntonio/moodle/commit/93c18e73c2e75209777c7a998b1906dc837db702

          Cheers

          Show
          Raymond Antonio added a comment - Hi Damyon, Michael and Mauri, This is a patch for this bug using 'nosubmissions' as suggested and it sits on my github repo : MDL-34660 https://github.com/raymondAntonio/moodle/tree/MDL-34660 and here is the diff: https://github.com/raymondAntonio/moodle/commit/93c18e73c2e75209777c7a998b1906dc837db702 Cheers
          Hide
          Eloy Lafuente (stronk7) added a comment -

          The main moodle.git repository has just been updated with latest weekly modifications. You may wish to rebase your PULL branches to simplify history and avoid any possible merge conflicts. This would also make integrator's life easier next week.

          TIA and ciao

          Show
          Eloy Lafuente (stronk7) added a comment - The main moodle.git repository has just been updated with latest weekly modifications. You may wish to rebase your PULL branches to simplify history and avoid any possible merge conflicts. This would also make integrator's life easier next week. TIA and ciao
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Integrated (23 & master), thanks!

          Show
          Eloy Lafuente (stronk7) added a comment - Integrated (23 & master), thanks!
          Hide
          Michael de Raadt added a comment -

          Test result: Success!

          Tested under Oracle and MySQL in 2.3 and master.

          This could have benefited from some more specific test instructions.

          Show
          Michael de Raadt added a comment - Test result: Success! Tested under Oracle and MySQL in 2.3 and master. This could have benefited from some more specific test instructions.
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Gutta cavat lapidem, non vi sed saepe cadendo - Ovidio

          This issue has been integrated upstream and is now available both via git and cvs (and in some hours, via mirrors and downloads).

          Thanks!

          Show
          Eloy Lafuente (stronk7) added a comment - Gutta cavat lapidem, non vi sed saepe cadendo - Ovidio This issue has been integrated upstream and is now available both via git and cvs (and in some hours, via mirrors and downloads). Thanks!

            People

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

              Dates

              • Created:
                Updated:
                Resolved: