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

Review and fix some obvious SELECT DISTINCT + TEXT column

    XMLWordPrintable

Details

    • Microsoft SQL, Oracle
    • MOODLE_19_STABLE, MOODLE_20_STABLE
    • MOODLE_19_STABLE, MOODLE_20_STABLE
    • Moderate

    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

              stronk7 Eloy Lafuente (stronk7)
              stronk7 Eloy Lafuente (stronk7)
              David Mudrák (@mudrd8mz) David Mudrák (@mudrd8mz)
              David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo, David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                21/Feb/11