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

Some DISTINCT clauses break MSSQL and Oracle in database activity

    XMLWordPrintable

    Details

    • Affected Branches:
      MOODLE_18_STABLE, MOODLE_19_STABLE
    • Fixed Branches:
      MOODLE_18_STABLE, MOODLE_19_STABLE

      Description

      I was testing MDL-12893 under 1.8 when I discovered a bunch of

      SELECT DISTINCT ... clauses breaking both MSSQL and Oracle when sorting was specified.

      Both MSSQL and Oracle doesn't allow to specify such DISTINCT clause when the list of fields contain one TEXT (CLOB) field.

      Looking at code I've found:

      • 3 occurrences in 18_STABLE view.php
      • 2 occurrences in 19_STABLE view.php
      • 2 occurrences in HEAD view.php

      IMO, there are 3 ways to fix this:

      1) Analyse if we really need that DISTINCT clause in those queries. Take it out if not needed.
      2) Analyse if we need to fetch the TEXT (CLOB) field really from DD. Take it out if not needed.
      3) Build one subselect getting the TEXT field in the outer query, once everything has been executed in the inner one.

      I've tried directly the 1st alternative (dropping the DISTINCT clause) and seems to work here in my basic tests under MSSQL.

      I guess we could implement the 2nd alternative too (saving some TEXT traffic between DB and PHP...

      And finally, the 3rd alternative should be only used if both the 2 above aren't ok.

      For your consideration... my +1 to implement 1) and 2) if possible. From 18_STABLE to HEAD.

      Ciao

        Attachments

          Activity

            People

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

              Dates

              • Created:
                Updated:
                Resolved:
                Fix Release Date:
                13/May/09