Index: accesslib.php
===================================================================
RCS file: /cvsroot/moodle/moodle/lib/accesslib.php,v
retrieving revision 1.421.2.74
diff -u -r1.421.2.74 accesslib.php
--- accesslib.php	18 Aug 2008 22:37:18 -0000	1.421.2.74
+++ accesslib.php	26 Aug 2008 17:38:51 -0000
@@ -972,9 +972,10 @@
         //
         // narrow down where we have the caps to a few contexts
         // this will be a combination of
-        // - categories where we have the rights
-        // - courses    where we have an explicit enrolment OR that have an override
-        // 
+        // - courses    where user has an explicit enrolment
+        // - courses    that have an override (any status) on that capability
+        // - categories where user has the rights (granted status) on that capability
+        //
         $sql = "SELECT ctx.*
                 FROM   {$CFG->prefix}context ctx
                 WHERE  ctx.contextlevel=".CONTEXT_COURSECAT."
@@ -994,7 +995,7 @@
             for ($n=0;$n<$cc;$n++) {
                 $catpaths[$n] = "ctx.path LIKE '{$catpaths[$n]}/%'";
             }
-            $catclause = 'OR (' . implode(' OR ', $catpaths) .')';
+            $catclause = 'WHERE (' . implode(' OR ', $catpaths) .')';
         }
         unset($catpaths);
 
@@ -1002,30 +1003,69 @@
         if ($doanything) {
             $capany = " OR rc.capability='moodle/site:doanything'";
         }
-        //
-        // Note here that we *have* to have the compound clauses
-        // in the LEFT OUTER JOIN condition for them to return NULL
-        // appropriately and narrow things down...
-        //
-        $sql = "SELECT $coursefields,
-                       ctx.id AS ctxid, ctx.path AS ctxpath,
-                       ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel,
-                       cc.path AS categorypath
-                FROM {$CFG->prefix}course c
-                JOIN {$CFG->prefix}course_categories cc
-                  ON c.category=cc.id
-                JOIN {$CFG->prefix}context ctx 
-                  ON (c.id=ctx.instanceid AND ctx.contextlevel=".CONTEXT_COURSE.")
-                LEFT OUTER JOIN {$CFG->prefix}role_assignments ra
-                  ON (ra.contextid=ctx.id AND ra.userid=$userid)
-                LEFT OUTER JOIN {$CFG->prefix}role_capabilities rc
-                  ON (rc.contextid=ctx.id AND (rc.capability='$cap' $capany))
-                WHERE    ra.id IS NOT NULL
-                      OR rc.id IS NOT NULL
-                      $catclause
-                $sort ";
+
+        /// UNION 3 queries:
+        /// - user role assignments in courses
+        /// - user capability (override - any status) in courses
+        /// - user right (granted status) in categories (optionally executed)
+        /// Enclosing the 3-UNION into an inline_view to avoid column names conflict and making the ORDER BY cross-db
+        /// and to allow selection of TEXT columns in the query (MSSQL and Oracle limitation). MDL-16209
+        $sql = "
+            SELECT $coursefields, ctxid, ctxpath, ctxdepth, ctxlevel, categorypath 
+              FROM (
+                    SELECT c.id,
+                           ctx.id AS ctxid, ctx.path AS ctxpath,
+                           ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel,
+                           cc.path AS categorypath
+                    FROM {$CFG->prefix}course c
+                    JOIN {$CFG->prefix}course_categories cc
+                      ON c.category=cc.id
+                    JOIN {$CFG->prefix}context ctx
+                      ON (c.id=ctx.instanceid AND ctx.contextlevel=".CONTEXT_COURSE.")
+                    JOIN {$CFG->prefix}role_assignments ra
+                      ON (ra.contextid=ctx.id AND ra.userid=$userid)
+                    UNION
+                    SELECT c.id,
+                           ctx.id AS ctxid, ctx.path AS ctxpath,
+                           ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel,
+                           cc.path AS categorypath
+                    FROM {$CFG->prefix}course c
+                    JOIN {$CFG->prefix}course_categories cc
+                      ON c.category=cc.id
+                    JOIN {$CFG->prefix}context ctx
+                      ON (c.id=ctx.instanceid AND ctx.contextlevel=".CONTEXT_COURSE.")
+                    JOIN {$CFG->prefix}role_capabilities rc
+                      ON (rc.contextid=ctx.id AND (rc.capability='$cap' $capany)) ";
+
+        if (!empty($catclause)) { /// If we have found the right in categories, add child courses here too
+            $sql .= "
+                    UNION
+                    SELECT c.id,
+                           ctx.id AS ctxid, ctx.path AS ctxpath,
+                           ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel,
+                           cc.path AS categorypath
+                    FROM {$CFG->prefix}course c
+                    JOIN {$CFG->prefix}course_categories cc
+                      ON c.category=cc.id
+                    JOIN {$CFG->prefix}context ctx
+                      ON (c.id=ctx.instanceid AND ctx.contextlevel=".CONTEXT_COURSE.")
+                    $catclause";
+        }
+
+    /// Close the inline_view and join with courses table to get requested $coursefields
+        $sql .= "
+                ) inline_view
+                INNER JOIN {$CFG->prefix}course c
+                    ON inline_view.id = c.id";
+
+    /// To keep cross-db we need to strip any prefix in the ORDER BY clause for queries using UNION
+        $sql .= "
+                " . preg_replace('/[a-z]+\./i', '', $sort); /// Add ORDER BY clause
+
         $rs = get_recordset_sql($sql);
     }
+
+/// Confirm rights (granted capability) for each course returned
     $courses = array();
     $cc = 0; // keep count
     while ($c = rs_fetch_next_record($rs)) {