Moodle
  1. Moodle
  2. MDL-30370 Meta: Oracle SQL issues
  3. MDL-32063

Error viewing assignments with teacher role (in Oracle)

    Details

    • Database:
      Oracle
    • Workaround:
      Hide

      A casting must be done with the troublemaker column (data2). This one works at least in Oracle:

      ...
      return $DB->count_records_sql("SELECT COUNT('x')
                                       FROM {assignment_submissions} s
                                  LEFT JOIN {assignment} a ON a.id = s.assignment
                                 INNER JOIN ($enroledsql) u ON u.id = s.userid
                                      WHERE s.assignment = :assignmentid AND "
                                            . $DB->sql_compare_text("s.data2") . " = 'submitted'", $params);
      
      Show
      A casting must be done with the troublemaker column (data2). This one works at least in Oracle: ... return $DB->count_records_sql("SELECT COUNT('x') FROM {assignment_submissions} s LEFT JOIN {assignment} a ON a.id = s.assignment INNER JOIN ($enroledsql) u ON u.id = s.userid WHERE s.assignment = :assignmentid AND " . $DB->sql_compare_text( "s.data2" ) . " = 'submitted'" , $params);
    • Affected Branches:
      MOODLE_21_STABLE, MOODLE_22_STABLE
    • Rank:
      38757

      Description

      Using Oracle, when you try to view an assignment (/mod/assignment/view.php) sometimes it crashes with ORA-00932 inconsistent datatypes: expected - got CLOB

      * 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 1290 of \lib\dml\moodle_database.php: call to oci_native_moodle_database->get_records_sql()
      * line 1029 of \lib\dml\oci_native_moodle_database.php: call to moodle_database->get_record_sql()
      * line 1365 of \lib\dml\moodle_database.php: call to oci_native_moodle_database->get_record_sql()
      * line 1536 of \lib\dml\moodle_database.php: call to moodle_database->get_field_sql()
      * line 406 of \mod\assignment\type\upload\assignment.class.php: call to moodle_database->count_records_sql()
      * line 3753 of \mod\assignment\lib.php: call to assignment_upload->count_real_submissions()
      * line 3498 of \lib\navigationlib.php: call to assignment_extend_settings_navigation()
      * line 2914 of \lib\navigationlib.php: call to settings_navigation->load_module_settings()
      * line 601 of \lib\pagelib.php: call to settings_navigation->initialise()
      * line 617 of \lib\pagelib.php: call to moodle_page->magic_get_settingsnav()
      * line 133 of \blocks\settings\block_settings.php: call to moodle_page->__get()
      * line 280 of \blocks\moodleblock.class.php: call to block_settings->get_content()
      * line 232 of \blocks\moodleblock.class.php: call to block_base->formatted_contents()
      * line 926 of \lib\blocklib.php: call to block_base->get_content_for_output()
      * line 978 of \lib\blocklib.php: call to block_manager->create_block_contents()
      * line 349 of \lib\blocklib.php: call to block_manager->ensure_content_created()
      * line 6 of \theme\base\layout\general.php: call to block_manager->region_has_content()
      * line 654 of \lib\outputrenderers.php: call to include()
      * line 612 of \lib\outputrenderers.php: call to core_renderer->render_page_layout()
      * line ? of unknownfile: call to core_renderer->header()
      * line 1296 of \lib\setuplib.php: call to call_user_func_array()
      * line 194 of \mod\assignment\lib.php: call to bootstrap_renderer->__call()
      * line 194 of \mod\assignment\lib.php: call to bootstrap_renderer->header()
      * line 51 of \mod\assignment\type\upload\assignment.class.php: call to assignment_base->view_header()
      * line 51 of \mod\assignment\view.php: call to assignment_upload->view().
      

      I've seen that the problem is when it executes the following sql on line 401 of /mod/assignment/type/upload/assignment.class.php:

      ...
      SELECT COUNT('x')
        FROM {assignment_submissions} s
        LEFT JOIN {assignment} a ON a.id = s.assignment
       INNER JOIN ($enroledsql) u ON u.id = s.userid
       WHERE s.assignment = :assignmentid
         AND s.data2 = 'submitted'
      

      The reason is that the data2 columns' datatype is clob, and (in Oracle) you cannot make comparisons between LOB columns and strings.

        Activity

        Hide
        Michael de Raadt added a comment -

        Thanks for spotting that.

        I don't think to_char() will work in other databases. There is a helper function called $DB->sql_compare_text that might be able to be used.

        Show
        Michael de Raadt added a comment - Thanks for spotting that. I don't think to_char() will work in other databases. There is a helper function called $DB->sql_compare_text that might be able to be used.
        Hide
        Iñigo Zendegi added a comment -

        I knew that was just a quick patch for our case, I've tried using that function and works fine too, so I've just changed the workaround of this issue

        Show
        Iñigo Zendegi added a comment - I knew that was just a quick patch for our case, I've tried using that function and works fine too, so I've just changed the workaround of this issue
        Hide
        Iñigo Zendegi added a comment -

        When I've gone to apply this patch to Moodle 2.1.7 I've seen it has been already fixed.

        I've realized that on 2.2.4 version it has been fixed too, so this issue can be closed as fixed (if it only affects the versions listed up there).

        I think I haven't privileges enough to close the issue (and if I do I can't find how to do it), so someone who can would have to do it

        Great work anyway!

        Show
        Iñigo Zendegi added a comment - When I've gone to apply this patch to Moodle 2.1.7 I've seen it has been already fixed. I've realized that on 2.2.4 version it has been fixed too, so this issue can be closed as fixed (if it only affects the versions listed up there). I think I haven't privileges enough to close the issue (and if I do I can't find how to do it), so someone who can would have to do it Great work anyway!
        Hide
        Sam Hemelryk added a comment -

        Thanks for letting us know that this has been resolved Iñigo. Closing now.

        Show
        Sam Hemelryk added a comment - Thanks for letting us know that this has been resolved Iñigo. Closing now.

          People

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

            Dates

            • Created:
              Updated:
              Resolved: