mahara-contributors team mailing list archive

[Mahara Bot <1898650@xxxxxxxxxxxxxxxxxx>]

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

-- 
You received this bug notification because you are a member of Mahara
Contributors, which is subscribed to Mahara.
Matching subscriptions: mahara-contributors
https://bugs.launchpad.net/bugs/1898650

Title:
  Duplicate Collection View Displayorder

Status in Mahara:
  Fix Released

Bug description:
  Mahara: 19.10.04  ( 2019093018 )
  OS: Linux 16.04 (PHP 7.0)
  DB: Postgres
  Browser: FF / Chrome

  
  In certain situations, the users are able to create a collection where the displayorder of the pages are duplicated. i.e. 2 pages with displayorder = 0 in the collection_view table.

  I have no idea how this situation came about. I only saw it in one of
  our production sites.

  For 1 collection with 37 pages, there were:

  * 2 pages with displayorder = 0
  * 2 pages with displayorder = 1

  The remaining pages were ordered correctly starting from 2 onwards.

  The result was the error:

  'A nonrecoverable error occurred. This probably means you have
  encountered a bug in the system'

  When trying to go to the Pages and Collections screen.

  The offending subquery which returned multiple records for a column is
  in lib/view.php::get_myviews_data() line 3804:

          $collselect = '
              UNION
              SELECT (SELECT view FROM {collection_view} cvid WHERE cvid.collection = c.id AND displayorder = 0) as id,
              null AS vid, c.name as title, c.name AS vtitle, c.description, null as type, c.ctime as vctime, c.mtime as vmtime, c.mtime as vatime,
              c.owner, c.group, c.institution, null as locked, null as ownerformat, null as urlid, null AS vvisits,
                     (SELECT COUNT(*) FROM {collection_view} cv WHERE cv.collection = c.id) AS numviews, c.id AS collid';

  
  As a side note, we really should not be calling subqueries for a column. This has huge performance issues since that subquery will be run for every row.

  I also note that there is another subquery to retrieve 'numviews'.
  Again, another performance hit.

  
  To eliminate the performance hit, the following should be changed:

          $collselect = '
              UNION
              SELECT cvid.view as id,
              null AS vid, c.name as title, c.name AS vtitle, c.description, null as type, c.ctime as vctime, c.mtime as vmtime, c.mtime as vatime,
              c.owner, c.group, c.institution, null as locked, null as ownerformat, null as urlid, null AS vvisits,
              numviews.numviews AS numviews, c.id AS collid';


          $collfrom = '
              FROM {view} v
              LEFT OUTER JOIN {collection_view} cv ON cv.view = v.id
              LEFT OUTER JOIN {collection} c ON cv.collection = c.id
              INNER join {collection_view} cvid on cvid.collection = c.id AND cvid.displayorder = 0
              INNER JOIN (SELECT COUNT(*) as numviews, numcv.collection 
                      FROM {collection_view} numcv
                      INNER JOIN {collection} numc ON numcv.collection = numc.id
                      GROUP BY numcv.collection) as numviews on numviews.collection = cv.collection';

  
  and under 'mostcomments':

  $collgroupby = ' GROUP BY cvid.view, numviews.numviews, c.id';

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