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

Lesson query fails because of annoying DML limitation

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 2.0
    • Fix Version/s: 2.0
    • Component/s: Lesson
    • Labels:
      None
    • Database:
      Any
    • Affected Branches:
      MOODLE_20_STABLE
    • Fixed Branches:
      MOODLE_20_STABLE

      Description

      Currently lesson is failing in function lesson_get_user_grades because:

      [[invalidqueryparam]]

      • line 364 of lib/dml/moodle_database.php: call to print_error()
      • line 397 of lib/dml/adodb_moodle_database.php: call to moodle_database->fix_sql_params()
      • line 386 of mod/lesson/lib.php: call to adodb_moodle_database->get_records_sql()
      • line 402 of mod/lesson/lib.php: call to lesson_get_user_grades()
      • line 78 of mod/lesson/lib.php: call to lesson_update_grades()
      • line 268 of course/modedit.php: call to lesson_update_instance()

      I tracked this down and got the following:

      SQL

      SELECT u.id, u.id AS userid, g.grade AS rawgrade
      FROM coremdl_user u, coremdl_lesson_grades g, (SELECT uu.id AS userid, MIN(gg.id) AS firstcompleted
      FROM coremdl_user uu, coremdl_lesson_grades gg
      WHERE uu.id = gg.userid AND gg.lessonid = :lessonid
      AND uu.id = :userid
      GROUP BY uu.id) f
      WHERE u.id = g.userid AND g.lessonid = :lessonid
      AND g.id = f.firstcompleted AND g.userid=f.userid
      AND u.id = :userid

      params

      Array
      (
      [lessonid] => 1
      [userid] => 0
      )

      As far as I can see this SQL and param list is correct. However it appears that at present it is not possible to use the same parameter more than once This means that either:

      a) Somebody needs to fix lesson to call the other parameter :lessonid2, :userid2

      or, far far far preferably,

      b) Somebody needs to fix dmllib so that fix_sql_params and related code actually supports multiple uses of the same param

      This code is in moodle_database.php so I don't think it is database-dependent (but I am testing using Postgres if that matters).

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                quen Sam Marshall
                Reporter:
                quen Sam Marshall
                Participants:
                Component watchers:
                Adrian Greeve, Mihail Geshoski, Peter Dias
              • Votes:
                0 Vote for this issue
                Watchers:
                0 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  24/Nov/10