Weird database error with quiz, ORA-01799

Weird database error with quiz, ORA-01799

بذریعہ Longfei Yu -
جوابات کی تعداد: 7

I am with 2.0.1.

When I check the quiz -> Results -> Grade, on the top of the page, there are 2 options, if I select "that are graded for each user (Highest grade), (please see the attachment), then I get the following Oracle database error message:

Default exception handler: Error reading from database Debug: ORA-01799: a column may not be outer-joined to a subquery\nSELECT qqr.* FROM m_user u LEFT JOIN m_quiz_attempts qa ON qa.userid = u.id AND qa.quiz = :quizid AND qa.sumgrades = (SELECT MAX(qa2.sumgrades) FROM m_quiz_attempts qa2 WHERE qa2.quiz = qa.quiz AND qa2.userid = qa.userid) AND qa.timestart = (SELECT MIN(qa3.timestart) FROM m_quiz_attempts qa3 WHERE qa3.quiz = qa.quiz AND qa3.userid = qa.userid AND qa3.sumgrades = qa.sumgrades), m_quiz_question_regrade qqr WHERE qqr.attemptid = qa.uniqueid AND u.id IN (:u0000,:u0001,:u0002) AND qa.id IS NULL\n[array (\n  'quizid' => '5',\n  'u0000' => 283,\n  'u0001' => 284,\n  'u0002' => 217,\n  'oracle_num_rows' => 1,\n)]\n* line 391 of /lib/dml/moodle_database.php: dml_read_exception thrown\n* line 268 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->query_end()\n* line 1043 of /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()\n* line 110 of /mod/quiz/report/reportlib.php: call to oci_native_moodle_database->get_records_sql()\n* line 258 of /mod/quiz/report/overview/report.php: call to quiz_get_regraded_qs()\n* line 90 of /mod/quiz/report.php: call to quiz_overview_report->display()\n, referer: http://our.tests.erver.edu/mod/quiz/report.php

منسلکہ quiz_error.png
درجہ بندی کا اوسط: -
Longfei Yu کے جواب میں

Re: Weird database error with quiz, ORA-01799

بذریعہ Longfei Yu -

Tim Hunt,

Would you like to take a look at this error message from Oracle? Could you please execute this SQL statement to see what Oracle gives to you?

We are using 11g. Thanks.


SELECT qqr.* FROM m_user u LEFT JOIN m_quiz_attempts qa ON     qa.userid = u.id     AND qa.quiz = 22     AND qa.sumgrades = (SELECT MAX(qa2.sumgrades)                             FROM m_quiz_attempts qa2                             WHERE qa2.quiz = qa.quiz                             AND qa2.userid = qa.userid)     AND qa.timestart = (SELECT MIN(qa3.timestart)                            FROM m_quiz_attempts qa3                             WHERE qa3.quiz = qa.quiz                             AND qa3.userid = qa.userid                             AND qa3.sumgrades = qa.sumgrades),     m_quiz_question_regrade qqr WHERE qqr.attemptid = qa.uniqueid AND u.id = 16 AND qa.preview = 0 AND qa.id IS NOT NULL

درجہ بندی کا اوسط: -
Longfei Yu کے جواب میں

Re: Weird database error with quiz, ORA-01799

بذریعہ Tim Hunt -
Core developers کی تصویر Documentation writers کی تصویر Particularly helpful Moodlers کی تصویر Peer reviewers کی تصویر Plugin developers کی تصویر

I don't have access to an Oracle database. I just see bug reports like this and think "why would anyone pay huge sums of money for Oracle when it can't do all the things that open source databases can do?"

It is probably possible to fix the query, but to do so you will have to look at the horrible code in mod/quiz/report/overview/report.php. In particular, look at the $qmsubselect variable. (This is in Moodle 2.0, right?)

درجہ بندی کا اوسط: -
Tim Hunt کے جواب میں

Re: Weird database error with quiz, ORA-01799

بذریعہ Longfei Yu -

Thanks so much, Tim. The University pays Oracle, yeah, it is a lot of money.

Who made this "horrible code" in the ../report.php? lol.

I am kind of new to Moodle, and I am wondering if the Moodle makes the same SQL statement for MYSQL and Oracle? This is the reason why this statement is not working for Oracle? Or, Moodle will generate different SQL Statement for different database.

Thanks.

درجہ بندی کا اوسط: -
Longfei Yu کے جواب میں

Re: Weird database error with quiz, ORA-01799

بذریعہ Tim Hunt -
Core developers کی تصویر Documentation writers کی تصویر Particularly helpful Moodlers کی تصویر Peer reviewers کی تصویر Plugin developers کی تصویر

Code that horribly cannot possibly be the product of just one mind, it was evolved lovingly over many years اشارہ Actually, the real problem is trying to do everything efficiently in a few DB queries, in a report with many different options. Hopefully it will be a bit simpler in Moodle 2.1.

Anyway, to answer your question, the aim in Moodle is to use the same SQL for all databases. That is not always possible. For example, the syntax for concatenating two strings seems to be very incosistent, so to help with things like that we have some methods $DB->sql_... to gererate the SQL that must be different. You also need to follow some other rules. For example, to work on all DBs, you must use AS for column aliases, and you must not use it for table aliases.

درجہ بندی کا اوسط: -
Tim Hunt کے جواب میں

Re: Weird database error with quiz, ORA-01799

بذریعہ Longfei Yu -

Thanks a lot, Tim.

Apparently, this subquery with LEFT JOIN is not working with Oracle. I feel a bit strange, nobody ever reported this problem before on this whole site, b/c everyone is using MYSQL? LOL

It is a really complicated page (report.php), so I am wondering if there is anyone from the development team can help me to modify the QUERY and test with Oracle.

Thanks.

درجہ بندی کا اوسط: -
Longfei Yu کے جواب میں

Re: Weird database error with quiz, ORA-01799

بذریعہ Tim Hunt -
Core developers کی تصویر Documentation writers کی تصویر Particularly helpful Moodlers کی تصویر Peer reviewers کی تصویر Plugin developers کی تصویر

I think that while the query is complex, it does actually decompose into separate bits, and only one of those bits is causing the problem. I think you just need to keep reading the code until it makes sense, and then propose a change to the code that fixes things on oracle. Not fun, but possible, and not too much work.

Not that there is some work-in-progress code at https://github.com/timhunt/Moodle-Question-Engine-2/blob/new_qe/mod/quiz/report/attemptsreport.php#L142 where things have been cleaned up a bit. This is what is heading towards Moodle 2.1. I don't know if looking at that helps or not.

درجہ بندی کا اوسط: -
Longfei Yu کے جواب میں

Re: Weird database error with quiz, ORA-01799

بذریعہ María José Blanes -

Hi,

I may come a couple of years later but,... did you get any solution?

We're using Moodle 2.5 and we get the same error.

Thanks,

Maria

درجہ بندی کا اوسط: -