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

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

    Details

    • Type: Bug
    • Status: Closed
    • Priority: 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:

      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

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

              jaume77 Jaume Rocarias created issue -
              jaume77 Jaume Rocarias made changes -
              Field Original Value New Value
              Priority Minor [ 4 ] Blocker [ 1 ]
              Database Oracle [ 10011 ]
              Attachment screen.jpg [ 28895 ]
              Environment Ubuntu Server 11.10 32 bits
              PHP : 5.3.6
              BD : Oracle 10.2.0.1.0
              Hide
              damyon 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 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?
              damyon Damyon Wiese made changes -
              Assignee Damyon Wiese [ damyon ] moodle.com [ moodle.com ]
              Hide
              moodlecvqo 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
              moodlecvqo 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 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 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
              jaume77 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
              jaume77 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
              jaume77 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
              jaume77 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 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 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
              corleone 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
              corleone 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
              corleone Raymond Antonio made changes -
              Link This issue is duplicated by MDL-34661 [ MDL-34661 ]
              damyon Damyon Wiese made changes -
              Testing Instructions Pre-requisites:
              Moodle installed with Oracle database

              Go to "View/grade all submissions" page in assignment module.
              Verify you do not see the error described in this bug.
              Workaround Use a different database :)
              Hide
              damyon 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 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).
              damyon Damyon Wiese made changes -
              Status Open [ 1 ] Waiting for integration review [ 10010 ]
              damyon Damyon Wiese made changes -
              Link This issue is duplicated by MDL-34660 [ MDL-34660 ]
              damyon Damyon Wiese made changes -
              Link This issue is duplicated by MDL-34660 [ MDL-34660 ]
              Hide
              sarjona 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
              sarjona 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
              stronk7 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
              stronk7 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
              corleone 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
              corleone 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
              nebgor Aparup Banerjee made changes -
              Currently in integration Yes [ 10041 ]
              stronk7 Eloy Lafuente (stronk7) made changes -
              Assignee moodle.com [ moodle.com ] Eloy Lafuente (stronk7) [ stronk7 ]
              Hide
              stronk7 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
              stronk7 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
              stronk7 Eloy Lafuente (stronk7) made changes -
              Status Waiting for integration review [ 10010 ] Integration review in progress [ 10004 ]
              Integrator stronk7
              Hide
              corleone Raymond Antonio added a comment -

              Hi Eloy,

              That's cool too with me

              Show
              corleone Raymond Antonio added a comment - Hi Eloy, That's cool too with me
              stronk7 Eloy Lafuente (stronk7) made changes -
              Testing Instructions Pre-requisites:
              Moodle installed with Oracle database

              Go to "View/grade all submissions" page in assignment module.
              Verify you do not see the error described in this bug.
              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.
              stronk7 Eloy Lafuente (stronk7) made changes -
              Testing Instructions 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.
              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.

              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.
              Hide
              stronk7 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
              stronk7 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
              stronk7 Eloy Lafuente (stronk7) made changes -
              Status Integration review in progress [ 10004 ] Waiting for testing [ 10005 ]
              Affects Version/s 2.4 [ 12255 ]
              Fix Version/s 2.3.2 [ 12353 ]
              stronk7 Eloy Lafuente (stronk7) made changes -
              Testing Instructions 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.

              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.
              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.
              stronk7 Eloy Lafuente (stronk7) made changes -
              Link This issue has a non-specific relationship to MDL-35004 [ MDL-35004 ]
              timb Tim Barker made changes -
              Tester salvetore
              salvetore Michael de Raadt made changes -
              Status Waiting for testing [ 10005 ] Testing in progress [ 10011 ]
              salvetore Michael de Raadt made changes -
              Labels triaged
              Hide
              salvetore 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
              salvetore 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.
              salvetore Michael de Raadt made changes -
              Status Testing in progress [ 10011 ] Tested [ 10006 ]
              Hide
              stronk7 Eloy Lafuente (stronk7) added a comment -

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

              http://youtu.be/n64CdfDRnZY

              Closing as fixed, ciao

              Show
              stronk7 Eloy Lafuente (stronk7) added a comment - I'm so proud...of you, many thanks! http://youtu.be/n64CdfDRnZY Closing as fixed, ciao
              stronk7 Eloy Lafuente (stronk7) made changes -
              Status Tested [ 10006 ] Closed [ 6 ]
              Resolution Fixed [ 1 ]
              Currently in integration Yes [ 10041 ]
              Integration date 31/Aug/12
              damyon Damyon Wiese made changes -
              Link This issue is duplicated by MDL-35178 [ MDL-35178 ]
              salvetore Michael de Raadt made changes -
              Link This issue is duplicated by MDL-35313 [ MDL-35313 ]

                People

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

                  Dates

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