[#MDL-12084] Quiz overal feedback is not displayed on Oracle, because it cannot compare a TEXT text column with '' - Moodle Tracker

Details

Type: Bug
Status: Closed
Priority: Minor
Resolution: Fixed
Affects Version/s: 1.8.3
Fix Version/s: 1.8.4, 1.9, 2.0
Component/s: Quiz
Labels:
None
Environment:
Solaris + Oracle

Database:

Oracle
URL:
http://agora.xtec.cat
Affected Branches:
MOODLE_18_STABLE
Fixed Branches:
MOODLE_18_STABLE, MOODLE_19_STABLE, MOODLE_20_STABLE

Description

In some cases following error appears:

ORA-00932: inconsistent datatypes: expected - got CLOB
SELECT * FROM mlquiz_feedback WHERE quizid = 741 AND feedbacktext <>''

line 677 of lib/dmllib.php: call to debugging()
line 307 of lib/dmllib.php: call to get_recordset_sql()
line 290 of lib/dmllib.php: call to record_exists_sql()
line 346 of mod/quiz/locallib.php: call to record_exists_select()
line 181 of mod/quiz/view.php: call to quiz_has_feedback()

More Options
- Sort By Name
- Sort By Date
- Ascending
- Descending
- Download All

Attachments

locallib.php_18.patch

04/Dec/07 8:52 AM

0.7 kB

Eloy Lafuente (stronk7)

Issue Links

This issue is a clone of:
MDL-12186	Oracle SQL Statement problem -- unable to list glossary entries

This issue has been marked as being related by:
MDL-12186	Oracle SQL Statement problem -- unable to list glossary entries

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Tim Hunt added a comment - 12/Nov/07 5:33 AM

We need to use the right function out of dmllib.php to generate the SQL for the comparison.

Show

Tim Hunt added a comment - 12/Nov/07 5:33 AM We need to use the right function out of dmllib.php to generate the SQL for the comparison.

Hide

Permalink

Joseph Boiteau added a comment - 16/Nov/07 11:41 PM

Same problem as mine:
Oracle doesn't allow that SQL syntax with the datatype CLOB (field "feedbacktext" for you)

the next is crashing
SELECT * FROM m_quiz_feedback WHERE feedbacktext <> '';
but as soon you remove the quotes, it's working....
SELECT * FROM m_quiz_feedback WHERE feedbacktext is not null;

I don't know how to handle it !

Show

Joseph Boiteau added a comment - 16/Nov/07 11:41 PM Same problem as mine: Oracle doesn't allow that SQL syntax with the datatype CLOB (field "feedbacktext" for you) the next is crashing SELECT * FROM m_quiz_feedback WHERE feedbacktext <> ''; but as soon you remove the quotes, it's working.... SELECT * FROM m_quiz_feedback WHERE feedbacktext is not null; I don't know how to handle it !

Hide

Permalink

Eloy Lafuente (stronk7) added a comment - 04/Dec/07 12:29 AM

Hi,

can you confirm that, in your Oracle DBs, the content of that field (feedbacktext) is ONE whitespace? That way we'll be able to determine the correct solution for all DB flavours.

TIA and ciao

Show

Eloy Lafuente (stronk7) added a comment - 04/Dec/07 12:29 AM Hi, can you confirm that, in your Oracle DBs, the content of that field (feedbacktext) is ONE whitespace? That way we'll be able to determine the correct solution for all DB flavours. TIA and ciao

Hide

Permalink

Eloy Lafuente (stronk7) added a comment - 04/Dec/07 8:52 AM

Hi,

I've added to new functions:

sql_isempty() and sql_isnotempty() in order to dinamically generate the correct SQL sentences to compare with empty values under all DBs.

Those functions are in CVS for all versions of Moodle (1.8, 1.9 and HEAD).

Also, I've added one small patch here with the code needed to use those functions in the SQL that is causing this bug.

Can somebody do this:

1) Update his 1.8 Moodle server from CVS (to the latest 1.8.x available).
2) Apply the patch above and see if it's working ok (I've executed it here under MySQL, PostgreSQL and Oracle and seems to be working ok).

