Moodle

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

Details

  • Database:
    Oracle, Microsoft SQL
  • 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:
  • Pull Master Diff URL:

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

Ascending order - Click to sort in descending order
Hide
Permalink
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
Permalink
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
Permalink
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
Permalink
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
Permalink
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
Permalink
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
Permalink
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
Permalink
Eloy Lafuente (stronk7) added a comment -

Pass, thanks!

Show
Eloy Lafuente (stronk7) added a comment - Pass, thanks!
Hide
Permalink
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

Vote (0)
Watch (0)

Dates

  • Created:
    Updated:
    Resolved: