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

            Assignee:
            stronk7 Eloy Lafuente (stronk7)
            Reporter:
            stronk7 Eloy Lafuente (stronk7)
            Tester:
            Petr Skoda
            Participants:
            Component watchers:
            Adrian Greeve, Jake Dallimore, Mathew May, Mihail Geshoski, Peter Dias, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

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