mahara-contributors team mailing list archive

[Mahara Bot <1571421@xxxxxxxxxxxxxxxxxx>]

Reviewed:  https://reviews.mahara.org/6352
Committed: https://git.mahara.org/mahara/mahara/commit/65c21985055662cdaa71fed5c66c273c28a356ae
Submitter: Robert Lyon (robertl@xxxxxxxxxxxxxxx)
Branch:    master

commit 65c21985055662cdaa71fed5c66c273c28a356ae
Author: Tobias Zeuch <tobias.zeuch@xxxxxxxxxxxxxx>
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 <tobias.zeuch@xxxxxxxxxxxxxx>

-- 
You received this bug notification because you are a member of Mahara
Contributors, which is subscribed to Mahara.
Matching subscriptions: Subscription for all Mahara Contributors -- please ask on #mahara-dev or mahara.org forum before editing or unsubscribing it!
https://bugs.launchpad.net/bugs/1571421

Title:
  Illegal join expression when searching shared-with-me

Status in Mahara:
  Fix Committed
Status in Mahara 15.10 series:
  In Progress
Status in Mahara 16.04 series:
  Fix Committed
Status in Mahara 16.10 series:
  Fix Committed

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)
                  ))))
  ")

To manage notifications about this bug go to:
https://bugs.launchpad.net/mahara/+bug/1571421/+subscriptions