Uploaded image for project: '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

      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

        Gliffy Diagrams

          Issue Links

            Activity

            Hide
            stronk7 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
            stronk7 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
            stronk7 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
            stronk7 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
            stronk7 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
            stronk7 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
            mudrd8mz 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
            mudrd8mz 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:
                  Fix Release Date:
                  21/Feb/11