Moodle
  1. Moodle
  2. MDL-26371

Review and fix some obvious SELECT DISTINCT + TEXT column

    Details

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

      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

        Issue Links

          Activity

          Hide
          Eloy Lafuente (stronk7) added a comment -

          Finished the 20_STABLE review. Only these need fixing:

          • enrol/category
          • mod/glossary
          • mod/lesson

          (the rest aren't problematic as far as the tables don't contain text columns).

          Created PULL-282 for integration into 2.0.

          Going to work on 1.9 now.

          Ciao

          Show
          Eloy Lafuente (stronk7) added a comment - Finished the 20_STABLE review. Only these need fixing: enrol/category mod/glossary mod/lesson (the rest aren't problematic as far as the tables don't contain text columns). Created PULL-282 for integration into 2.0. Going to work on 1.9 now. Ciao
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Finished the 19_STABLE review. Only these need fixing:

          • admin/report/security: 2 wrong uses of distinct + text and another detected problem with incorrect order by in count statement.
          • mod/lesson

          (the rest aren't problematic as far as the tables don't contain text columns).

          Created PULL-287 for integration into 1.9

          Ciao

          Show
          Eloy Lafuente (stronk7) added a comment - Finished the 19_STABLE review. Only these need fixing: admin/report/security: 2 wrong uses of distinct + text and another detected problem with incorrect order by in count statement. mod/lesson (the rest aren't problematic as far as the tables don't contain text columns). Created PULL-287 for integration into 1.9 Ciao
          Hide
          Eloy Lafuente (stronk7) added a comment -

          As far as the pull requests are already sent and all the subtasks have been closed/resolved, resolving this.

          Ciao

          Show
          Eloy Lafuente (stronk7) added a comment - As far as the pull requests are already sent and all the subtasks have been closed/resolved, resolving this. Ciao
          Hide
          David Mudrak added a comment -

          Both pull requests passed tests on MSSQL 2008 R2 Express edition with mssql_n (1.9) and mssql (2.0) drivers.

          Show
          David Mudrak added a comment - Both pull requests passed tests on MSSQL 2008 R2 Express edition with mssql_n (1.9) and mssql (2.0) drivers.

            People

            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: