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

Microsoft SQL server incompatibility in quiz responses report

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 2.5
    • Fix Version/s: 2.4.8, 2.5.4
    • Component/s: Quiz
    • Labels:
    • Testing Instructions:
      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.
    • Affected Branches:
      MOODLE_25_STABLE
    • Fixed Branches:
      MOODLE_24_STABLE, MOODLE_25_STABLE
    • Pull from Repository:
    • Pull Master Branch:

      Description

      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()

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              timhunt Tim Hunt
              Reporter:
              mguffroy Matthieu Guffroy
              Peer reviewer:
              Petr Skoda
              Integrator:
              Eloy Lafuente (stronk7)
              Tester:
              Eloy Lafuente (stronk7)
              Participants:
              Component watchers:
              Tim Hunt, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                13/Jan/14