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

recordset_to_array() returns incomplete results with certain JOIN queries

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Not a bug
    • Affects Version/s: 1.7
    • Fix Version/s: None
    • Component/s: Database SQL/XMLDB
    • Labels:
      None
    • Environment:
      Tested on Windows XP SP2, Apache 2.2.2, MySQL 5.0.21, PHP 5.1.4.
    • Affected Branches:
      MOODLE_17_STABLE

      Description

      When using any function that invokes recordset_to_array() wherin the SQL statement includes a 1 to many join, if a field from the 'many' side is not the first field in the SQL Select then the data is effectively filtered to 1 row per the '1' side.

      An example: for the following outputs we should expect the same rowcount for all results, namely:
      SELECT COUNT FROM mdl_users u INNER JOIN mdl_role_assignments ra on u.id = ra.userid;

      but $vx and $ra return essentially
      SELECT COUNT FROM mdl_users;

      The culprit in both cases is recordset_to_array(). I believe it has to do with the ADOdb_fetch_mode issues at http://docs.moodle.org/en/XMLDB_Problems but I can't quite put my finger on how to fix it.

      Here's an example to reproduce the issue:
      Create a PHP Page in the moodle root folder and paste the following:

      /**start test code****************************/
      require_once("../config.php");

      $vx = get_records_sql("SELECT u.username, ra.id FROM mdl_user u INNER JOIN mdl_role_assignments ra on u.id = ra.userid; ");
      $vy = get_records_sql("SELECT ra.id, u.username FROM mdl_user u INNER JOIN mdl_role_assignments ra on u.id = ra.userid; ");
      $vz = get_recordset_sql("SELECT ra.id, u.username FROM mdl_user u INNER JOIN mdl_role_assignments ra on u.id = ra.userid; ");
      $va = get_recordset_sql("SELECT u.username, ra.id FROM mdl_user u INNER JOIN mdl_role_assignments ra on u.id = ra.userid; ");

      $rz = recordset_to_array($vz);
      $ra = recordset_to_array($va);

      print '$vx count is '.count($vx).'<br>';
      print '$vy count is '.count($vy).'<br>';
      print '$vz count is '.$vz->_numOfRows.'<br>';
      print '$rz count is '.count($rz).'<br>';
      print '$va count is '.$va->_numOfRows.'<br>';
      print '$ra count is '.count($ra);
      /******end of test code***********************************/

      When you run the code you'll see that the record count returned is in line with the SELECT statements above.

        Attachments

          Activity

            People

            Assignee:
            skodak Petr Skoda
            Reporter:
            greglyon Greg Lyon
            Tester:
            Nobody
            Participants:
            Component watchers:
            Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: