-
Bug
-
Resolution: Fixed
-
Major
-
2.9.4, 2.9.5, 3.0.3, 3.1, 3.2
-
MOODLE_29_STABLE, MOODLE_30_STABLE, MOODLE_31_STABLE, MOODLE_32_STABLE
-
MOODLE_30_STABLE, MOODLE_31_STABLE
-
MDL-53481-master-uniqsqlparam -
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
- Testing discovered
-
MDL-56884 sql_intersect() under fire / limits
-
- Closed
-