Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: 2.3.5, 2.4.1
    • Fix Version/s: 2.3.7, 2.4.4
    • Component/s: Workshop
    • Labels:
    • Database:
      Oracle
    • Testing Instructions:
      Hide

      Testing difficulty: medium (requires multiple DB)

      1. Prepare a course with couple of students and groups.
      2. Create a workshop (no need to define the assessment form), Separate groups mode, no groupings. Switch it to the submission phase.
      3. Let students from all groups submit their work (HINT: http://docs.moodle.org/dev/Workshop/fakesubmissions.php)
      4. Click on Allocate submissions
      5. TEST: Make sure that Manual allocation and Random allocation work without throwing error.

      This tests SQL changes due to an error with Oracle. We have to make sure there are no regressions. The patch was already tested at Oracle by the reporter and at PostgreSQL by the assignee during the development. Thence we need to test it at MySQL and MSSQL yet. One of them should be tested at 2.3, the other at 2.4 or 2.5. Thanks in advance for your time!

      Show
      Testing difficulty: medium (requires multiple DB) Prepare a course with couple of students and groups. Create a workshop (no need to define the assessment form), Separate groups mode, no groupings. Switch it to the submission phase. Let students from all groups submit their work (HINT: http://docs.moodle.org/dev/Workshop/fakesubmissions.php ) Click on Allocate submissions TEST: Make sure that Manual allocation and Random allocation work without throwing error. This tests SQL changes due to an error with Oracle. We have to make sure there are no regressions. The patch was already tested at Oracle by the reporter and at PostgreSQL by the assignee during the development. Thence we need to test it at MySQL and MSSQL yet. One of them should be tested at 2.3, the other at 2.4 or 2.5. Thanks in advance for your time!
    • Affected Branches:
      MOODLE_23_STABLE, MOODLE_24_STABLE
    • Fixed Branches:
      MOODLE_23_STABLE, MOODLE_24_STABLE
    • Pull from Repository:
    • Pull 2.4 Branch:
      MDL-38215-workshop-oracle_24
    • Pull Master Branch:
      MDL-38215-workshop-oracle
    • Rank:
      48057

      Description

      When I try to submit allocation in workshop (using ORACLE) I get this error:

      Debug info: ORA-00904: “U”.”ID”: invalid identifier
      SELECT u.id,u.picture,u.firstname,u.lastname,u.imagealt,u.email
      FROM c_user u
      JOIN (SELECT DISTINCT eu5_u.id
      FROM c_user eu5_u JOIN c_role_assignments eu5_ra3 ON (eu5_ra3.userid = eu5_u.id AND eu5_ra3.roleid IN (5)
      AND eu5_ra3.contextid IN (1,15,141,356))
      JOIN c_groups_members eu5_gm ON (eu5_gm.userid = eu5_u.id AND eu5_gm.groupid = :o_eu5_gmid)
      JOIN c_user_enrolments eu5_ue ON eu5_ue.userid = eu5_u.id
      JOIN c_enrol eu5_e ON (eu5_e.id = eu5_ue.enrolid AND eu5_e.courseid = :o_eu5_courseid)
      WHERE eu5_u.deleted = 0 AND eu5_u.id <> :o_eu5_guestid AND eu5_ue.status = :o_eu5_active AND
      eu5_e.status = :o_eu5_enabled AND eu5_ue.timestart < :o_eu5_now1 AND (eu5_ue.timeend = 0 OR
      eu5_ue.timeend > :o_eu5_now2)) je ON (je.id = u.id AND u.deleted = 0) JOIN c_workshop_submissions ws ON
      (ws.authorid = u.id AND ws.example = 0 AND ws.workshopid = :o_workshopid6)
      UNION
      SELECT u.id,u.picture,u.firstname,u.lastname,u.imagealt,u.email
      FROM c_user u
      JOIN (SELECT DISTINCT eu6_u.id
      FROM c_user eu6_u JOIN c_role_assignments eu6_ra3 ON (eu6_ra3.userid = eu6_u.id AND eu6_ra3.roleid IN
      (5) AND eu6_ra3.contextid IN (1,15,141,356))
      JOIN c_groups_members eu6_gm ON (eu6_gm.userid = eu6_u.id AND eu6_gm.groupid = :o_eu6_gmid) JOIN
      c_user_enrolments eu6_ue ON eu6_ue.userid = eu6_u.id
      JOIN c_enrol eu6_e ON (eu6_e.id = eu6_ue.enrolid AND eu6_e.courseid = :o_eu6_courseid)
      WHERE eu6_u.deleted = 0 AND eu6_u.id <> :o_eu6_guestid AND eu6_ue.status = :o_eu6_active AND
      eu6_e.status = :o_eu6_enabled AND eu6_ue.timestart < :o_eu6_now1 AND (eu6_ue.timeend = 0 OR
      eu6_ue.timeend > :o_eu6_now2)) je ON (je.id = u.id AND u.deleted = 0)
      JOIN c_workshop_submissions ws ON (ws.authorid = u.id AND ws.example = 0 AND ws.workshopid = :o_workshopid7)
      ORDER BY u.lastname, u.firstname, u.id

      [array (
      'o_eu5_gmid' => 4,
      'o_eu5_courseid' => '25',
      'o_eu5_guestid' => '1',
      'o_eu5_active' => 0,
      'o_eu5_enabled' => 0,
      'o_eu5_now1' => 1360757700,
      'o_eu5_now2' => 1360757700,
      'o_workshopid6' => '43',
      'o_eu6_gmid' => 3,
      'o_eu6_courseid' => '25',
      'o_eu6_guestid' => '1',
      'o_eu6_active' => 0,
      'o_eu6_enabled' => 0,
      'o_eu6_now1' => 1360757700,
      'o_eu6_now2' => 1360757700,
      'o_workshopid7' => '43',
      )]

      Error code: dmlreadexception
      Stack trace: line 426 of /lib/dml/moodle_database.php: dml_read_exception thrown line 274 of

      /lib/dml/oci_native_moodle_database.php: call to moodle_database→query_end() line 1101 of

      /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database→query_end() line 424 of

      /mod/workshop/locallib.php: call to oci_native_moodle_database→get_records_sql() line 324 of

      /mod/workshop/allocation/manual/lib.php: call to workshop→get_potential_authors() line 79 of

      /mod/workshop/allocation.php: call to workshop_manual_allocator→ui()

      Testing Instructions:
      1) create a workshop
      2) Set Group mode: Separate groups
      3) Set Grouping: None
      4) Click on Allocate submissions
      5) Click on Manual allocation or Random allocation

      I think that is problem is caused the usage of alias in the union.

      I try to add a dummy alias in 2 functions: get_potential_authors and get_potential_reviewers)
      in this way

      list($sort, $sortparams) = users_order_by_sql('u');
      $sql .= " ORDER BY $sort";

      $sql = "SELECT * FROM (".$sql;
      list($sort, $sortparams) = users_order_by_sql('uu');
      $sql .= ")uu ORDER BY $sort";

      In this way it seems to work but I'm not sure that this solution is correct.

      Any confirm?

        Issue Links

          Activity

          Hide
          David Mudrak added a comment -

          Thanks for the report. This is on my radar now. Please confirm that adding the alias fixed the issue for you.

          Show
          David Mudrak added a comment - Thanks for the report. This is on my radar now. Please confirm that adding the alias fixed the issue for you.
          Hide
          Sara Cenni added a comment -

          Everything works properly now!

          Show
          Sara Cenni added a comment - Everything works properly now!
          Hide
          David Mudrak added a comment -
          Show
          David Mudrak added a comment - For the refernce: http://docs.oracle.com/html/E10592_02/queries005.htm
          Hide
          David Mudrak added a comment -

          Same situation was solved in MDL-30051 (a415ecc21cf7b4f2848fc86c1e5b35da98a0d96d)

          Show
          David Mudrak added a comment - Same situation was solved in MDL-30051 (a415ecc21cf7b4f2848fc86c1e5b35da98a0d96d)
          Hide
          David Mudrak added a comment -

          Sara, can you please try and apply the patch https://github.com/mudrd8mz/moodle/compare/MOODLE_24_STABLE...MDL-38215-workshop-oracle_24 and let me know if that fixes the issue for you? Please try both Manual allocation and Random allocation methods.

          Show
          David Mudrak added a comment - Sara, can you please try and apply the patch https://github.com/mudrd8mz/moodle/compare/MOODLE_24_STABLE...MDL-38215-workshop-oracle_24 and let me know if that fixes the issue for you? Please try both Manual allocation and Random allocation methods.
          Hide
          Sara Cenni added a comment -

          Everything works with the patch. I tried all the 3 types of allocation: Manual allocation, Random allocation and Scheduled allocation.

          Show
          Sara Cenni added a comment - Everything works with the patch. I tried all the 3 types of allocation: Manual allocation, Random allocation and Scheduled allocation.
          Hide
          David Mudrak added a comment -

          Great! Thanks a lot Sara. I'm submitting this for integration review now.

          Show
          David Mudrak added a comment - Great! Thanks a lot Sara. I'm submitting this for integration review now.
          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
          Damyon Wiese added a comment -

          Thanks David,

          Note: I had a quick look at adding unit tests for this - but the current locallib tests for workshop do not use a real context/course so "get_users_with_capability_sql" failed.

          Tested on master with MSSQL and passed fine.

          Integrated to 23, 24 and master branches now.

          Show
          Damyon Wiese added a comment - Thanks David, Note: I had a quick look at adding unit tests for this - but the current locallib tests for workshop do not use a real context/course so "get_users_with_capability_sql" failed. Tested on master with MSSQL and passed fine. Integrated to 23, 24 and master branches now.
          Hide
          Frédéric Massart added a comment -

          Passing after testing with MySQL on 2.3 and master. MSSQL has been tested by Damyon on master. Thanks!

          Show
          Frédéric Massart added a comment - Passing after testing with MySQL on 2.3 and master. MSSQL has been tested by Damyon on master. Thanks!
          Hide
          Damyon Wiese added a comment -

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

          Thanks for your contributions!

          Show
          Damyon Wiese added a comment - This issue has been integrated upstream and is now available via git (and in some hours, via mirrors and downloads). Thanks for your contributions!

            People

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

              Dates

              • Created:
                Updated:
                Resolved: