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

lib/filterlib: ORDER BY into inline views / subqueries problem

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Minor
    • Resolution: Fixed
    • 2.0
    • 2.0
    • Filters, Libraries
    • None
    • MOODLE_20_STABLE
    • MOODLE_20_STABLE
    • Easy

    Description

      Just got SQL error in MSSQL with this query when upgrading to test MDL-18132.

      SELECT active.filter, fc.name, fc.value FROM (SELECT f.filter FROM mdl_filter_active f JOIN mdl_context ctx ON f.contextid = ctx.id WHERE ctx.id IN (1) GROUP BY filter HAVING MAX(f.active * ctx.depth ) > -MIN(f.active * ctx.depth ) ORDER BY MAX(f.sortorder)) active LEFT JOIN mdl_filter_config fc ON fc.filter = active.filter AND fc.contextid = 1
      [array ( )]
      line 305 of lib/dml/moodle_database.php: dml_read_exception thrown
      line 385 of lib/dml/adodb_moodle_database.php: call to moodle_database->query_end()
      line 676 of lib/filterlib.php: call to adodb_moodle_database->get_recordset_sql()
      line 84 of lib/filterlib.php: call to filter_get_active_in_context()
      line 138 of lib/filterlib.php: call to filter_manager->load_filters()
      line 162 of lib/filterlib.php: call to filter_manager->get_text_filters()
      line 1268 of lib/weblib.php: call to filter_manager->text_filtering_hash()
      line 98 of index.php: call to format_text()

      IMO, ORDER BY clauses within subqueries / inline views aren't cross-db at all (in fact I think they should break in ALL DBs). The order of one subquery cannot affect the recordset returned. Taking it out from the query should do the trick.

      Ciao

      Attachments

        Issue Links

          Activity

            People

              timhunt Tim Hunt
              stronk7 Eloy Lafuente (stronk7)
              Eloy Lafuente (stronk7) Eloy Lafuente (stronk7)
              Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Ilya Tregubov, Sara Arjona (@sarjona), Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Ilya Tregubov, Sara Arjona (@sarjona)
              Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                24/Nov/10