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

Oracle ORA-00932 in quiz responses report when sort by questions

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Waiting for integration review
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 3.6.6, 3.7.2
    • Fix Version/s: None
    • Component/s: Quiz
    • Labels:
    • Testing Instructions:
      Hide

      This must be tested on all four supported database types.

      1. Create a course, set to allow self-enrolment and manual enrolments.
      2. Create a quiz.
      3. Add an multiple choice question, and ensure that the question text is more than 32 characters long.
      4. Also ensure that all the choices are more than 32 characters long.
      5. Log in as a student.
      6. Self-enrol in the coures.
      7. Attempt the quiz.
      8. Log in as Admin or Teacher.
      9. Manually enrol the student in the course so they now have two enrolments (as in the testing instructions for MDL-57511).
      10. Go to the quiz, and Results -> Responses.
      11. Turn on all three options for what to show (question text, right answer, and response text) and redisplay the report.
      12. Verify that the report displays, and the student is only listed once, and the full text of the question, response and right answer is shown.
      13. Click to sort by the Question 1 question text column.
      14. Verify that the report displays without errors, and the full text of the question, response and right answer is shown.
      15. Click to sort by the Question 1 right answer column.
      16. Verify that the report displays without errors, and the full text of the question, response and right answer is shown.
      17. Click to sort by the Question 1 response column.
      18. Verify that the report displays without errors, and the full text of the question, response and right answer is shown.
      Show
      This must be tested on all four supported database types. Create a course, set to allow self-enrolment and manual enrolments. Create a quiz. Add an multiple choice question, and ensure that the question text is more than 32 characters long. Also ensure that all the choices are more than 32 characters long. Log in as a student. Self-enrol in the coures. Attempt the quiz. Log in as Admin or Teacher. Manually enrol the student in the course so they now have two enrolments (as in the testing instructions for MDL-57511 ). Go to the quiz, and Results -> Responses. Turn on all three options for what to show (question text, right answer, and response text) and redisplay the report. Verify that the report displays, and the student is only listed once, and the full text of the question, response and right answer is shown. Click to sort by the Question 1 question text column. Verify that the report displays without errors, and the full text of the question, response and right answer is shown. Click to sort by the Question 1 right answer column. Verify that the report displays without errors, and the full text of the question, response and right answer is shown. Click to sort by the Question 1 response column. Verify that the report displays without errors, and the full text of the question, response and right answer is shown.
    • Affected Branches:
      MOODLE_36_STABLE, MOODLE_37_STABLE
    • Pull 3.8 Branch:
    • Pull 3.9 Branch:
    • Pull Master Branch:

      Description

      It is necessary to have an installation on an Oracle DB.

      1 - Access as a teacher in a course and create a quiz, with at least 1 question.
      2 - Access as a student and answer this quiz.
      3 - Access as a teacher and go to the "Responses" report screen.
      4 - In the table, sort by one of the "question" columns.

       
      The attached error is displayed.
       
      Debug info: ORA-00932: tipos de dato inconsistentes: se esperaba - se ha obtenido CLOB
      SELECT *
      FROM (SELECT
      DISTINCT MOODLELIB.UNDO_MEGA_HACK(MOODLELIB.TRICONCAT(u.id, '#', COALESCE(quiza.attempt, 0))) AS uniqueid,
      quiza.uniqueid AS usageid,
      quiza.id AS attempt,
      u.id AS userid,
      u.idnumber, u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.firstname,u.lastname,
      u.picture,
      u.imagealt,
      u.institution,
      u.department,
      u.email,
      quiza.state,
      quiza.sumgrades,
      quiza.timefinish,
      quiza.timestart,
      CASE WHEN quiza.timefinish = 0 THEN null
      WHEN quiza.timefinish > quiza.timestart THEN quiza.timefinish - quiza.timestart
      ELSE 0 END AS duration,
      dbms_lob.substr(qa1.questionsummary, 32,1) AS question1,
      qa1.questionsummary AS question1full,
      dbms_lob.substr(qa1.rightanswer, 32,1) AS right1,
      qa1.rightanswer AS right1full,
      dbms_lob.substr(qa1.responsesummary, 32,1) AS response1,
      qa1.responsesummary AS response1full
      FROM m_user u
      LEFT JOIN m_quiz_attempts quiza ON
      quiza.userid = u.id AND quiza.quiz = :o_quizid
      JOIN m_user_enrolments ej1_ue ON ej1_ue.userid = u.id
      JOIN m_enrol ej1_e ON (ej1_e.id = ej1_ue.enrolid AND ej1_e.courseid = :o_ej1_courseid)
      LEFT JOIN (
      SELECT qa1qa.id AS questionattemptid,
      qa1qa.questionusageid,
      qa1qa.slot,
      qa1qa.behaviour,
      qa1qa.questionid,
      qa1qa.variant,
      qa1qa.maxmark,
      qa1qa.minfraction,
      qa1qa.maxfraction,
      qa1qa.flagged,
      qa1qa.questionsummary,
      qa1qa.rightanswer,
      qa1qa.responsesummary,
      qa1qa.timemodified,
      qa1qas.id AS attemptstepid,
      qa1qas.sequencenumber,
      qa1qas.state,
      qa1qas.fraction,
      qa1qas.timecreated,
      qa1qas.userid

      FROM m_quiz_attempts qa1quiza
      JOIN m_question_attempts qa1qa ON qa1qa.questionusageid = qa1quiza.uniqueid
      JOIN m_question_attempt_steps qa1qas ON qa1qas.questionattemptid = qa1qa.id
      AND qa1qas.sequencenumber = (
      SELECT MAX(sequencenumber)
      FROM m_question_attempt_steps
      WHERE questionattemptid = qa1qa.id
      )
      WHERE qa1quiza.quiz = :o_qa1quizid
      ) qa1 ON qa1.questionusageid = quiza.uniqueid AND qa1.slot = :o_qa1slot
      WHERE quiza.preview = 0 AND quiza.id IS NOT NULL AND 1 = 1 AND u.deleted = 0
      ORDER BY response1 ASC, firstname ASC, quiza.id ASC)
      WHERE rownum <= :o_oracle_num_rows
      [array (
      'o_quizid' => '7101',
      'o_ej1_courseid' => '4582',
      'o_qa1quizid' => '7101',
      'o_qa1slot' => '1',
      'o_oracle_num_rows' => 30,
      )]
      Error code: dmlreadexception
       
      Stack trace: * line 486 of /lib/dml/moodle_database.php: dml_read_exception thrown

      • line 277 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->query_end()
      • line 1180 of /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()
      • line 1609 of /lib/tablelib.php: call to oci_native_moodle_database->get_records_sql()
      • line 575 of /mod/quiz/report/attemptsreport_table.php: call to table_sql->query_db()
      • line 1630 of /lib/tablelib.php: call to quiz_attempts_report_table->query_db()
      • line 192 of /mod/quiz/report/responses/report.php: call to table_sql->out()
      • line 97 of /mod/quiz/report.php: call to quiz_responses_report->display()
         
         

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              timhunt Tim Hunt
              Reporter:
              jaume77 Jaume Rocarias
              Peer reviewer:
              Shamim Rezaie
              Participants:
              Component watchers:
              Tim Hunt, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 4 hours
                  4h