Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-7840 recordset_to_array() returns incomplete results with certain JOIN queries
  3. MDL-10787

get_records_sql() with GROUP BY still returns incomplete result sets

    XMLWordPrintable

    Details

    • Type: Sub-task
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.9
    • Fix Version/s: 1.8.3, 1.9
    • Component/s: Database SQL/XMLDB
    • Labels:
      None
    • Environment:
      Windows Vista Business
      Apache 2.2.4
      MySQL 5.0.37
      PHP 5.2.1
    • Database:
      MySQL
    • Affected Branches:
      MOODLE_19_STABLE
    • Fixed Branches:
      MOODLE_18_STABLE, MOODLE_19_STABLE

      Description

      The problem described in issue MDL-7840 still exists in some special cases.
      Resolving MDL-7840 as "no bug" does not solve the problem because in many cases the function recordset_to_array() ist still used with non-unique keys in some files. It is still hard (if not impossible) to avoid using GROUP BY or to introduce unique keys in many cases.

      For example, see mod/survey/lib.php:

      SELECT MAX(a.time) as time,
      u.id, u.firstname, u.lastname, u.picture
      FROM {$CFG->prefix}survey_answers a,
      {$CFG->prefix}user u $groupsdb
      WHERE a.survey = $surveyid
      AND a.userid = u.id $groupsql
      GROUP BY u.id, u.firstname, u.lastname, u.picture
      ORDER BY time ASC

      If 2 users posts their answer at the same time (the timestamp is equal) it is still possible to get an equal MAX(a.time) resulting in records lost (only in some cases due to the complex WHERE clause -> unluckily, you it is difficult to reproduce this problem until it appears ).

      I found a similar problem today in another file, so I expect that there will be many more files with GROUP-BY clauses using non-unique keys. In my opinion, the recordset_to_array() function SHOULD BE CHANGED to support more than one identical value for the first column. For this reason issue MDL-7840 may be reopened because it still produces errors in some cases (and therefore it is a bug!!!).
      As an alternative for changing recordset_to_array(), ALL used GROUP-BYs in the code must be checked if their first column is really a unique key.

      The workaround by using get_recordset_sql() instead of get_records_sql() does also not work in all cases (there seems to be some problems with non-MySQL databases, but I cannot reproduce this on my system since i do not have an Oracle installation here).

        Attachments

          Activity

            People

            Assignee:
            stronk7 Eloy Lafuente (stronk7)
            Reporter:
            gesa Gert Sauerstein
            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:
              Fix Release Date:
              11/Oct/07