Moodle
  1. Moodle
  2. MDL-34192

ORA-00918: column ambiguously defined when access to grading in Assign module

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Blocker Blocker
    • Resolution: Fixed
    • Affects Version/s: 2.3, 2.4
    • Fix Version/s: 2.3.2
    • Component/s: Assignment
    • Labels:
    • Environment:
      Ubuntu Server 11.10 32 bits
      PHP : 5.3.6
      BD : Oracle 10.2.0.1.0
    • Database:
      Oracle
    • Testing Instructions:
      Hide

      Important note to testers:
      Due to some regressions caused by MDL-31341, this only can be tested under 23_STABLE. Fix is 100% the same for master so it should be enough and will work the same once master is fixed (by MDL-35004).

      Pre-requisites:
      Moodle installed with Oracle database & developer debug enabled.

      Go to "View/grade all submissions" page in new assignment (assign) module.
      Verify you do not see the error described in this bug.

      Show
      Important note to testers: Due to some regressions caused by MDL-31341 , this only can be tested under 23_STABLE. Fix is 100% the same for master so it should be enough and will work the same once master is fixed (by MDL-35004 ). Pre-requisites: Moodle installed with Oracle database & developer debug enabled. Go to "View/grade all submissions" page in new assignment (assign) module. Verify you do not see the error described in this bug.
    • Workaround:
      Hide

      Use a different database

      Show
      Use a different database
    • Affected Branches:
      MOODLE_23_STABLE, MOODLE_24_STABLE
    • Fixed Branches:
      MOODLE_23_STABLE
    • Pull Master Branch:
    • Rank:
      42530

      Description

      When a theacher push in link "View/grade all submissions" in a instance of a new assignment, see these error :

      Example link : "..mod/assign/view.php?id=2&action=grading"

      Debug info: ORA-00918: column ambiguously defined
      SELECT *
      FROM (SELECT
      u.id,u.picture,u.firstname,u.lastname,u.imagealt,u.email, u.id as userid, u.firstname as firstname, u.lastname as lastname, s.status as status, s.id as submissionid, s.timecreated as firstsubmission, s.timemodified as timesubmitted, g.id as gradeid, g.grade as grade, g.timemodified as timemarked, g.timecreated as firstmarked, g.mailed as mailed, g.locked as locked
      FROM m_user u LEFT JOIN m_assign_submission s ON u.id = s.userid AND s.assignment = :o_assignmentid1 LEFT JOIN m_assign_grades g ON u.id = g.userid AND g.assignment = :o_assignmentid2
      WHERE u.id IN (:o_user15,:o_user16,:o_user17,:o_user18,:o_user19,:o_user20,:o_user21,:o_user22)
      )
      WHERE rownum <= :o_oracle_num_rows
      [array (
      'o_assignmentid1' => 1,
      'o_assignmentid2' => 1,
      'o_user15' => 21,
      'o_user16' => 22,
      'o_user17' => 23,
      'o_user18' => 24,
      'o_user19' => 25,
      'o_user20' => 26,
      'o_user21' => 27,
      'o_user22' => 28,
      'o_oracle_num_rows' => 10,
      )]
      Error code: dmlreadexception
      Stack trace:

      line 407 of /lib/dml/moodle_database.php: dml_read_exception thrown
      line 274 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->query_end()
      line 1101 of /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()
      line 1362 of /lib/tablelib.php: call to oci_native_moodle_database->get_records_sql()
      line 1382 of /lib/tablelib.php: call to table_sql->query_db()
      line 642 of /mod/assign/renderer.php: call to table_sql->out()
      line 546 of /mod/assign/renderer.php: call to mod_assign_renderer->flexible_table()
      line 215 of /lib/outputrenderers.php: call to mod_assign_renderer->render_assign_grading_table()
      line 1764 of /mod/assign/locallib.php: call to plugin_renderer_base->render()
      line 1792 of /mod/assign/locallib.php: call to assign->view_grading_table()
      line 373 of /mod/assign/locallib.php: call to assign->view_grading_page()
      line 53 of /mod/assign/view.php: call to assign->view()

      Moodle plataform :

      Server : Ubuntu 11.10 Server 32 bits
      BD : Oracle 10.2.0.1.0

        Issue Links

          Activity

          Hide
          Damyon Wiese added a comment -

          Sorry - I don't have access to an Oracle test environment and I can't see anything obviously wrong with this query. Can someone from HQ take a look?

          Show
          Damyon Wiese added a comment - Sorry - I don't have access to an Oracle test environment and I can't see anything obviously wrong with this query. Can someone from HQ take a look?
          Hide
          CLAIRE BROWNE added a comment -

          I have an Oracle site which Moodle 2.3 is running on.

          In the SELECT column list, "firstname" and "lastname" are selected twice, so they need an alias if those selections are genuine. Please note, "u.firstname" (3rd column) is same as "u.firstname as firstname" (8th column) and similarly, "u.lastname" (4th column) is same as "u.lastname as lastname" (9th column).

          If my memory serves me correct, Petr Skoda (i think) said somewhere in one of the forums / trackers I read a while ago that there is a setting in Moodle, where we can choose what columns are to be selected by default and so if that setting says "id, firstname and lastname", then probably that's why this query shows those columns twice!! - just a thought.

          Show
          CLAIRE BROWNE added a comment - I have an Oracle site which Moodle 2.3 is running on. In the SELECT column list, "firstname" and "lastname" are selected twice, so they need an alias if those selections are genuine. Please note, "u.firstname" (3rd column) is same as "u.firstname as firstname" (8th column) and similarly, "u.lastname" (4th column) is same as "u.lastname as lastname" (9th column). If my memory serves me correct, Petr Skoda (i think) said somewhere in one of the forums / trackers I read a while ago that there is a setting in Moodle, where we can choose what columns are to be selected by default and so if that setting says "id, firstname and lastname", then probably that's why this query shows those columns twice!! - just a thought.
          Hide
          vickerylm added a comment - - edited

          We are also using Oracle and have experienced this issue. I did not see where the firstname / lastname aliases were referenced so we removed them and assigned the id alias in the fields method call. We attempted to make the minimum change possible to fix the issue, HQ might want to do this differently.

          Show
          vickerylm added a comment - - edited We are also using Oracle and have experienced this issue. I did not see where the firstname / lastname aliases were referenced so we removed them and assigned the id alias in the fields method call. We attempted to make the minimum change possible to fix the issue, HQ might want to do this differently.
          Hide
          Jaume Rocarias added a comment -

          Hi all,

          Thanks for the help, but making the change of code above, another error appears:

          Notice: Undefined property: stdClass::$id in /var/www/moodle/mod/assign/gradingtable.php on line 457 Notice: Undefined property: stdClass::$id in /var/www/moodle/mod/assign/gradingtable.php on line 483 Notice: Undefined property: stdClass::$id in /var/www/moodle/mod/assign/gradingtable.php on line 489 Notice: Undefined property: stdClass::$id in /var/www/moodle/mod/assign/gradingtable.php on line 490

          Error detectat. cal que l'arregli un programador: User id is required when printing user avatar image.

          Més informació d'aquest error
          Debug info:
          Error code: codingerror
          Stack trace:

          line 186 of /lib/outputcomponents.php: coding_exception thrown
          line 1885 of /lib/outputrenderers.php: call to user_picture->__construct()
          line ? of unknownfile: call to core_renderer->user_picture()
          line 234 of /lib/outputrenderers.php: call to call_user_func_array()
          line 287 of /mod/assign/gradingtable.php: call to plugin_renderer_base->__call()
          line 287 of /mod/assign/gradingtable.php: call to mod_assign_renderer->user_picture()
          line 703 of /lib/tablelib.php: call to assign_grading_table->col_picture()
          line 1271 of /lib/tablelib.php: call to flexible_table->format_row()
          line 1383 of /lib/tablelib.php: call to table_sql->build_table()
          line 642 of /mod/assign/renderer.php: call to table_sql->out()
          line 546 of /mod/assign/renderer.php: call to mod_assign_renderer->flexible_table()
          line 215 of /lib/outputrenderers.php: call to mod_assign_renderer->render_assign_grading_table()
          line 1764 of /mod/assign/locallib.php: call to plugin_renderer_base->render()
          line 1792 of /mod/assign/locallib.php: call to assign->view_grading_table()
          line 373 of /mod/assign/locallib.php: call to assign->view_grading_page()
          line 53 of /mod/assign/view.php: call to assign->view()

          Thanks

          Show
          Jaume Rocarias added a comment - Hi all, Thanks for the help, but making the change of code above, another error appears: Notice: Undefined property: stdClass::$id in /var/www/moodle/mod/assign/gradingtable.php on line 457 Notice: Undefined property: stdClass::$id in /var/www/moodle/mod/assign/gradingtable.php on line 483 Notice: Undefined property: stdClass::$id in /var/www/moodle/mod/assign/gradingtable.php on line 489 Notice: Undefined property: stdClass::$id in /var/www/moodle/mod/assign/gradingtable.php on line 490 Error detectat. cal que l'arregli un programador: User id is required when printing user avatar image. Més informació d'aquest error Debug info: Error code: codingerror Stack trace: line 186 of /lib/outputcomponents.php: coding_exception thrown line 1885 of /lib/outputrenderers.php: call to user_picture->__construct() line ? of unknownfile: call to core_renderer->user_picture() line 234 of /lib/outputrenderers.php: call to call_user_func_array() line 287 of /mod/assign/gradingtable.php: call to plugin_renderer_base->__call() line 287 of /mod/assign/gradingtable.php: call to mod_assign_renderer->user_picture() line 703 of /lib/tablelib.php: call to assign_grading_table->col_picture() line 1271 of /lib/tablelib.php: call to flexible_table->format_row() line 1383 of /lib/tablelib.php: call to table_sql->build_table() line 642 of /mod/assign/renderer.php: call to table_sql->out() line 546 of /mod/assign/renderer.php: call to mod_assign_renderer->flexible_table() line 215 of /lib/outputrenderers.php: call to mod_assign_renderer->render_assign_grading_table() line 1764 of /mod/assign/locallib.php: call to plugin_renderer_base->render() line 1792 of /mod/assign/locallib.php: call to assign->view_grading_table() line 373 of /mod/assign/locallib.php: call to assign->view_grading_page() line 53 of /mod/assign/view.php: call to assign->view() Thanks
          Hide
          Jaume Rocarias added a comment -

          I test with :

          $fields = user_picture::fields('u', array(), 'id') . ', ';

          and I can see the table.

          It's OK?

          Thanks

          Show
          Jaume Rocarias added a comment - I test with : $fields = user_picture::fields('u', array(), 'id') . ', '; and I can see the table. It's OK? Thanks
          Hide
          vickerylm added a comment -

          Sorry about that, yes we were testing on an incomplete grading table (no user pics apparently). Your fixed works fine for us, I think that's what we'll use until Moodle HQ provides a fix. Thanks!

          Show
          vickerylm added a comment - Sorry about that, yes we were testing on an incomplete grading table (no user pics apparently). Your fixed works fine for us, I think that's what we'll use until Moodle HQ provides a fix. Thanks!
          Hide
          Raymond Antonio added a comment -

          Hi,

          This my proposed fix for this tracker and it should do the trick to fix the oracle database error and anything that's related to it though I don't have access to an Oracle test environment. it sits on my github repo : MDL-34192
          https://github.com/raymondAntonio/moodle/tree/MDL-34192
          and here is the diff:
          https://github.com/raymondAntonio/moodle/commit/683d718732c74db6e14ec200d67f0b5f9e866db6

          Cheers

          Show
          Raymond Antonio added a comment - Hi, This my proposed fix for this tracker and it should do the trick to fix the oracle database error and anything that's related to it though I don't have access to an Oracle test environment. it sits on my github repo : MDL-34192 https://github.com/raymondAntonio/moodle/tree/MDL-34192 and here is the diff: https://github.com/raymondAntonio/moodle/commit/683d718732c74db6e14ec200d67f0b5f9e866db6 Cheers
          Hide
          Damyon Wiese added a comment - - edited

          This change looks fine to me - it should fix this issue (but I don't have an Oracle to test with either).

          Show
          Damyon Wiese added a comment - - edited This change looks fine to me - it should fix this issue (but I don't have an Oracle to test with either).
          Hide
          Sara Arjona added a comment -

          We have the same problem and we've fixed it removing only the 'u.firstname as firstname' and 'u.lastname as lastname'. It's not necessary to remove 'u.id as userid' so, in this way, the changes are minimal.

          File: mod/assign/gradingtable.php (line 91)

          $fields = user_picture::fields('u') . ', u.id as userid, ';

          Show
          Sara Arjona added a comment - We have the same problem and we've fixed it removing only the 'u.firstname as firstname' and 'u.lastname as lastname'. It's not necessary to remove 'u.id as userid' so, in this way, the changes are minimal. File: mod/assign/gradingtable.php (line 91) $fields = user_picture::fields('u') . ', u.id as userid, ';
          Hide
          Eloy Lafuente (stronk7) added a comment -

          The main moodle.git repository has just been updated with latest weekly modifications. You may wish to rebase your PULL branches to simplify history and avoid any possible merge conflicts. This would also make integrator's life easier next week.

          TIA and ciao

          Show
          Eloy Lafuente (stronk7) added a comment - The main moodle.git repository has just been updated with latest weekly modifications. You may wish to rebase your PULL branches to simplify history and avoid any possible merge conflicts. This would also make integrator's life easier next week. TIA and ciao
          Hide
          Raymond Antonio added a comment -

          Hi Eloy,

          Thanks for your suggestion and I just did the rebase my PULL branches to make integrator's life less tough and here is the new diff:

          https://github.com/raymondAntonio/moodle/commit/7bd4db2bc6e889a4111d2e12846a24b5c69e2916

          Cheers

          Show
          Raymond Antonio added a comment - Hi Eloy, Thanks for your suggestion and I just did the rebase my PULL branches to make integrator's life less tough and here is the new diff: https://github.com/raymondAntonio/moodle/commit/7bd4db2bc6e889a4111d2e12846a24b5c69e2916 Cheers
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Hi Raymond (et all),

          while your code looks perfect and it seems that you've effectively replaced all occurrences of ->userid by ->id... I really think that, both for being safer and for having code more "descriptive", it's better to, simply, perform this change:

          - $fields = user_picture::fields('u') . ', u.id as userid, u.firstname as firstname, u.lastname as lastname, ';
          + $fields = user_picture::fields('u') . ', u.id as userid, ';
          

          and done.

          How does that sound? If nobody disagrees I'll be integrating that (reduced) change both in 23_STABLE and master.

          Thanks everybody, ciao

          Show
          Eloy Lafuente (stronk7) added a comment - Hi Raymond (et all), while your code looks perfect and it seems that you've effectively replaced all occurrences of ->userid by ->id... I really think that, both for being safer and for having code more "descriptive", it's better to, simply, perform this change: - $fields = user_picture::fields('u') . ', u.id as userid, u.firstname as firstname, u.lastname as lastname, '; + $fields = user_picture::fields('u') . ', u.id as userid, '; and done. How does that sound? If nobody disagrees I'll be integrating that (reduced) change both in 23_STABLE and master. Thanks everybody, ciao
          Hide
          Raymond Antonio added a comment -

          Hi Eloy,

          That's cool too with me

          Show
          Raymond Antonio added a comment - Hi Eloy, That's cool too with me
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Integrated (23 and master), thanks!

          Note1: I've amended the testing instructions a bit, mainly because...

          Note2: I've found some regressions introduced both by MDL-31288 and MDL-31341 when working on this.

          Thanks all and ciao

          Show
          Eloy Lafuente (stronk7) added a comment - Integrated (23 and master), thanks! Note1: I've amended the testing instructions a bit, mainly because... Note2: I've found some regressions introduced both by MDL-31288 and MDL-31341 when working on this. Thanks all and ciao
          Hide
          Michael de Raadt added a comment -

          Test result: Success!

          Tested in 2.3 on Oracle, MySQL, MS SQL and PostgreSQL.

          Only my Oracle instance produced this error before upgrading.

          After upgrading, all instanced behaved.

          Show
          Michael de Raadt added a comment - Test result: Success! Tested in 2.3 on Oracle, MySQL, MS SQL and PostgreSQL. Only my Oracle instance produced this error before upgrading. After upgrading, all instanced behaved.
          Hide
          Eloy Lafuente (stronk7) added a comment -

          I'm so proud...of you, many thanks!

          http://youtu.be/n64CdfDRnZY

          Closing as fixed, ciao

          Show
          Eloy Lafuente (stronk7) added a comment - I'm so proud...of you, many thanks! http://youtu.be/n64CdfDRnZY Closing as fixed, ciao

            People

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

              Dates

              • Created:
                Updated:
                Resolved: