Moodle
  1. Moodle
  2. MDL-32708

Feedback analsysis causes ORA-00932: inconsistent datatypes: expected - got CLOB

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Won't Fix
    • Affects Version/s: 2.1.4
    • Fix Version/s: None
    • Component/s: Feedback
    • Labels:
    • Affected Branches:
      MOODLE_21_STABLE
    • Rank:
      39663

      Description

      Description:
      The feedback analysis tab appears to be showing errors. This will probably only affect those courses that have already had feedback submitted.

      More information about this error
      
      Debug info: ORA-00932: inconsistent datatypes: expected - got CLOB
      SELECT * FROM m_feedback_value WHERE item = :o_param1 AND value != '' 
      [array (
      'o_param1' => '104',
      )]
      Stack trace:
      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 1122 of /lib/dml/moodle_database.php: call to oci_native_moodle_database->get_records_sql()
      line 2159 of /mod/feedback/lib.php: call to moodle_database->get_records_select()
      line 116 of /mod/feedback/item/multichoice/lib.php: call to feedback_get_group_values()
      line 208 of /mod/feedback/item/multichoice/lib.php: call to feedback_item_multichoice->get_analysed()
      line 140 of /mod/feedback/analysis.php: call to feedback_item_multichoice->print_analysed()
      

      I have fixed this error by replacing a line in the library file with an Oracle-specific SQL statement

      file: /moodle/mod/feedback/lib.php, line 2150
      $ignore_empty_select = "AND value != ''";
      

      replaced with the following line:

      $ignore_empty_select = "AND value is not null";
      

        Activity

        Hide
        Michael de Raadt added a comment -

        Thanks for reporting that.

        For a cross-platform solution, $DB->sql_compare_text() should be used whenever the "value" text field is compared to a varchar or literal string.

        These comparisons are now around line 2350 in master.

        Show
        Michael de Raadt added a comment - Thanks for reporting that. For a cross-platform solution, $DB->sql_compare_text() should be used whenever the "value" text field is compared to a varchar or literal string. These comparisons are now around line 2350 in master.
        Hide
        Michael de Raadt added a comment -

        Quick patch added. I searched for uses of value in queries in this file.

        Show
        Michael de Raadt added a comment - Quick patch added. I searched for uses of value in queries in this file.
        Hide
        Rob King added a comment -

        Hi Michael,

        Thanks very much for this.

        I have tried to implement the fix that you provided, but I've run into a problem.

        Please keep in mind that we're using an older version of Moodle (2.1.4) so the code that you provided in the diff file doesn't quite fit our version. So I have implemented the fixes by hand.

        So, using the approach that you suggested, here are the 2 changes that I made to our library:

            if (intval($groupid) > 0) {
                if($ignore_empty) {
                    $ignore_empty_select = "AND {$DB->sql_compare_text('fbv.value')} != ''";
                }
                else {
                    $ignore_empty_select = "";
                }
        

        AND

            
                if($ignore_empty) {
                    $ignore_empty_select = "AND {$DB->sql_compare_text('value')} != ''";
                }
                else {
                    $ignore_empty_select = "";
                }
        

        These fixes make the error disappear. HOWEVER, I noticed now that Moodle is not displaying the graphs (i.e. coloured bars) on the analysis page. See attached picture, feedback_analysis_with_patch.png to see what I'm seeing.

        When I changed the code back to the Oracle-specific "is not null", the Oracle error disappears, and the graphs appear correctly. See attached picture titled feedback_analysis_with_oracle_specific_fix.png as proof.

        Regards,
        Rob

        Show
        Rob King added a comment - Hi Michael, Thanks very much for this. I have tried to implement the fix that you provided, but I've run into a problem. Please keep in mind that we're using an older version of Moodle (2.1.4) so the code that you provided in the diff file doesn't quite fit our version. So I have implemented the fixes by hand. So, using the approach that you suggested, here are the 2 changes that I made to our library: if (intval($groupid) > 0) { if($ignore_empty) { $ignore_empty_select = "AND {$DB->sql_compare_text('fbv.value')} != ''"; } else { $ignore_empty_select = ""; } AND if($ignore_empty) { $ignore_empty_select = "AND {$DB->sql_compare_text('value')} != ''"; } else { $ignore_empty_select = ""; } These fixes make the error disappear. HOWEVER, I noticed now that Moodle is not displaying the graphs (i.e. coloured bars) on the analysis page. See attached picture, feedback_analysis_with_patch.png to see what I'm seeing. When I changed the code back to the Oracle-specific "is not null", the Oracle error disappears, and the graphs appear correctly. See attached picture titled feedback_analysis_with_oracle_specific_fix.png as proof. Regards, Rob
        Hide
        Rob King added a comment -

        Results using patch.

        Show
        Rob King added a comment - Results using patch.
        Hide
        Rob King added a comment -

        Screenshot with oracle-specific fix.

        Show
        Rob King added a comment - Screenshot with oracle-specific fix.
        Hide
        Rob King added a comment -

        I turned on DB tracing, and I can see that this is what's bring run in the database.

        This is the statement being executed during page access:

        SQL> SELECT * FROM lmsusr.m_feedback_value WHERE item = 121 AND dbms_lob.substr(value, 32,1) != '';

        The previous SQL query will result in 0 records.

        If I run the following SQL query (which is Oracle-specific), this will result in 3 records. This uses the Oracle-specific solution I proposed earlier.

        SELECT * FROM lmsusr.m_feedback_value WHERE item = 121 AND value is not null;

        After playing with it a bit, I think I have come up with a solution. The following query will return all 3 records:

        SELECT * FROM lmsusr.m_feedback_value WHERE item = 121 AND dbms_lob.substr(value, 32,1) != ' ';

        Using this logic, the fixes would look like this:

        1. $ignore_empty_select = "AND {$DB->sql_compare_text('fbv.value')} != ' '";
        2. $ignore_empty_select = "AND {$DB->sql_compare_text('value')} != ' '";

        I have tried these fixes in our dev environment, and these seem to work.

        So, is this code OK for this fix? I don't know if it's valid for people to enter a single space as an answer on a feedback question, so it may not be a good choice.

        Show
        Rob King added a comment - I turned on DB tracing, and I can see that this is what's bring run in the database. This is the statement being executed during page access: SQL> SELECT * FROM lmsusr.m_feedback_value WHERE item = 121 AND dbms_lob.substr(value, 32,1) != ''; The previous SQL query will result in 0 records. If I run the following SQL query (which is Oracle-specific), this will result in 3 records. This uses the Oracle-specific solution I proposed earlier. SELECT * FROM lmsusr.m_feedback_value WHERE item = 121 AND value is not null; After playing with it a bit, I think I have come up with a solution. The following query will return all 3 records: SELECT * FROM lmsusr.m_feedback_value WHERE item = 121 AND dbms_lob.substr(value, 32,1) != ' '; Using this logic, the fixes would look like this: 1. $ignore_empty_select = "AND {$DB->sql_compare_text('fbv.value')} != ' '"; 2. $ignore_empty_select = "AND {$DB->sql_compare_text('value')} != ' '"; I have tried these fixes in our dev environment, and these seem to work. So, is this code OK for this fix? I don't know if it's valid for people to enter a single space as an answer on a feedback question, so it may not be a good choice.
        Hide
        Michael de Raadt added a comment -

        Thanks for reporting this issue.

        We have detected that this issue has been inactive for over a year. It was reported as affecting versions that are no longer supported.

        If you believe that this issue is still relevant to current versions (2.5 and beyond), please comment on the issue. Issues left inactive for a further month will be closed.

        Michael d.

        TW9vZGxlDQo=

        Show
        Michael de Raadt added a comment - Thanks for reporting this issue. We have detected that this issue has been inactive for over a year. It was reported as affecting versions that are no longer supported. If you believe that this issue is still relevant to current versions (2.5 and beyond), please comment on the issue. Issues left inactive for a further month will be closed. Michael d. TW9vZGxlDQo=
        Hide
        Michael de Raadt added a comment -

        I'm closing this issue as it has been inactive for over a year has been recorded as affecting versions that are no longer supported.

        This is being done as part of a bulk annual clean-up of issues.

        If you still believe this is an issue in supported versions, please create a new issue.

        Show
        Michael de Raadt added a comment - I'm closing this issue as it has been inactive for over a year has been recorded as affecting versions that are no longer supported. This is being done as part of a bulk annual clean-up of issues. If you still believe this is an issue in supported versions, please create a new issue.

          People

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

            Dates

            • Created:
              Updated:
              Resolved: