Illegal join expression when searching shared-with-me

Bug #1571421 reported by Tobias Zeuch on 2016-04-17
18
This bug affects 2 people
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://mahara.org/interaction/forum/topic.php?id=7576&offset=0&limit=10#post30523

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_comment_comment" c JOIN "artefact" a ON c.artefact = a.id AND c.deletedby IS NULL AND c.private = 0
    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
            v2.title LIKE '%' || ? || '%'
            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
            (v.startdate IS NULL OR v.startdate < current_timestamp)
            AND (v.stopdate IS NULL OR v.stopdate > current_timestamp)
            AND (v.id IN (-- user access
                    SELECT va.view
                    FROM "view_access" va
                    WHERE va.usr = ?
                        AND (va.startdate IS NULL OR va.startdate < current_timestamp)
                        AND (va.stopdate IS NULL OR va.stopdate > current_timestamp)
                 UNION -- friend access
                    SELECT va.view
                    FROM "view_access" va
                        JOIN "view" vf ON va.view = vf.id AND vf.owner IS NOT NULL
                        JOIN "usr_friend" f ON ((f.usr1 = ? AND f.usr2 = vf.owner) OR (f.usr1 = vf.owner AND f.usr2 = ?))
                    WHERE va.accesstype = 'friends'
                        AND (va.startdate IS NULL OR va.startdate < current_timestamp)
                        AND (va.stopdate IS NULL OR va.stopdate > current_timestamp)
                 UNION -- group access
                    SELECT va.view
                    FROM "view_access" va
                        JOIN "group_member" m ON va.group = m.group AND (va.role = m.role OR va.role IS NULL)
                    WHERE
                        m.member = ?
                        AND (va.startdate IS NULL OR va.startdate < current_timestamp)
                        AND (va.stopdate IS NULL OR va.stopdate > current_timestamp)
                ))))
")

Mahara Bot (dev-mahara) wrote :

Patch for "master" branch: https://reviews.mahara.org/6352

Robert Lyon (robertl-9) on 2016-04-18
Changed in mahara:
importance: Undecided → High
status: New → Confirmed
status: Confirmed → In Progress
milestone: none → 16.10.0

Reviewed: https://reviews.mahara.org/6352
Committed: https://git.mahara.org/mahara/mahara/commit/65c21985055662cdaa71fed5c66c273c28a356ae
Submitter: Robert Lyon (<email address hidden>)
Branch: master

commit 65c21985055662cdaa71fed5c66c273c28a356ae
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: I5097154d939bf7ddba01d5845af7e8cbb42681b8
Signed-off-by: Tobias Zeuch <email address hidden>

Reviewed: https://reviews.mahara.org/6363
Committed: https://git.mahara.org/mahara/mahara/commit/fc43b4be4851f8391f22ec820aff49aba722a460
Submitter: Robert Lyon (<email address hidden>)
Branch: 16.04_STABLE

commit fc43b4be4851f8391f22ec820aff49aba722a460
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: I5097154d939bf7ddba01d5845af7e8cbb42681b8
Signed-off-by: Tobias Zeuch <email address hidden>
(cherry picked from commit 65c21985055662cdaa71fed5c66c273c28a356ae)

Mahara Bot (dev-mahara) wrote :

Reviewed: https://reviews.mahara.org/6351
Committed: https://git.mahara.org/mahara/mahara/commit/504d8270fc79773108fa986a1a84b1b7c6e470da
Submitter: Robert Lyon (<email address hidden>)
Branch: 15.10_STABLE

commit 504d8270fc79773108fa986a1a84b1b7c6e470da
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: I5097154d939bf7ddba01d5845af7e8cbb42681b8
Signed-off-by: Tobias Zeuch <email address hidden>
(cherry picked from commit 65c21985055662cdaa71fed5c66c273c28a356ae)

Robert Lyon (robertl-9) on 2016-10-21
Changed in mahara:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Duplicates of this bug

Other bug subscribers