Uploaded image for project: 'Plugins'
  1. Plugins
  2. CONTRIB-4417

Filtering: SQL Error when no where-clause in query

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 2.5
    • Fix Version/s: None
    • Labels:
      None
    • Affected Branches:
      MOODLE_25_STABLE

      Description

      Adding a filter to a SQL report causes a SQL error if the query does not have a where clause in it.

      For example, add a "User field search box" filter on the filter tab and at the bottom of your sql (%%FILTER_USERS:ex.lastname%%).

      Sample query:

      SELECT *
      FROM PREFIX_EXPORT ex
      %%FILTER_USERS:ex.lastname%%

      This will cause an error:

      Error reading from database

      More information about this error
      Debug info: ORA-00907: missing right parenthesis
      SELECT *
      FROM (SELECT *
      FROM m_EXPORT ex
      AND ex.lastname LIKE '%Fisher%')
      WHERE rownum <= :o_oracle_num_rows
      [array (
      'o_oracle_num_rows' => 5000,
      )]
      Error code: dmlreadexception
      Stack trace:

      line 423 of /lib/dml/moodle_database.php: dml_read_exception thrown
      line 272 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->query_end()
      line 1088 of /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()
      line 54 of /blocks/configurable_reports/reports/sql/report.class.php: call to oci_native_moodle_database->get_recordset_sql()
      line 86 of /blocks/configurable_reports/reports/sql/report.class.php: call to report_sql->execute_query()
      line 67 of /blocks/configurable_reports/viewreport.php: call to report_sql->create_report()

      A work-around is to add a "WHERE 1=1":

      SELECT *
      FROM PREFIX_EXPORT ex
      WHERE 1=1
      %%FILTER_USERS:ex.lastname%%

        Attachments

          Activity

            People

            • Assignee:
              jleyva Juan Leyva
              Reporter:
              icefresh Michael E
              Participants:
              Component watchers:
              Juan Leyva
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: