Illegal join expression when searching shared-with-me
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
| Mahara |
High
|
Unassigned | ||
| 15.10 |
High
|
Unassigned | ||
| 16.04 |
High
|
Unassigned | ||
| 16.10 |
High
|
Unassigned |
Bug Description
In the German forum a user reported a "site unavailable" error when searching on the "shared with me" page. This applied to version 15.10.2.
Another use confirmed the problem in his installation with version 15.10.1 installed.
From the first setup we got an error log with the SQL-Error (see below)
See forum post (german): https:/
Failed to get a recordset: mysqli error: [1054: Unknown column 'cv.collection' in 'on clause'] in EXECUTE("
FROM "view" v
LEFT OUTER JOIN "collection_view" cv ON cv.view = v.id
LEFT OUTER JOIN "collection" c ON cv.collection = c.id
LEFT OUTER JOIN "usr" qu ON (v.owner = qu.id)
LEFT JOIN "view_tag" vt ON (vt.view = v.id AND vt.tag = ?)
LEFT OUTER JOIN "group" qqg ON (v.group = qqg.id)
LEFT OUTER JOIN "institution" qqi ON (v.institution = qqi.name)LEFT OUTER JOIN (
SELECT c.onview, MAX(a.mtime) AS lastcomment
FROM "artefact_
GROUP BY c.onview
) l ON v.id = l.onview
WHERE (v.owner IS NULL OR (v.owner > 0 AND v.owner != ?))
AND (v.group IS NULL OR v.group NOT IN (SELECT id FROM "group" WHERE deleted = 1))
AND (qu.suspendedctime is null OR v.owner = ?) AND v.type IN ('portfolio')
AND (v.title LIKE '%' || ? || '%'
OR v.description LIKE '%' || ? || '%'
OR vt.tag = ?
OR qu.preferredname LIKE '%' || ? || '%'
OR qu.firstname LIKE '%' || ? || '%'
OR qu.lastname LIKE '%' || ? || '%'
OR qqg.name LIKE '%' || ? || '%'
OR qqi.displayname LIKE '%' || ? || '%'
OR qu.username LIKE '%' || ? || '%' OR EXISTS (
SELECT 1
FROM
"view" v2
INNER JOIN "collection_view" cv2
ON v2.id=cv2.view
AND cv2.collection = cv.collection
INNER JOIN "collection" c2
ON c2.id = cv2.collection
LEFT OUTER JOIN "view_tag" vt
ON (vt.view = v2.id AND vt.tag = ?)
LEFT OUTER JOIN "collection_tag" ct
ON (ct.collection = cv2.collection AND ct.tag = ?)
WHERE
OR v2.description LIKE '%' || ? || '%'
OR c2.name LIKE '%' || ? || '%'
OR c2.description LIKE '%' || ? || '%'
OR vt.tag = ?
OR ct.tag = ?
))
AND (cv.displayorder = 0 OR cv.displayorder IS NULL)
AND (FALSE
OR ( -- user has permission to see the view
AND (v.stopdate IS NULL OR v.stopdate > current_timestamp)
AND (v.id IN (-- user access
")
Mahara Bot (dev-mahara) wrote : | #2 |
Patch for "master" branch: https:/
Changed in mahara: | |
importance: | Undecided → High |
status: | New → Confirmed |
status: | Confirmed → In Progress |
milestone: | none → 16.10.0 |
Reviewed: https:/
Committed: https:/
Submitter: Robert Lyon (<email address hidden>)
Branch: master
commit 65c21985055662c
Author: Tobias Zeuch <email address hidden>
Date: Mon Apr 18 00:05:28 2016 +0200
Fix illegal reference in join condition on Exists-subquery
Bug 1571421: In the exists-subquery the join-condition contains a reference to an outer table alias, which is not supported in MySql (though it seems to work in Postgres). This leads to a "site unavailable" e.g. when searching on the "shared with me" page.
The solution is to move the condition into the WHERE-part. This might actually improve performance because it allows the database engine to precalculate the join one single time and reuse it for each Exists-subquery
behatnotneeded
Change-Id: I5097154d939bf7
Signed-off-by: Tobias Zeuch <email address hidden>
Patch for "16.04_STABLE" branch: https:/
Reviewed: https:/
Committed: https:/
Submitter: Robert Lyon (<email address hidden>)
Branch: 16.04_STABLE
commit fc43b4be4851f83
Author: Tobias Zeuch <email address hidden>
Date: Mon Apr 18 00:05:28 2016 +0200
Fix illegal reference in join condition on Exists-subquery
Bug 1571421: In the exists-subquery the join-condition contains a reference to an outer table alias, which is not supported in MySql (though it seems to work in Postgres). This leads to a "site unavailable" e.g. when searching on the "shared with me" page.
The solution is to move the condition into the WHERE-part. This might actually improve performance because it allows the database engine to precalculate the join one single time and reuse it for each Exists-subquery
behatnotneeded
Change-Id: I5097154d939bf7
Signed-off-by: Tobias Zeuch <email address hidden>
(cherry picked from commit 65c21985055662c
Mahara Bot (dev-mahara) wrote : | #6 |
Reviewed: https:/
Committed: https:/
Submitter: Robert Lyon (<email address hidden>)
Branch: 15.10_STABLE
commit 504d8270fc79773
Author: Tobias Zeuch <email address hidden>
Date: Mon Apr 18 00:05:28 2016 +0200
Fix illegal reference in join condition on Exists-subquery
Bug 1571421: In the exists-subquery the join-condition contains a reference to an outer table alias, which is not supported in MySql (though it seems to work in Postgres). This leads to a "site unavailable" e.g. when searching on the "shared with me" page.
The solution is to move the condition into the WHERE-part. This might actually improve performance because it allows the database engine to precalculate the join one single time and reuse it for each Exists-subquery
behatnotneeded
Change-Id: I5097154d939bf7
Signed-off-by: Tobias Zeuch <email address hidden>
(cherry picked from commit 65c21985055662c
Changed in mahara: | |
status: | Fix Committed → Fix Released |
Patch for "15.10_STABLE" branch: https:/ /reviews. mahara. org/6351