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

Applying the same condition/filter to custom report can throw DML exceptions


      This was reported by a parter in Workplace (WP-4710), but needs fixing in Moodle. To quote myself from that ticket:

      Having the same field as both a condition and a filter in itself is not normally a problem, in 99% of the time it'll work fine. Unfortunately you seem to have uncovered the 1% where it's a problem - when the filter contains parameters in it's definition (as the Datastore course > Course fullname filter does) they become duplicated in the report query when both the condition and the filter are active at the same time - so we end up with fewer parameters than expected as reported by the exception (really it's a limitation of our DML...)

      The key points are thus:

      1. The filter instance provides SQL parameters;
      2. The filter instance is used as both a condition and a filter in the same report;
      3. The filter and condition are both active at the same time

      Out of the +100 filters currently in Moodle, only 1 of them provides SQL parameters (we have more in Workplace): the badge expiry filter added in MDL-81073. That this filter exists only from 4.4 onwards presents a problem for reproduction and testing in stable branches where we also need to fix the same

      We have a method already that should allow us to fix this: database::sql_replace_parameter_names(...) from MDL-77555

      To reproduce create the following report:

      Then apply the filter on preview:

      Stacktrace follows:

      ERROR: Incorrect number of query parameters. Expected 7, got 5.
      File: /lib/dml/moodle_database.php
      Line: 958
      Stack trace:
      Error code: invalidqueryparam
      * line 958 of /lib/dml/moodle_database.php: dml_exception thrown
      * line 1393 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->fix_sql_params()
      * line 1684 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->get_records_sql()
      * line 1757 of /lib/dml/moodle_database.php: call to moodle_database->get_record_sql()
      * line 1982 of /lib/dml/moodle_database.php: call to moodle_database->get_field_sql()
      * line 177 of /reportbuilder/classes/table/base_report_table.php: call to moodle_database->count_records_sql()
      * line 374 of /reportbuilder/classes/table/custom_report_table.php: call to core_reportbuilder\table\base_report_table->query_db()
      * line 261 of /lib/table/classes/external/dynamic/get.php: call to core_reportbuilder\table\custom_report_table->out()
      * line ? of unknownfile: call to core_table\external\dynamic\get::execute()
      * line 253 of /lib/external/classes/external_api.php: call to call_user_func_array()
      * line 83 of /lib/ajax/service.php: call to core_external\external_api::call_external_function()

            pholden Paul Holden
            pholden Paul Holden
            Carlos Castillo Carlos Castillo
            David Carrillo David Carrillo
            CiBoT CiBoT
            0 Vote for this issue
            4 Start watching this issue


                Original Estimate - Not Specified
                Not Specified
                Remaining Estimate - 0 minutes
                Time Spent - 5 hours, 15 minutes
                5h 15m

                  Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.