Moodle
  1. Moodle
  2. MDL-26819

quiz->report->statistics sql error in oracle and mssql

    Details

    • Database:
      Microsoft SQL, Oracle
    • Testing Instructions:
      Hide

      1) Run the database unit tests in as many DBs as possible.
      2) TEST: No test fails in the test_get_records_sql_complicated() unit test.
      3) Also, in as many DBs as possible, navigate to ( or create ) one quiz with multiple attempts by various students
      4) Go to the statistics report (from the Navigation menu)
      5) TEST: Select "calculate statistics from first attempt". No error.
      6) TEST: Select "calculate statistics from all attempts". No error.
      7) TEST: Click on "recalculate now" button (if present). No error.

      Show
      1) Run the database unit tests in as many DBs as possible. 2) TEST: No test fails in the test_get_records_sql_complicated() unit test. 3) Also, in as many DBs as possible, navigate to ( or create ) one quiz with multiple attempts by various students 4) Go to the statistics report (from the Navigation menu) 5) TEST: Select "calculate statistics from first attempt". No error. 6) TEST: Select "calculate statistics from all attempts". No error. 7) TEST: Click on "recalculate now" button (if present). No error.
    • Affected Branches:
      MOODLE_20_STABLE, MOODLE_21_STABLE, MOODLE_22_STABLE
    • Fixed Branches:
      MOODLE_20_STABLE, MOODLE_21_STABLE
    • Pull from Repository:
    • Pull Master Branch:
    • Rank:
      16480

      Description

      in Quiz->results->statistics page,
      i'm getting oracle error:

      Debug info: ORA-00907: missing right parenthesis
      SELECT (CASE WHEN attempt=1 THEN 1 ELSE 0 END) AS isfirst, COUNT(1) AS countrecs, SUM(sumgrades) AS total FROM m_quiz_attempts qa WHERE qa.quiz = :quizid AND qa.preview=0 AND qa.timefinish !=0 GROUP BY (attempt=1)
      [array (
      'quizid' => '1',
      )]
      Stack trace:
      line 391 of \lib\dml\moodle_database.php: dml_read_exception thrown
      line 268 of \lib\dml\oci_native_moodle_database.php: call to moodle_database->query_end()
      line 1042 of \lib\dml\oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()
      line 464 of \mod\quiz\report\statistics\report.php: call to oci_native_moodle_database->get_records_sql()
      line 594 of \mod\quiz\report\statistics\report.php: call to quiz_statistics_report->quiz_stats()
      line 97 of \mod\quiz\report\statistics\report.php: call to quiz_statistics_report->quiz_questions_stats()
      line 90 of \mod\quiz\report.php: call to quiz_statistics_report->display()

      and MSSQL error:

      Debug info: Incorrect syntax near '='.
      SELECT (CASE WHEN attempt=1 THEN 1 ELSE 0 END) AS isfirst, COUNT(1) AS countrecs, SUM(sumgrades) AS total FROM mdl_quiz_attempts qa WHERE qa.quiz = ? AND qa.preview=0 AND qa.timefinish !=0 GROUP BY (attempt=1)
      [array (
      0 => '2',
      )]
      Stack trace:
      line 391 of /lib/dml/moodle_database.php: dml_read_exception thrown
      line 255 of /lib/dml/mssql_native_moodle_database.php: call to moodle_database->query_end()
      line 707 of /lib/dml/mssql_native_moodle_database.php: call to mssql_native_moodle_database->query_end()
      line 736 of /lib/dml/mssql_native_moodle_database.php: call to mssql_native_moodle_database->get_recordset_sql()
      line 464 of /mod/quiz/report/statistics/report.php: call to mssql_native_moodle_database->get_records_sql()
      line 594 of /mod/quiz/report/statistics/report.php: call to quiz_statistics_report->quiz_stats()
      line 97 of /mod/quiz/report/statistics/report.php: call to quiz_statistics_report->quiz_questions_stats()
      line 90 of /mod/quiz/report.php: call to quiz_statistics_report->display()

        Activity

        Hide
        Eloy Lafuente (stronk7) added a comment -

        Uhm, that GROUP clause it 100% wrong, I guess it only works under some DBs because it's used as "1" (column position).

        AFAIK, then only cross-db way to do that is to replicate the complete CASE... expression in the GROUP BY clause. Nor column positions nor aliases are cross-db (only MySQL and PostgreSQL support them).

        Going to work on some test & solution...

        Show
        Eloy Lafuente (stronk7) added a comment - Uhm, that GROUP clause it 100% wrong, I guess it only works under some DBs because it's used as "1" (column position). AFAIK, then only cross-db way to do that is to replicate the complete CASE... expression in the GROUP BY clause. Nor column positions nor aliases are cross-db (only MySQL and PostgreSQL support them). Going to work on some test & solution...
        Hide
        Eloy Lafuente (stronk7) added a comment -

        Confirmed, only the "complete expression in GROUP BY" alternative is correct and cross-db here. Fix and test added (test passing against the RDBMS).

        Show
        Eloy Lafuente (stronk7) added a comment - Confirmed, only the "complete expression in GROUP BY" alternative is correct and cross-db here. Fix and test added (test passing against the RDBMS).
        Hide
        Tim Hunt added a comment -

        +1 from me. Good fix.

        Strange that GROUP BY boolean does not work. However, for this query it does not matter, because we want to group by the case, and the group by and the select fields have to match.

        Show
        Tim Hunt added a comment - +1 from me. Good fix. Strange that GROUP BY boolean does not work. However, for this query it does not matter, because we want to group by the case, and the group by and the select fields have to match.
        Hide
        Sam Hemelryk added a comment -

        Thanks Eloy, this has been integrated now

        Show
        Sam Hemelryk added a comment - Thanks Eloy, this has been integrated now
        Hide
        Aparup Banerjee added a comment -

        unit tests are ok

        testing quiz->statistics->first attempt+show report = ok,
        when testing for recalculation and quiz->statistics -> all attempts (& recalculation) :

        Debug info: ORA-01008: not all variables bound
        INSERT INTO m_quiz_question_statistics (slot,s,subquestion,subquestions,randomguessscore,questionid,positions,maxmark,facility,sd,discriminationindex,discriminativeefficiency,negcovar,effectiveweight,quizstatisticsid) VALUES (:o_slot,:s,:o_subquestion,:o_subquestions,:o_randomguessscore,:o_questionid,:o_positions,:o_maxmark,:o_facility,:o_sd,:o_discriminationindex,:o_discriminativeefficiency,:o_negcovar,:o_effectiveweight,:o_quizstatisticsid)
        [array (
        'o_slot' => 1,
        'o_s' => 2,
        'o_subquestion' => 0,
        'o_subquestions' => '',
        'o_randomguessscore' => 0.5,
        'o_questionid' => '3',
        'o_positions' => 1,
        'o_maxmark' => '1',
        'o_facility' => 1,
        'o_sd' => 0,
        'o_discriminationindex' => NULL,
        'o_discriminativeefficiency' => NULL,
        'o_negcovar' => 0,
        'o_effectiveweight' => 0,
        'o_quizstatisticsid' => 1,
        )]
        Stack trace:
        line 397 of \lib\dml\moodle_database.php: dml_write_exception thrown
        line 268 of \lib\dml\oci_native_moodle_database.php: call to moodle_database->query_end()
        line 1176 of \lib\dml\oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()
        line 1219 of \lib\dml\oci_native_moodle_database.php: call to oci_native_moodle_database->insert_record_raw()
        line 846 of \mod\quiz\report\statistics\report.php: call to oci_native_moodle_database->insert_record()
        line 889 of \mod\quiz\report\statistics\report.php: call to quiz_statistics_report->cache_stats()
        line 140 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()

        Show
        Aparup Banerjee added a comment - unit tests are ok testing quiz->statistics->first attempt+show report = ok, when testing for recalculation and quiz->statistics -> all attempts (& recalculation) : Debug info: ORA-01008: not all variables bound INSERT INTO m_quiz_question_statistics (slot,s,subquestion,subquestions,randomguessscore,questionid,positions,maxmark,facility,sd,discriminationindex,discriminativeefficiency,negcovar,effectiveweight,quizstatisticsid) VALUES (:o_slot,:s,:o_subquestion,:o_subquestions,:o_randomguessscore,:o_questionid,:o_positions,:o_maxmark,:o_facility,:o_sd,:o_discriminationindex,:o_discriminativeefficiency,:o_negcovar,:o_effectiveweight,:o_quizstatisticsid) [array ( 'o_slot' => 1, 'o_s' => 2, 'o_subquestion' => 0, 'o_subquestions' => '', 'o_randomguessscore' => 0.5, 'o_questionid' => '3', 'o_positions' => 1, 'o_maxmark' => '1', 'o_facility' => 1, 'o_sd' => 0, 'o_discriminationindex' => NULL, 'o_discriminativeefficiency' => NULL, 'o_negcovar' => 0, 'o_effectiveweight' => 0, 'o_quizstatisticsid' => 1, )] Stack trace: line 397 of \lib\dml\moodle_database.php: dml_write_exception thrown line 268 of \lib\dml\oci_native_moodle_database.php: call to moodle_database->query_end() line 1176 of \lib\dml\oci_native_moodle_database.php: call to oci_native_moodle_database->query_end() line 1219 of \lib\dml\oci_native_moodle_database.php: call to oci_native_moodle_database->insert_record_raw() line 846 of \mod\quiz\report\statistics\report.php: call to oci_native_moodle_database->insert_record() line 889 of \mod\quiz\report\statistics\report.php: call to quiz_statistics_report->cache_stats() line 140 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()
        Hide
        Aparup Banerjee added a comment -

        please feel free to pass if its a different code area from the patch.

        Show
        Aparup Banerjee added a comment - please feel free to pass if its a different code area from the patch.
        Hide
        Eloy Lafuente (stronk7) added a comment -

        Great, "s" being converted to ":s" and "o_s" placeholder.

        I'm passing this because it's related with the mess created by MDL-26842 and being fixed by MDL-28080.

        Ciao

        Show
        Eloy Lafuente (stronk7) added a comment - Great, "s" being converted to ":s" and "o_s" placeholder. I'm passing this because it's related with the mess created by MDL-26842 and being fixed by MDL-28080 . Ciao
        Hide
        Eloy Lafuente (stronk7) added a comment -

        Pass, thanks!

        Show
        Eloy Lafuente (stronk7) added a comment - Pass, thanks!
        Hide
        Eloy Lafuente (stronk7) added a comment -

        git & cvs repositories have been populated with this solution. Many thanks for your collaboration, yay!

        Closing, ciao

        Show
        Eloy Lafuente (stronk7) added a comment - git & cvs repositories have been populated with this solution. Many thanks for your collaboration, yay! Closing, ciao

          People

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

            Dates

            • Created:
              Updated:
              Resolved: