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

Microsoft SQL server incompatibility in quiz responses report

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Fixed
    • Icon: Minor Minor
    • 2.4.8, 2.5.4
    • 2.5
    • Quiz
    • MOODLE_25_STABLE
    • MOODLE_24_STABLE, MOODLE_25_STABLE
    • Hide

      This needs to be tested on MS SQL server and Oracle (to ensure the bug is fixed) and MySQL or Postgres (to ensure there are no regressions). (I already tested Postgres. Ideally test on all 4 DBs.)

      You need a quiz with some attempts by students.

      1. Go to Results -> Responses for that quiz.
      2. Turn all all the options Show question text, Show responses and Show correct responses.
      3. Click on the column headings to sort by those columns for various questions. Ensure you don't get any errors.
      Show
      This needs to be tested on MS SQL server and Oracle (to ensure the bug is fixed) and MySQL or Postgres (to ensure there are no regressions). (I already tested Postgres. Ideally test on all 4 DBs.) You need a quiz with some attempts by students. Go to Results -> Responses for that quiz. Turn all all the options Show question text, Show responses and Show correct responses. Click on the column headings to sort by those columns for various questions. Ensure you don't get any errors.

      To reproduce this bug, you have to run the latest stable moodle version with a Microsoft SQL server database.
      Create a quiz.
      Make an attempts to this quiz.
      Go to see the responses report, the url in my case is: moodle/mod/quiz/report.php?id=61&mode=responses

      And I have got this debug message (after having enabling the debug mode):

      Removing "response1 ASC," from the last line of the request, made it works. But I don't know moodle source code enough to know if it is an acceptable patch to do, (and it sounds a bad things to me)...

      Debug info: SQLState: 42000<br>
      Error Code: 306<br>
      Message: [Microsoft][SQL Server Native Client 11.0][SQL Server]The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.<br>

      SELECT TOP 30
      CAST(u.id AS NVARCHAR(255)) + CAST('#' AS NVARCHAR(255)) + CAST(COALESCE(quiza.attempt, 0) AS NVARCHAR(255)) AS uniqueid,
      (CASE WHEN quiza.id = (
      SELECT MIN(qa2.id)
      FROM mdl_quiz_attempts qa2
      WHERE qa2.quiz = quiza.quiz AND
      qa2.userid = quiza.userid AND
      COALESCE(qa2.sumgrades, 0) = (
      SELECT MAX(COALESCE(qa3.sumgrades, 0))
      FROM mdl_quiz_attempts qa3
      WHERE qa3.quiz = quiza.quiz AND
      qa3.userid = quiza.userid
      )
      ) THEN 1 ELSE 0 END) AS gradedattempt,
      quiza.uniqueid AS usageid,
      quiza.id AS attempt,
      u.id AS userid,
      u.idnumber,
      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,
      qa1.questionsummary AS question1,
      qa1.rightanswer AS right1,
      qa1.responsesummary AS response1
      FROM
      mdl_user u
      LEFT JOIN mdl_quiz_attempts quiza ON
      quiza.userid = u.id AND quiza.quiz = '1'
      LEFT JOIN (
      SELECT qa1qa.id AS questionattemptid,
      qa1qa.questionusageid,
      qa1qa.slot,
      qa1qa.behaviour,
      qa1qa.questionid,
      qa1qa.variant,
      qa1qa.maxmark,
      qa1qa.minfraction,
      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 mdl_quiz_attempts qa1quiza
      JOIN mdl_question_attempts qa1qa ON qa1qa.questionusageid = qa1quiza.uniqueid
      JOIN mdl_question_attempt_steps qa1qas ON
      qa1qas.id = (
      SELECT MAX(id)
      FROM mdl_question_attempt_steps
      WHERE questionattemptid = qa1qa.id
      )
      WHERE qa1quiza.quiz = '1'
      ) qa1 ON qa1.questionusageid = quiza.uniqueid AND qa1.slot = '1'
      WHERE u.id IN ('4','5') AND quiza.preview = 0 AND quiza.id IS NOT NULL
      ORDER BY response1 ASC, quiza.id ASC
      [array (
      0 => '1',
      1 => '1',
      2 => '1',
      3 => 4,
      4 => 5,
      )]
      Error code: dmlreadexception
      Stack trace:
      line 423 of \lib\dml\moodle_database.php: dml_read_exception thrown
      line 260 of \lib\dml\sqlsrv_native_moodle_database.php: call to moodle_database->query_end()
      line 367 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->query_end()
      line 787 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->do_query()
      line 835 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->get_recordset_sql()
      line 1395 of \lib\tablelib.php: call to sqlsrv_native_moodle_database->get_records_sql()
      line 491 of \mod\quiz\report\attemptsreport_table.php: call to table_sql->query_db()
      line 1415 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 100 of \mod\quiz\report.php: call to quiz_responses_report->display()

            timhunt Tim Hunt
            mguffroy Matthieu Guffroy
            Petr Skoda Petr Skoda
            Eloy Lafuente (stronk7) Eloy Lafuente (stronk7)
            Eloy Lafuente (stronk7) Eloy Lafuente (stronk7)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Created:
              Updated:
              Resolved:

                Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.