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

Error when adding a grouping access restriction to workshop: Incorrect number of query parameters. Expected 20, got 19

    XMLWordPrintable

Details

    • MOODLE_29_STABLE, MOODLE_30_STABLE, MOODLE_31_STABLE, MOODLE_32_STABLE
    • MOODLE_30_STABLE, MOODLE_31_STABLE
    • MDL-53481-master-uniqsqlparam
    • Hide
      1. Create a course with a group and a grouping.
      2. Set up a workshop activity. Configure the availability criteria so that the workshop is restricted only for users in both a group and a grouping.
      3. Attempt to view the workshop main page.
      4. TEST: Make sure you do not see an error and the number of expected submissions is displayed correctly.
      Show
      Create a course with a group and a grouping. Set up a workshop activity. Configure the availability criteria so that the workshop is restricted only for users in both a group and a grouping. Attempt to view the workshop main page. TEST: Make sure you do not see an error and the number of expected submissions is displayed correctly.

    Description

      We get this error when adding a grouping access restriction to a workshop:

      Default exception handler: ERROR: Incorrect number of query parameters. Expected 20, got 19. Debug: 
      Error code: invalidqueryparam
      * line 851 of /lib/dml/moodle_database.php: dml_exception thrown
      * line 761 of /lib/dml/pgsql_native_moodle_database.php: call to moodle_database->fix_sql_params()
      * line 1480 of /lib/dml/moodle_database.php: call to pgsql_native_moodle_database->get_records_sql()
      * line 1553 of /lib/dml/moodle_database.php: call to moodle_database->get_record_sql()
      * line 1763 of /lib/dml/moodle_database.php: call to moodle_database->get_field_sql()
      * line 467 of /mod/workshop/locallib.php: call to moodle_database->count_records_sql()
      * line 2936 of /mod/workshop/locallib.php: call to workshop->count_potential_authors()
      * line 104 of /mod/workshop/view.php: call to workshop_user_plan->__construct()
      

      I think this can be a issue of the recursive get_users_with_capability_sql function on /mod/workshop/locallib.php, because we've discovered that the query built there uses the same parameter twice (:usp1) but that parameter is included only once in the $params list because of the array_merge function.

      I'm not sure about how this is handled on each DBMS (our database is PostgreSQL, and AFAIK Oracle does the same as PG), so maybe this error only happens on some DBMS.

      This is a example of the query and the parameters:

      SELECT COUNT(*)
        FROM (SELECT u.id,
                     u.picture,
                     u.firstname,
                     u.lastname,
                     u.firstnamephonetic,
                     u.lastnamephonetic,
                     u.middlename,
                     u.alternatename,
                     u.imagealt,
                     u.email
                FROM {user} u
                JOIN (SELECT DISTINCT eu1_u.id
                       FROM {user} eu1_u
                       JOIN {role_assignments} eu1_ra3
                         ON (eu1_ra3.userid = eu1_u.id AND eu1_ra3.roleid IN (5) AND
                            eu1_ra3.contextid IN (1, 41, 113233, 113329))
                       JOIN {user_enrolments} eu1_ue
                         ON eu1_ue.userid = eu1_u.id
                       JOIN {enrol} eu1_e
                         ON (eu1_e.id = eu1_ue.enrolid AND
                            eu1_e.courseid = :eu1_courseid)
                      WHERE eu1_u.deleted = 0
                        AND eu1_u.id <> :eu1_guestid
                        AND eu1_ue.status = :eu1_active
                        AND eu1_e.status = :eu1_enabled
                        AND eu1_ue.timestart < :eu1_now1
                        AND (eu1_ue.timeend = 0 OR eu1_ue.timeend > :eu1_now2)) je
                  ON (je.id = u.id AND u.deleted = 0)
                JOIN(((SELECT userids.id
                        FROM (SELECT DISTINCT eu3_u.id
                                FROM {user} eu3_u
                                JOIN {user_enrolments} eu3_ue
                                  ON eu3_ue.userid = eu3_u.id
                                JOIN {enrol} eu3_e
                                  ON (eu3_e.id = eu3_ue.enrolid AND
                                     eu3_e.courseid = :eu3_courseid)
                               WHERE eu3_u.deleted = 0
                                 AND eu3_u.id <> :eu3_guestid) userids
                       WHERE (userids.id IN
                             (SELECT DISTINCT eu2_u.id
                                 FROM {user} eu2_u
                                 JOIN {role_assignments} eu2_ra3
                                   ON (eu2_ra3.userid = eu2_u.id AND
                                      eu2_ra3.roleid IN (1, 4, 3) AND
                                      eu2_ra3.contextid IN (1, 41, 113233))
                                 JOIN {user_enrolments} eu2_ue
                                   ON eu2_ue.userid = eu2_u.id
                                 JOIN {enrol} eu2_e
                                   ON (eu2_e.id = eu2_ue.enrolid AND
                                      eu2_e.courseid = :eu2_courseid)
                                WHERE eu2_u.deleted = 0
                                  AND eu2_u.id <> :eu2_guestid))
                          OR EXISTS (SELECT 1
                                FROM {groups_members} gm
                               WHERE gm.userid = userids.id
                                 AND gm.groupid = :usp1))
              UNION (SELECT DISTINCT eu4_u.id
                      FROM {user} eu4_u
                      JOIN {role_assignments} eu4_ra3
                        ON (eu4_ra3.userid = eu4_u.id AND eu4_ra3.roleid IN (3, 1) AND
                           eu4_ra3.contextid IN (1, 41, 113233))
                      JOIN {user_enrolments} eu4_ue
                        ON eu4_ue.userid = eu4_u.id
                      JOIN {enrol} eu4_e
                        ON (eu4_e.id = eu4_ue.enrolid AND
                           eu4_e.courseid = :eu4_courseid)
                     WHERE eu4_u.deleted = 0
                       AND eu4_u.id <> :eu4_guestid))
              INTERSECT((SELECT userids.id
                          FROM (SELECT DISTINCT eu6_u.id
                                  FROM {user} eu6_u
                                  JOIN {user_enrolments} eu6_ue
                                    ON eu6_ue.userid = eu6_u.id
                                  JOIN {enrol} eu6_e
                                    ON (eu6_e.id = eu6_ue.enrolid AND
                                       eu6_e.courseid = :eu6_courseid)
                                 WHERE eu6_u.deleted = 0
                                   AND eu6_u.id <> :eu6_guestid) userids
                         WHERE (userids.id IN
                               (SELECT DISTINCT eu5_u.id
                                   FROM {user} eu5_u
                                   JOIN {role_assignments} eu5_ra3
                                     ON (eu5_ra3.userid = eu5_u.id AND
                                        eu5_ra3.roleid IN (1, 4, 3) AND
                                        eu5_ra3.contextid IN
                                        (1, 41, 113233, 113329))
                                   JOIN {user_enrolments} eu5_ue
                                     ON eu5_ue.userid = eu5_u.id
                                   JOIN {enrol} eu5_e
                                     ON (eu5_e.id = eu5_ue.enrolid AND
                                        eu5_e.courseid = :eu5_courseid)
                                  WHERE eu5_u.deleted = 0
                                    AND eu5_u.id <> :eu5_guestid))
                            OR EXISTS (SELECT 1
                                  FROM {groups_members} gm
                                  JOIN {groupings_groups} gg
                                    ON gg.groupid = gm.groupid
                                 WHERE gm.userid = userids.id
                                   AND gg.groupingid = :usp1))
              UNION (SELECT DISTINCT eu7_u.id
                      FROM {user} eu7_u
                      JOIN {role_assignments} eu7_ra3
                        ON (eu7_ra3.userid = eu7_u.id AND
                           eu7_ra3.roleid IN (4, 3, 1) AND
                           eu7_ra3.contextid IN (1, 41, 113233, 113329))
                      JOIN {user_enrolments} eu7_ue
                        ON eu7_ue.userid = eu7_u.id
                      JOIN {enrol} eu7_e
                        ON (eu7_e.id = eu7_ue.enrolid AND
                           eu7_e.courseid = :eu7_courseid)
                     WHERE eu7_u.deleted = 0
                       AND eu7_u.id <> :eu7_guestid))) restricted
                  ON restricted.id = u.id) tmp
      

      {"eu1_guestid":"1","eu1_courseid":"8395","eu1_enabled":0,"eu1_active":0,"eu1_now1":1458041200,"eu1_now2":1458041200,"eu3_guestid":"1","eu3_courseid":"8395","eu2_guestid":"1","eu2_courseid":"8395","usp1":256,"eu4_guestid":"1","eu4_courseid":"8395","eu6_guestid":"1","eu6_courseid":"8395","eu5_guestid":"1","eu5_courseid":"8395","eu7_guestid":"1","eu7_courseid":"8395"}
      

      The error happens when the query has an INTERSECT, and it happens (at least) when:

      • We have groups and groupings with students in a course
      • We add a workshop to that course with two access restrictions: one for groups and another one for groupings
      • The error shows up just after saving changes

      The error only shows up to teachers, and I think that's because the error is in the query that counts how many submissions are expected and that's only shown to teachers

      Attachments

        Issue Links

          Activity

            People

              mudrd8mz David Mudrák (@mudrd8mz)
              izendegi Iñigo Zendegi
              Adam Olley Adam Olley
              David MonllaĂł David MonllaĂł
              Rajesh Taneja Rajesh Taneja
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                14/Nov/16