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

SQL Errors in Quiz Statistics report under MSSQL

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: 2.6
    • Fix Version/s: 2.6
    • Component/s: Quiz
    • Labels:

      Description

      There's some rather complex SQL that MSSQL disagrees with in the Statistics report.

      Replication steps:

      1. Log in as teacher or admin on site using MSSQL
      2. Navigate to a course
      3. Create a quiz and add some questions
      4. Navigate to Quiz admin > Results > Statistics

      It appears on the Statistics Report page as...

      Error reading from database
       
      More information about this error
      Debug info: SQLState: 42000<br>
      Error Code: 130<br>
      Message: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot perform an aggregate function on an expression containing an aggregate or a subquery.<br>
      SQLState: 42000<br>
      Error Code: 130<br>
      Message: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot perform an aggregate function on an expression containing an aggregate or a subquery.<br>
      SQLState: 42000<br>
      Error Code: 130<br>
      Message: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot perform an aggregate function on an expression containing an aggregate or a subquery.<br>
      SQLState: 42000<br>
      Error Code: 130<br>
      Message: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot perform an aggregate function on an expression containing an aggregate or a subquery.<br>
       
       
      SELECT
      SUM(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 highestattemptscount,
      SUM(sumgrades * 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 highestattemptstotal,
      SUM(1) AS allattemptscount,
      SUM(sumgrades * 1) AS allattemptstotal,
      SUM(CASE WHEN (quiza.attempt = 1) THEN 1 ELSE 0 END) AS firstattemptscount,
      SUM(sumgrades * CASE WHEN (quiza.attempt = 1) THEN 1 ELSE 0 END) AS firstattemptstotal,
      SUM(CASE WHEN (quiza.id = (
      SELECT MAX(qa2.id)
      FROM mdl_quiz_attempts qa2
      WHERE qa2.quiz = quiza.quiz AND
      qa2.userid = quiza.userid)) THEN 1 ELSE 0 END) AS lastattemptscount,
      SUM(sumgrades * CASE WHEN (quiza.id = (
      SELECT MAX(qa2.id)
      FROM mdl_quiz_attempts qa2
      WHERE qa2.quiz = quiza.quiz AND
      qa2.userid = quiza.userid)) THEN 1 ELSE 0 END) AS lastattemptstotal
      FROM mdl_quiz_attempts quiza
      WHERE quiza.quiz = '1' AND quiza.preview = 0 AND quiza.state = N'finished' AND quiza.sumgrades IS NOT NULL
      [array (
      0 => 1,
      1 => 'finished',
      )]
      Error code: dmlreadexception
      Stack trace:
       
          line 441 of \lib\dml\moodle_database.php: dml_read_exception thrown
          line 245 of \lib\dml\sqlsrv_native_moodle_database.php: call to moodle_database->query_end()
          line 352 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->query_end()
          line 780 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->do_query()
          line 828 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->get_recordset_sql()
          line 1415 of \lib\dml\moodle_database.php: call to sqlsrv_native_moodle_database->get_records_sql()
          line 216 of \mod\quiz\report\statistics\classes\calculator.php: call to moodle_database->get_record_sql()
          line 50 of \mod\quiz\report\statistics\classes\calculator.php: call to quiz_statistics_calculator->attempt_counts_and_averages()
          line 516 of \mod\quiz\report\statistics\report.php: call to quiz_statistics_calculator->calculate()
          line 136 of \mod\quiz\report\statistics\report.php: call to quiz_statistics_report->get_quiz_and_questions_stats()
          line 100 of \mod\quiz\report.php: call to quiz_statistics_report->display()
      

      It shows up under unit tests as follows...

      3) quiz_report_statistics_from_steps::test_walkthrough_from_csv with data set #0
       (array('00', 'deferredfeedback'), array(
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+
      | questions
                                         |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+
      |         slot         |         type         |        which         |         c
      at          |         mark         |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+
      |          1           |        random        |                      |         r
      and         |          1           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+
      |                      |     shortanswer      |                      |         r
      and         |          1           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+
      |                      |      numerical       |                      |         r
      and         |          1           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+
      |          2           |   calculatedsimple   |   sumwithvariants    |       mai
      ncat        |          1           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+
      |          3           |        match         |                      |       mai
      ncat        |          1           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+
      |          4           |      truefalse       |                      |       mai
      ncat        |          1           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+
      |          5           |     multichoice      |     two_of_four      |       mai
      ncat        |          1           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+
      |          6           |     multichoice      |     one_of_four      |       mai
      ncat        |          1           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+
      |          7           |     multianswer      |                      |       mai
      ncat        |          1           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+
       
      ,
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+---------
      -------------+----------------------+----------------------+--------------------
      --+----------------------+----------------------+----------------------+--------
      --------------+
      | steps
       
       
       
       
                    |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+---------
      -------------+----------------------+----------------------+--------------------
      --+----------------------+----------------------+----------------------+--------
      --------------+
      |     quizattempt      |      firstname       |       lastname       |       ran
      dqs.1       |  responses.1.answer  |      variants.2      |  responses.2.answer
       |    responses.3.0     |    responses.3.1     |    responses.3.2     |  respons
      es.4.answer  |    responses.5.0     |    responses.5.1     |    responses.5.2
        |    responses.5.3     |  responses.6.answer  | responses.7.1.answer | respons
      es.7.2.answer |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+---------
      -------------+----------------------+----------------------+--------------------
      --+----------------------+----------------------+----------------------+--------
      --------------+
      |          1           |         John         |        Jones         |      nume
      rical       |         3.14         |          1           |         9.9
       |      amphibian       |        mammal        |      amphibian       |
       1           |          1           |          0           |          1
        |          0           |          1           |         Owl          |
        2           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+---------
      -------------+----------------------+----------------------+--------------------
      --+----------------------+----------------------+----------------------+--------
      --------------+
      |          2           |         John         |        Smith         |     short
      answer      |         frog         |          1           |         9.9
       |      amphibian       |        mammal        |      amphibian       |
       1           |          0           |          1           |          1
        |          0           |          0           |         Dog          |
        0           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+---------
      -------------+----------------------+----------------------+--------------------
      --+----------------------+----------------------+----------------------+--------
      --------------+
      |          3           |         John         |        Vicars        |      nume
      rical       |         3.14         |          6           |         9.4
       |      amphibian       |        mammal        |      amphibian       |
       1           |          0           |          0           |          1
        |          1           |          1           |         Owl          |
        2           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+---------
      -------------+----------------------+----------------------+--------------------
      --+----------------------+----------------------+----------------------+--------
      --------------+
      |          4           |         John         |        Pacino        |     short
      answer      |      butterfly       |          6           |         -0.1
       |      amphibian       |        mammal        |      amphibian       |
       1           |          1           |          0           |          1
        |          0           |          3           |         Dog          |
        0           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+---------
      -------------+----------------------+----------------------+--------------------
      --+----------------------+----------------------+----------------------+--------
      --------------+
      |          5           |         John         |        Deniro        |      nume
      rical       |         3.14         |          4           |          0
       |      amphibian       |        mammal        |      amphibian       |
       0           |          0           |          1           |          0
        |          1           |          0           |         Dog          |
        2           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+---------
      -------------+----------------------+----------------------+--------------------
      --+----------------------+----------------------+----------------------+--------
      --------------+
      |          6           |         John         |        Banks         |      nume
      rical       |         3.14         |          1           |         9.9
       |      amphibian       |        mammal        |      amphibian       |
       1           |          1           |          0           |          0
        |          1           |          1           |         Owl          |
        1           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+---------
      -------------+----------------------+----------------------+--------------------
      --+----------------------+----------------------+----------------------+--------
      --------------+
      |          7           |         John         |        Asimov        |      nume
      rical       |        3.142         |          7           |         9.1
       |      amphibian       |        mammal        |      amphibian       |
       1           |          0           |          0           |          1
        |          1           |          0           |         Owl          |
        1           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+---------
      -------------+----------------------+----------------------+--------------------
      --+----------------------+----------------------+----------------------+--------
      --------------+
      |          8           |         John         |       Chomsky        |      nume
      rical       |         3.14         |          4           |         19.4
       |      amphibian       |        mammal        |      amphibian       |
       0           |          0           |          0           |          1
        |          1           |          0           |         Owl          |
        1           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+---------
      -------------+----------------------+----------------------+--------------------
      --+----------------------+----------------------+----------------------+--------
      --------------+
      |          9           |         John         |      Yamaguchi       |     short
      answer      |         frog         |          1           |         -0.7
       |      amphibian       |        mammal        |      amphibian       |
       1           |          1           |          0           |          1
        |          0           |          0           |         Owl          |
        2           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+---------
      -------------+----------------------+----------------------+--------------------
      --+----------------------+----------------------+----------------------+--------
      --------------+
      |          10          |         John         |       Robbins        |      nume
      rical       |         3.1          |          5           |         14.2
       |      amphibian       |        mammal        |      amphibian       |
       0           |          0           |          1           |          1
        |          0           |          0           |         Owl          |
        0           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+---------
      -------------+----------------------+----------------------+--------------------
      --+----------------------+----------------------+----------------------+--------
      --------------+
      |          11          |         Joe          |        Jones         |     short
      answer      |         toad         |          6           |         -0.2
       |      amphibian       |        mammal        |      amphibian       |
       1           |          1           |          1           |          0
        |          0           |          0           |         Owl          |
        0           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+---------
      -------------+----------------------+----------------------+--------------------
      --+----------------------+----------------------+----------------------+--------
      --------------+
      |          12          |         Joe          |        Smith         |     short
      answer      |         frog         |          8           |         5.7
       |      amphibian       |        mammal        |      amphibian       |
       0           |          0           |          0           |          1
        |          1           |          0           |         Owl          |
        2           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+---------
      -------------+----------------------+----------------------+--------------------
      --+----------------------+----------------------+----------------------+--------
      --------------+
      |          13          |         Joe          |        Vicars        |      nume
      rical       |         3.14         |          8           |         -0.2
       |      amphibian       |        mammal        |      amphibian       |
       1           |          1           |          0           |          0
        |          1           |          0           |        wfz9p         |
        2           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+---------
      -------------+----------------------+----------------------+--------------------
      --+----------------------+----------------------+----------------------+--------
      --------------+
      |          14          |         Joe          |        Pacino        |     short
      answer      |         frog         |          1           |         -0.2
       |      amphibian       |        mammal        |      amphibian       |
       1           |          1           |          0           |          1
        |          0           |          1           |         Owl          |
        0           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+---------
      -------------+----------------------+----------------------+--------------------
      --+----------------------+----------------------+----------------------+--------
      --------------+
      |          15          |         Joe          |        Deniro        |      nume
      rical       |         3.1          |          7           |         -0.9
       |      amphibian       |        mammal        |      amphibian       |
       1           |          0           |          1           |          0
        |          1           |          0           |         Owl          |
        2           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+---------
      -------------+----------------------+----------------------+--------------------
      --+----------------------+----------------------+----------------------+--------
      --------------+
      |          16          |         Joe          |        Banks         |     short
      answer      |         frog         |          10          |         -0.7
       |      amphibian       |        mammal        |      amphibian       |
       0           |          1           |          0           |          0
        |          1           |          0           |         Owl          |
        2           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+---------
      -------------+----------------------+----------------------+--------------------
      --+----------------------+----------------------+----------------------+--------
      --------------+
      |          17          |         Joe          |        Asimov        |     short
      answer      |         toad         |          4           |         -0.4
       |      amphibian       |        mammal        |      amphibian       |
       1           |          0           |          1           |          1
        |          0           |          2           |         Owl          |
        2           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+---------
      -------------+----------------------+----------------------+--------------------
      --+----------------------+----------------------+----------------------+--------
      --------------+
      |          18          |         Joe          |       Chomsky        |     short
      answer      |         frog         |          6           |          -1
       |      amphibian       |        mammal        |      amphibian       |
       1           |          0           |          1           |          0
        |          1           |          0           |      Pussy-cat       |
        2           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+---------
      -------------+----------------------+----------------------+--------------------
      --+----------------------+----------------------+----------------------+--------
      --------------+
      |          19          |         Joe          |      Yamaguchi       |      nume
      rical       |         3.14         |          8           |         -0.5
       |      amphibian       |        mammal        |      amphibian       |
       1           |          1           |          1           |          0
        |          0           |          1           |         Owl          |
        0           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+---------
      -------------+----------------------+----------------------+--------------------
      --+----------------------+----------------------+----------------------+--------
      --------------+
      |          20          |         Joe          |       Robbins        |     short
      answer      |      butterfly       |          4           |         19.4
       |      amphibian       |        mammal        |      amphibian       |
       0           |          0           |          1           |          1
        |          0           |          0           |     Wiggly worm      |
        0           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+---------
      -------------+----------------------+----------------------+--------------------
      --+----------------------+----------------------+----------------------+--------
      --------------+
      |          21          |       Roberto        |        Jones         |     short
      answer      |         frog         |          8           |         5.7
       |      amphibian       |      amphibian       |        mammal        |
       1           |          0           |          1           |          1
        |          0           |          2           |        RjUpn         |
        2           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+---------
      -------------+----------------------+----------------------+--------------------
      --+----------------------+----------------------+----------------------+--------
      --------------+
      |          22          |       Roberto        |        Smith         |     short
      answer      |         frog         |          5           |         14.2
       |      amphibian       |        mammal        |      amphibian       |
       0           |          1           |          0           |          0
        |          1           |          1           |         Dog          |
        2           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+---------
      -------------+----------------------+----------------------+--------------------
      --+----------------------+----------------------+----------------------+--------
      --------------+
      |          23          |       Roberto        |        Vicars        |     short
      answer      |         frog         |          5           |         14.2
       |      amphibian       |        mammal        |      amphibian       |
       1           |          0           |          1           |          0
        |          1           |          0           |     Wiggly worm      |
        2           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+---------
      -------------+----------------------+----------------------+--------------------
      --+----------------------+----------------------+----------------------+--------
      --------------+
      |          24          |       Roberto        |        Pacino        |      nume
      rical       |         3.1          |          1           |          -1
       |      amphibian       |        mammal        |      amphibian       |
       0           |          1           |          1           |          0
        |          0           |          0           |         Owl          |
        2           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+---------
      -------------+----------------------+----------------------+--------------------
      --+----------------------+----------------------+----------------------+--------
      --------------+
      |          25          |       Roberto        |        Deniro        |      nume
      rical       |         3.1          |          8           |         -0.1
       |      amphibian       |        mammal        |      amphibian       |
       1           |          1           |          0           |          1
        |          0           |          0           |         Owl          |
        2           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+---------
      -------------+----------------------+----------------------+--------------------
      --+----------------------+----------------------+----------------------+--------
      --------------+
       
      ,
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+
      | results
       
                                                     |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+
      |     quizattempt      |     slots.1.mark     |     slots.2.mark     |     slots
      .3.mark     |     slots.4.mark     |     slots.5.mark     |     slots.6.mark
       |     slots.7.mark     |       summarks       |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+
      |          1           |          1           |          1           |
      1           |          1           |          1           |          0
       |          1           |          6           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+
      |          2           |          1           |          1           |
      1           |          1           |         0.5          |          1
       |          0           |         5.5          |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+
      |          3           |          1           |          1           |
      1           |          1           |         0.5          |          0
       |          1           |         5.5          |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+
      |          4           |          0           |          0           |
      1           |          1           |          1           |          0
       |          0           |          3           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+
      |          5           |          1           |          0           |
      1           |          0           |          0           |          1
       |         0.5          |         3.5          |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+
      |          6           |          1           |          1           |
      1           |          1           |         0.5          |          0
       |         0.5          |          5           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+
      |          7           |          0           |          1           |
      1           |          1           |         0.5          |          1
       |         0.5          |          5           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+
      |          8           |          1           |          1           |
      1           |          0           |         0.5          |          1
       |         0.5          |          5           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+
      |          9           |          1           |          0           |
      1           |          1           |          1           |          1
       |          1           |          6           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+
      |          10          |          0           |          1           |
      1           |          0           |         0.5          |          1
       |         0.5          |          4           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+
      |          11          |         0.8          |          0           |
      1           |          1           |         0.5          |          1
       |         0.5          |         4.8          |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+
      |          12          |          1           |          1           |
      1           |          0           |         0.5          |          1
       |          1           |         5.5          |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+
      |          13          |          1           |          0           |
      1           |          1           |         0.5          |          1
       |         0.5          |          5           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+
      |          14          |          1           |          0           |
      1           |          1           |          1           |          0
       |         0.5          |         4.5          |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+
      |          15          |          0           |          0           |
      1           |          1           |          0           |          1
       |          1           |          4           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+
      |          16          |          1           |          0           |
      1           |          0           |         0.5          |          1
       |          1           |         4.5          |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+
      |          17          |         0.8          |          0           |
      1           |          1           |         0.5          |          0
       |          1           |         4.3          |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+
      |          18          |          1           |          0           |
      1           |          1           |          0           |          1
       |         0.5          |         4.5          |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+
      |          19          |          1           |          0           |
      1           |          1           |         0.5          |          0
       |         0.5          |          4           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+
      |          20          |          0           |          1           |
      1           |          0           |         0.5          |          1
       |          0           |         3.5          |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+
      |          21          |          1           |          1           |      0.33
      33333       |          1           |         0.5          |          0
       |         0.5          |       4.33333        |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+
      |          22          |          1           |          1           |
      1           |          0           |         0.5          |          0
       |         0.5          |          4           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+
      |          23          |          1           |          1           |
      1           |          1           |          0           |          1
       |         0.5          |         5.5          |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+
      |          24          |          0           |          0           |
      1           |          0           |         0.5          |          1
       |          1           |         3.5          |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+
      |          25          |          0           |          0           |
      1           |          1           |          1           |          1
       |          1           |          5           |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+
       
      ,
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+
      | qstats
       
                                                                            |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+
      |         slot         |       facility       |          sd          |   effecti
      veweight    |      covariance      |     markvariance     |  othermarkvariance
       | discriminationindex  |    covariancemax     | discriminativeeffici |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+
      |          1           |        0.704         |     0.4513682901     |    21.292
      2742344     |     -0.022555556     |     0.2037333333     |     0.5002777794
       |    -7.0650767526     |     0.2385555565     |    -9.4550536967     |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+
      |          2           |         0.48         |     0.5099019514     |    18.897
      9800309     |    -0.1172777785     |         0.26         |     0.6334555578
       |    -28.8982125772    |     0.318833334      |    -36.7834118938    |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+
      |          3           |     0.973333332      |      0.13333334      |     4.443
      012573      |    -0.0098888894     |     0.0177777796     |        0.6609
       |    -9.1230674268     |     0.045666669      |    -21.6545012165    |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+
      |          4           |         0.68         |     0.4760952286     |    18.934
      7251357     |    -0.0833888893     |     0.2266666667     |     0.5990111128
       |    -22.6306444113    |     0.2652222232     |    -31.4411395613    |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+
      |          5           |         0.52         |     0.3055050463     |    11.145
      0138688     |    -0.0436944444     |     0.0933333333     |     0.6529555563
       |    -17.6997047674    |     0.2063055556     |    -21.1794802584    |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+
      |          6           |         0.64         |     0.4898979486     |     9.808
      1339177     |    -0.2015555547     |         0.24         |     0.8220111101
       |    -45.3785178421    |     0.3539999995     |    -56.9365974439    |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+
      |          7           |         0.62         |     0.331662479      |    15.478
      8602394     |    -0.0142499998     |         0.11         |     0.5774000005
       |    -5.6543166602     |     0.2190833335     |    -6.5043742058     |
      +----------------------+----------------------+----------------------+----------
      ------------+----------------------+----------------------+---------------------
      -+----------------------+----------------------+----------------------+
       
      ))
      dml_read_exception: Error reading from database (SQLState: 42000<br>
      Error Code: 130<br>
      Message: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot perform an
       aggregate function on an expression containing an aggregate or a subquery.<br>
      SQLState: 42000<br>
      Error Code: 130<br>
      Message: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot perform an
       aggregate function on an expression containing an aggregate or a subquery.<br>
      SQLState: 42000<br>
      Error Code: 130<br>
      Message: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot perform an
       aggregate function on an expression containing an aggregate or a subquery.<br>
      SQLState: 42000<br>
      Error Code: 130<br>
      Message: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot perform an
       aggregate function on an expression containing an aggregate or a subquery.<br>
                      SELECT
                          SUM(CASE WHEN (quiza.id = (
                                  SELECT MIN(qa2.id)
                                  FROM phpu_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 phpu_quiz_attempts qa3
                                          WHERE qa3.quiz = quiza.quiz AND
                                              qa3.userid = quiza.userid
                                      )
                                  )) THEN 1 ELSE 0 END) AS highestattemptscount,
                          SUM(sumgrades * CASE WHEN (quiza.id = (
                                  SELECT MIN(qa2.id)
                                  FROM phpu_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 phpu_quiz_attempts qa3
                                          WHERE qa3.quiz = quiza.quiz AND
                                              qa3.userid = quiza.userid
                                      )
                                  )) THEN 1 ELSE 0 END) AS highestattemptstotal,
                          SUM(1) AS allattemptscount,
                          SUM(sumgrades * 1) AS allattemptstotal,
                          SUM(CASE WHEN (quiza.attempt = 1) THEN 1 ELSE 0 END) AS firstattemptscount,
                          SUM(sumgrades * CASE WHEN (quiza.attempt = 1) THEN 1 ELSE 0
      END) AS firstattemptstotal,
                          SUM(CASE WHEN (quiza.id = (
                                  SELECT MAX(qa2.id)
                                  FROM phpu_quiz_attempts qa2
                                  WHERE qa2.quiz = quiza.quiz AND
                                      qa2.userid = quiza.userid)) THEN 1 ELSE 0 END) AS lastattemptscount,
                          SUM(sumgrades * CASE WHEN (quiza.id = (
                                  SELECT MAX(qa2.id)
                                  FROM phpu_quiz_attempts qa2
                                  WHERE qa2.quiz = quiza.quiz AND
                                      qa2.userid = quiza.userid)) THEN 1 ELSE 0 END) AS lastattemptstotal
                      FROM phpu_quiz_attempts quiza
                      WHERE quiza.quiz = '1' AND quiza.preview = 0 AND quiza.state = N
      'finished' AND quiza.sumgrades IS NOT NULL
      [array (
        0 => 1,
        1 => 'finished',
      )])
       
      D:\xampp\htdocs\master_integration\lib\dml\moodle_database.php:441
      D:\xampp\htdocs\master_integration\lib\dml\sqlsrv_native_moodle_database.php:245
      D:\xampp\htdocs\master_integration\lib\dml\sqlsrv_native_moodle_database.php:352
      D:\xampp\htdocs\master_integration\lib\dml\sqlsrv_native_moodle_database.php:780
      D:\xampp\htdocs\master_integration\lib\dml\sqlsrv_native_moodle_database.php:828
      D:\xampp\htdocs\master_integration\lib\dml\moodle_database.php:1415
      D:\xampp\htdocs\master_integration\mod\quiz\report\statistics\classes\calculator.php:216
      D:\xampp\htdocs\master_integration\mod\quiz\report\statistics\classes\calculator.php:50
      D:\xampp\htdocs\master_integration\mod\quiz\report\statistics\report.php:516
      D:\xampp\htdocs\master_integration\mod\quiz\report\statistics\tests\stats_from_steps_walkthrough_test.php:47
      D:\xampp\htdocs\master_integration\mod\quiz\report\statistics\tests\stats_from_steps_walkthrough_test.php:96
      D:\xampp\htdocs\master_integration\lib\phpunit\classes\advanced_testcase.php:80
       
      To re-run:
       \xampp\php\phpunit quiz_report_statistics_from_steps mod\quiz\report\statistics\tests\stats_from_steps_walkthrough_test.php
      

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  18/Nov/13