Please feedback (not empty, like this bug :-D ) will be really welcome. If positive, the patch will be applied definitively to Moodle 1.8 and upwards.

TIA and ciao

Show

Eloy Lafuente (stronk7) added a comment - 04/Dec/07 8:52 AM Hi, I've added to new functions: sql_isempty() and sql_isnotempty() in order to dinamically generate the correct SQL sentences to compare with empty values under all DBs. Those functions are in CVS for all versions of Moodle (1.8, 1.9 and HEAD). Also, I've added one small patch here with the code needed to use those functions in the SQL that is causing this bug. Can somebody do this: 1) Update his 1.8 Moodle server from CVS (to the latest 1.8.x available). 2) Apply the patch above and see if it's working ok (I've executed it here under MySQL, PostgreSQL and Oracle and seems to be working ok). Please feedback (not empty, like this bug :-D ) will be really welcome. If positive, the patch will be applied definitively to Moodle 1.8 and upwards. TIA and ciao

Hide

Permalink

Martín Langhoff added a comment - 04/Dec/07 12:27 PM

I was just looking at this problem, reported by Dennis Rochford <Dennis.Rochford@usq.edu.au> who crafted a patch that does a cast to varchar in the WHERE clause. I think that sql_isempty() and sql_isnotempty() are the way to go though -

the Oracle Dirty Hack is abstracted away
no performance hit of doing a full-table-scan-and-cast
will help handle the transition to real nulls

So ack'd from this angle

Show

Martín Langhoff added a comment - 04/Dec/07 12:27 PM I was just looking at this problem, reported by Dennis Rochford <Dennis.Rochford@usq.edu.au> who crafted a patch that does a cast to varchar in the WHERE clause. I think that sql_isempty() and sql_isnotempty() are the way to go though -

the Oracle Dirty Hack is abstracted away
no performance hit of doing a full-table-scan-and-cast
will help handle the transition to real nulls

So ack'd from this angle

Hide

Permalink

Sara Arjona added a comment - 04/Dec/07 6:57 PM

Hi!
Thank you for your work but we currently are'nt able to test the new Moodle 1.8 CVS version because we only have a production environment. We are working to have another test environment. As soon as we get it, we'll try (but we suppose that this won't happen after Christmas).
Greetings!

Show

Sara Arjona added a comment - 04/Dec/07 6:57 PM Hi! Thank you for your work but we currently are'nt able to test the new Moodle 1.8 CVS version because we only have a production environment. We are working to have another test environment. As soon as we get it, we'll try (but we suppose that this won't happen after Christmas). Greetings!

Hide

Permalink

Eloy Lafuente (stronk7) added a comment - 07/Dec/07 11:34 PM

Oki,

so if nobody has objections... I'm going to apply the patch from 18 to HEAD.

Ciao

Show

Eloy Lafuente (stronk7) added a comment - 07/Dec/07 11:34 PM Oki, so if nobody has objections... I'm going to apply the patch from 18 to HEAD. Ciao

Hide

Permalink

Tim Hunt added a comment - 07/Dec/07 11:43 PM

Sounds good to me!

Show

Tim Hunt added a comment - 07/Dec/07 11:43 PM Sounds good to me!

Hide

Permalink

Eloy Lafuente (stronk7) added a comment - 08/Dec/07 12:04 AM

Applied and closing. Thanks and ciao

Show

Eloy Lafuente (stronk7) added a comment - 08/Dec/07 12:04 AM Applied and closing. Thanks and ciao

Hide

Permalink

Martín Langhoff added a comment - 08/Dec/07 10:32 AM

Great!

Show

Martín Langhoff added a comment - 08/Dec/07 10:32 AM Great!

People

Assignee:

Eloy Lafuente (stronk7)

Reporter:

Sara Arjona

Tester:

Nobody

Participants:

Eloy Lafuente (stronk7), Joseph Boiteau, Martín Langhoff, Sara Arjona, Tim Hunt

Vote (0)

Watch (3)

Dates

Created:

09/Nov/07 7:51 PM

Updated:

17/Dec/10 1:08 PM

Resolved:

08/Dec/07 12:04 AM