Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-30370 Meta: Oracle SQL issues
  3. MDL-30351

Error searching forum posts in Oracle forum_search_posts

    Details

    • Type: Sub-task
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.1.2, 2.1.3, 2.1.4, 2.1.5, 2.1.6, 2.2.3
    • Fix Version/s: 2.2.5, 2.3.2
    • Component/s: Forum
    • Labels:
    • Environment:
      Oracle DB
    • Database:
      Oracle
    • Testing Instructions:
      Hide

      Please test this on AT LEAST Oracle & MSSQL, other dbs if possible.

      1. Go to a course with forums
      2. Click on a forum
      3. In the right-upper part, fill the text form element and click "Search forums"
      4. You SHOULD NOT see any errors
      Show
      Please test this on AT LEAST Oracle & MSSQL, other dbs if possible. Go to a course with forums Click on a forum In the right-upper part, fill the text form element and click "Search forums" You SHOULD NOT see any errors
    • Workaround:
      Hide

      As far as the two columns are the same column (u.email), I figure that this can be solved just removing one of them, just like this:

          $searchsql = "SELECT p.*,
                               d.forum,
                               u.firstname,
                               u.lastname,
                               u.email,
                               u.picture,
                               u.imagealt
                          FROM $fromsql
                         WHERE $selectsql
                      ORDER BY p.modified DESC";
      

      I tried this and worked for me.

      Show
      As far as the two columns are the same column (u.email), I figure that this can be solved just removing one of them, just like this: $searchsql = "SELECT p.*, d.forum, u.firstname, u.lastname, u.email, u.picture, u.imagealt FROM $fromsql WHERE $selectsql ORDER BY p.modified DESC"; I tried this and worked for me.
    • Difficulty:
      Easy
    • Affected Branches:
      MOODLE_21_STABLE, MOODLE_22_STABLE
    • Fixed Branches:
      MOODLE_22_STABLE, MOODLE_23_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      MDL-30370_master

      Description

      When searching forum posts In Oracle it crashes with ORA-00918: column ambiguously defined

      * line 394 of \lib\dml\moodle_database.php: dml_read_exception thrown
      * line 268 of \lib\dml\oci_native_moodle_database.php: call to moodle_database->query_end()
      * line 1093 of \lib\dml\oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()
      * line 2056 of \mod\forum\lib.php: call to oci_native_moodle_database->get_records_sql()
      * line 145 of \mod\forum\search.php: call to forum_search_posts()
      

      The reason is that in /mod/forum/lib.php there is a 'select *' query with a subquery that uses twice the same column name (email):

          $searchsql = "SELECT p.*,
                               d.forum,
                               u.firstname,
                               u.lastname,
                               u.email,
                               u.picture,
                               u.imagealt,
                               u.email
                          FROM $fromsql
                         WHERE $selectsql
                      ORDER BY p.modified DESC";
      

        Gliffy Diagrams

          Attachments

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  10/Sep/12