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

SQL Errors in Quiz Statistics report under MSSQL

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Blocker
    • Resolution: Fixed
    • 2.6
    • 2.6
    • Quiz

    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

              jamiesensei Jamie Pratt
              salvetore Michael de Raadt
              Dan Poltawski Dan Poltawski
              Dan Poltawski Dan Poltawski
              Tim Hunt, Ilya Tregubov, Kevin Percy, Mathew May, Mihail Geshoski, Shamim Rezaie
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                18/Nov/13