Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-25321

sqlsrv_native's limit_to_top_n function destroys queries completely!

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: 2.0
    • Fix Version/s: 2.0.2
    • Component/s: Database SQL/XMLDB
    • Labels:
    • Database:
      Microsoft SQL
    • Difficulty:
      Moderate
    • Affected Branches:
      MOODLE_20_STABLE
    • Fixed Branches:
      MOODLE_20_STABLE

      Description

      Hi guys,

      I picked this up the other day, the sqlsrv_native limit_to_top_n function is just destroying SQL queries and just presenting mangled crap.
      A simple way to reproduce this is to create a tet.php file with the following contents:

      require_once('config.php');
       
      $sql = 'SELECT c.id, c.shortname
              FROM {course} c
              WHERE c.id NOT IN (
                  SELECT cm.course 
                  FROM {course_modules} cm 
                  JOIN {modules} m ON m.id = cm.module
                  WHERE m.name = \'wiki\'
              ) ORDER BY c.shortname ASC';
      $courses = $DB->get_records_sql($sql, null, 5, 5);
       
      echo "<pre>";
      print_r($courses);
      echo "</pre>";

      I've also attached a screenshot of what is happening with two queries one that selects the course id and shortname for all courses with limit 5, offset 5 and the second that does that same thing for all courses without a wiki activity.
      The function is returning all columns for the orginal from table and drop all where, group by, and order by statement segments.

      Let me know if there is any more information I can provide.

      Cheers
      Sam

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              samhemelryk Sam Hemelryk
              Reporter:
              samhemelryk Sam Hemelryk
              Participants:
              Component watchers:
              Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              3 Vote for this issue
              Watchers:
              6 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                21/Feb/11