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

One strange query...breaks Oracle

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.7
    • Fix Version/s: 1.7
    • Component/s: Forum
    • Labels:
      None
    • Environment:
      Any, with Oracle
    • Database:
      PostgreSQL, Microsoft SQL, Oracle
    • Affected Branches:
      MOODLE_17_STABLE
    • Fixed Branches:
      MOODLE_17_STABLE

      Description

      I was testing 1.7 agaisnt different DBs when I've found this query:

      SELECT DISTINCT(p.id), p.*, d.forum, u.firstname, u.lastname, u.email, u.picture
      FROM m_forum_posts p, m_forum_discussions d, m_user u
      WHERE ... ... ...
      ORDER BY p.modified DESC

      The list of fields in the SELECT clause seems to be really crazy, with one DISTINCT being applied to only one field (p.id) and, then, again, all the fields of p (p.*).

      I'm pretty sure that such syntax isn't correct at all. I only have tested it against Oracle and ir crash! My suggestion, if there isn't any good reason for the current syntax is to simply change it to:

      SELECT p.*, d.forum, u.firstname, u.lastname, u.email, u.picture
      FROM m_forum_posts p, m_forum_discussions d, m_user u
      WHERE ... ... ...
      ORDER BY p.modified DESC

      Not need to specify the DISTINCT at all, because ADOdb will guarantee unique values for the 1st field on each query.

        Attachments

          Activity

            People

            • Assignee:
              dougiamas Martin Dougiamas
              Reporter:
              stronk7 Eloy Lafuente (stronk7)
              Tester:
              Nobody
              Participants:
              Component watchers:
              Andrew Nicols, Mathew May, Michael Hawkins, Shamim Rezaie, Simey Lameze
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:
                Fix Release Date:
                7/Nov/06