Weird database error with quiz, ORA-01799

Weird database error with quiz, ORA-01799

by Longfei Yu -
Number of replies: 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

Attachment quiz_error.png
Average of ratings: -
In reply to Longfei Yu

Re: Weird database error with quiz, ORA-01799

by 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

Average of ratings: -
In reply to Longfei Yu

Re: Weird database error with quiz, ORA-01799

by 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?)

Average of ratings: -
In reply to Tim Hunt

Re: Weird database error with quiz, ORA-01799

by 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.

Average of ratings: -
In reply to Longfei Yu

Re: Weird database error with quiz, ORA-01799

by 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 wink 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.

Average of ratings: -
In reply to Tim Hunt

Re: Weird database error with quiz, ORA-01799

by 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.

Average of ratings: -
In reply to Longfei Yu

Re: Weird database error with quiz, ORA-01799

by 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.

Average of ratings: -
In reply to Longfei Yu

Re: Weird database error with quiz, ORA-01799

by 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

Average of ratings: -