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.
I neglected to mention that to see the bug using the example I provided ... at least one user must have multiple role assignments (in any context). Otherwise there's no 1->many relationship represented.