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

Review and fix some obvious SELECT DISTINCT + TEXT column

    XMLWordPrintable

    Details

    • Database:
      Microsoft SQL, Oracle
    • Difficulty:
      Moderate
    • Affected Branches:
      MOODLE_19_STABLE, MOODLE_20_STABLE
    • Fixed Branches:
      MOODLE_19_STABLE, MOODLE_20_STABLE

      Description

      One of the most recurring errors when achieving cross-db support is the use of "SELECT DISTINCT" queries retrieving TEXT columns. This issue is about to find / review / fix the most common ones.

      Results of the search in 19_STABLE:

      admin/report/security/lib.php:1116:        "SELECT DISTINCT r.*
      admin/report/security/lib.php:1122:        "SELECT DISTINCT r.*, rc.contextid
      blocks/search_books/search_books.php:106:    $sqlselect  = "SELECT DISTINCT bc.*";
      blocks/search_glossaries/search_glossaries.php:112:        $sqlselect  = "SELECT DISTINCT ge.*, ge.concept";
      lib/db/mysql.php:1790:        $rs = get_recordset_sql('SELECT DISTINCT * FROM '.$CFG->prefix.'log_display');
      lib/db/postgres7.php:1487:        $rs = get_recordset_sql('SELECT DISTINCT * FROM '.$CFG->prefix.'log_display');
      mod/forum/lib.php:2042:    return get_records_sql("SELECT DISTINCT d.*
      mod/lesson/essay.php:34:                        $sql = "SELECT DISTINCT u.*
      mod/lesson/report.php:22:        $sql = "SELECT DISTINCT u.*

      Results in 20_STABLE:

      enrol/category/locallib.php:266:    $sql = "SELECT DISTINCT c.*
      mod/feedback/lib.php:2214:    $sql =  "SELECT DISTINCT fc.*
      mod/feedback/lib.php:2257:            $query = "SELECT DISTINCT fbc.*
      mod/forum/lib.php:2189:    return $DB->get_records_sql("SELECT DISTINCT d.*
      mod/glossary/sql.php:206:                $sqlselect  = "SELECT DISTINCT ge.*, ge.concept AS glossarypivot";
      mod/lesson/essay.php:138:            if (!$users = $DB->get_records_sql("SELECT DISTINCT u.id, u.*
      mod/lesson/report.php:47:    $sql = "SELECT DISTINCT u.id, u.*
      mod/lesson/report.php:55:    $sql = "SELECT DISTINCT u.id, u.*

      Surely there are more... but these are obvious to verify. Going to do so. Ciao

      Some other related bugs... could be these: http://tracker.moodle.org/secure/IssueNavigator.jspa?reset=true&jqlQuery=resolution+%3DUnresolved+AND+%28description+%7E+%22%2BThe+%2Bntext+%2Bdata++%2Btype%22%29

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                stronk7 Eloy Lafuente (stronk7)
                Reporter:
                stronk7 Eloy Lafuente (stronk7)
                Tester:
                David Mudrák (@mudrd8mz)
                Participants:
                Component watchers:
                Jake Dallimore, Jun Pataleta, Ryan Wyllie, Amaia Anabitarte, Bas Brands, Carlos Escobedo, Sara Arjona (@sarjona), Víctor Déniz Falcón
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  21/Feb/11