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 Sub-task
    • Status: Closed
    • Priority: Major 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
    • Rank:
      32724

      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";
      

        Activity

        Hide
        Michael de Raadt added a comment -

        Thanks for reporting that and providing a solution.

        Show
        Michael de Raadt added a comment - Thanks for reporting that and providing a solution.
        Hide
        David Monllaó added a comment -

        Eskerrik asko for the report and patch Iñigo, the issue is still present in master; updating with pull branches and submitting for peer review.

        Tested with an Oracle 10g database

        Show
        David Monllaó added a comment - Eskerrik asko for the report and patch Iñigo, the issue is still present in master; updating with pull branches and submitting for peer review. Tested with an Oracle 10g database
        Hide
        Andrew Davis added a comment - - edited

        The code change looks fine. Not sure how that duplicate got in there in the first place.

        If you wouldn't mind also include doing a regular search through a regular forum (no URL manipulation) in the testing instructions in addition to what you currently have. I don't see how it could NOT work given how trivial the change is but it doesn't hurt to double check that the regular normal behaviour works as well as the exceptional.

        Show
        Andrew Davis added a comment - - edited The code change looks fine. Not sure how that duplicate got in there in the first place. If you wouldn't mind also include doing a regular search through a regular forum (no URL manipulation) in the testing instructions in addition to what you currently have. I don't see how it could NOT work given how trivial the change is but it doesn't hurt to double check that the regular normal behaviour works as well as the exceptional.
        Hide
        David Monllaó added a comment -

        Thanks for the comments Andrew, I replaced the URL manipulation step for the regular access to the forums search (internally is the same, also the forum search block) and added an Oracle DB as a requirement to test the issue.

        Show
        David Monllaó added a comment - Thanks for the comments Andrew, I replaced the URL manipulation step for the regular access to the forums search (internally is the same, also the forum search block) and added an Oracle DB as a requirement to test the issue.
        Hide
        Dan Poltawski added a comment -

        I've updated the testing instructions to include a test on mssql, because I think its worthwhile testing there too.

        Show
        Dan Poltawski added a comment - I've updated the testing instructions to include a test on mssql, because I think its worthwhile testing there too.
        Hide
        Dan Poltawski added a comment -

        Thanks David, i've integrated this to 22, 23 and master.

        Show
        Dan Poltawski added a comment - Thanks David, i've integrated this to 22, 23 and master.
        Hide
        Michael de Raadt added a comment -

        Test result: Success!

        Tested in Oracle, MS SQL with 2.2, 2.3 and master. Also tested in PostgreSQL and MySQL with master. I was able to reproduce the error before upgrading in Oracle only. All was good after upgrading.

        Show
        Michael de Raadt added a comment - Test result: Success! Tested in Oracle, MS SQL with 2.2, 2.3 and master. Also tested in PostgreSQL and MySQL with master. I was able to reproduce the error before upgrading in Oracle only. All was good after upgrading.
        Hide
        Eloy Lafuente (stronk7) added a comment -

        YEAR!*

        CAF*, TOT!*

        • Your effort amazingly resulted. (unbelievable :-P)
        • Closing as fixed.
        • Tons of thanks.
        Show
        Eloy Lafuente (stronk7) added a comment - YEAR!* CAF*, TOT!* Your effort amazingly resulted. (unbelievable :-P) Closing as fixed. Tons of thanks.

          People

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

            Dates

            • Created:
              Updated:
              Resolved: