title: Error with ORDER BY clause using UNION in MySQL
body: <p>I have the following query in MySQL:</p>
  
  <pre><code>(SELECT ue.id, ue.userid, ue.status, ue.timestart, ue.timeend, e.courseid,
          e.id AS enrolid, ra.roleid
   FROM user_enrolments ue
   JOIN enrol e ON e.id = ue.enrolid
   JOIN course c ON c.id = e.courseid
   JOIN user u ON u.id = ue.userid
   JOIN context ct ON ct.instanceid = c.id
   LEFT JOIN role_assignments ra ON ra.userid = u.id AND 
                                 ra.contextid = ct.id AND
                                 ra.itemid = e.id
   WHERE e.customint1 = 1 AND u.deleted = 0 AND 
         ct.contextlevel = 50 AND (ue.status = 0 OR ue.status = 1)) 
  
  UNION
  
  (SELECT de.enrolid AS id, de.userid, de.status, de.date_ini, de.date_fin, 
          de.courseid, de.enrolid, de.roleid
   FROM deleted_enrols de
   JOIN user u ON u.id = de.userid
   WHERE userid = ANY (SELECT userid FROM local_users WHERE clientid = 1))
  
  ORDER BY u.firstname, u.lastname, c.fullname LIMIT 0, 100
  </code></pre>
  
  <p>If I delete ORBER BY and LIMIT, this query works fine... but the ORDER BY clause gives an error:</p>
  
  <pre><code>Table 'u' from one of the SELECTs cannot be used in global ORDER clause
  </code></pre>
  
  <p>If I delete the parentheses of both SELECT querys, the error is different:</p>
  
  <pre><code>Table 'u' from one of the SELECTs cannot be used in field list
  </code></pre>
  
  <p>I have also tried with UNION ALL, but it does not work either.</p>
  
  <p>Any suggestion or clue? Thanks in advance for your time...</p>