Moodle

Oracle SQL Statement problem -- unable to list glossary entries

Details

  • Type: Bug Bug
  • Status: Closed Closed
  • Priority: Minor Minor
  • Resolution: Fixed
  • Affects Version/s: 1.8.2
  • Fix Version/s: 1.8.4, 1.9, 2.0
  • Component/s: Glossary
  • Labels:
    None
  • Environment:
    Red Hat Enterprise Linux ES release 4 (Nahant Update 5)
    PHP Version 5.2.3
    Oracle Database 10g Release 10.2.0.1.0
  • Database:
    Oracle
  • URL:
    sorry, intranet :)
  • Affected Branches:
    MOODLE_18_STABLE
  • Fixed Branches:
    MOODLE_18_STABLE, MOODLE_19_STABLE, MOODLE_20_STABLE

Description

While trying to use the auto-linking functionality with the glossary, I got nothing.....
When looking into code, I found a bug ; it seems to be relative to Oracle
The clause: != \'\' appears to not working under my Oracle....???
And obviously is not null does (but does not test the same).

Another problem shows up after it, when having aliases without entries or categories, the array_merge with a null array fails !

I've modified this in the joined file. Here's the diff lines:
73,74c73,74
< AND ge.concept != \'\' ');
<
—
> AND ge.concept is not null '); //Joseph Boiteau: previous sql isn't working with Oracle
> // and moreover, the string could'nt be emptyl using interface
85c85,86
< if ($aliases) {
—
> // Joseph Boiteau: adding controls to not use a null $concepts object
> if ($aliases and $concepts) {
87a89,91
> elseif ($aliases) { > $concepts = $aliases; > }

Should I commit that somewhere ?
Is it a bad sql or an Oracle abnormal comportment ?

It's working here but I don't know what to do with that !

Issue Links

This issue has a clone:
MDL-9593 ORA error: Errors when restoring a zipped course Major Closed
MDL-12084 Quiz overal feedback is not displayed on Oracle, because it cannot compare a TEXT text column with '' Minor Closed
This issue has a non-specific relationship to:
MDL-12084 Quiz overal feedback is not displayed on Oracle, because it cannot compare a TEXT text column with '' Minor Closed

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Joseph Boiteau added a comment -

Hi Eloy, you're apparently the Oracle god down there

I found more: it's because Oracle does not allow empty string (empty string is null for Oracle).

I didn't find a moodle track of that exact problem..?
I've seen MDL-7634 : speaking about a specific replacement of empty string by a space !?
I also found traces of a 'oracle_dirty_hack' function ; apparently obsolete.

Is there a known moodle way to handle it ?

I can probably remove the clause I mentioned
replacing
> != ''
with
> is not null
doesn't really match ; but the clause itself isn't really useful (I mean we cannot submit nothing in the input textbox).

Show
Joseph Boiteau added a comment - Hi Eloy, you're apparently the Oracle god down there I found more: it's because Oracle does not allow empty string (empty string is null for Oracle). I didn't find a moodle track of that exact problem..? I've seen MDL-7634 : speaking about a specific replacement of empty string by a space !? I also found traces of a 'oracle_dirty_hack' function ; apparently obsolete. Is there a known moodle way to handle it ? I can probably remove the clause I mentioned replacing > != '' with > is not null doesn't really match ; but the clause itself isn't really useful (I mean we cannot submit nothing in the input textbox).
Hide
Permalink
Joseph Boiteau added a comment -

Same problem: Oracle doesn't allow empty strings (neither comparison with empty string)

Show
Joseph Boiteau added a comment - Same problem: Oracle doesn't allow empty strings (neither comparison with empty string)
Hide
Permalink
Eloy Lafuente (stronk7) added a comment -

Hi Joseph,

yep, oracle doesn't differentiate between empty strings and nulls, and more yet, nulls aren't equal to anything. That's the cause we added the 1-space trick for oracle everytime one record is inserted/updated.

All this relaying about those fields not being used too much in searches.

I've been looking the glossary filter code and, in this case, I think the best solution is to delete the ='' condition because it's redundant, mainly because some lines below we iterate over all the concepts (in php) deleting all those that are empties.

I've added the proposed mod/glosary/filter.php above. Can you confirm if it's working ok under Oracle?

And to replace all those !='' by is not null isn't a good approach because the rest of DBs differentiate them son empties aren't nulls. Will have to find one solution for each occurrence.

Thanks!

Show
Eloy Lafuente (stronk7) added a comment - Hi Joseph, yep, oracle doesn't differentiate between empty strings and nulls, and more yet, nulls aren't equal to anything. That's the cause we added the 1-space trick for oracle everytime one record is inserted/updated. All this relaying about those fields not being used too much in searches. I've been looking the glossary filter code and, in this case, I think the best solution is to delete the ='' condition because it's redundant, mainly because some lines below we iterate over all the concepts (in php) deleting all those that are empties. I've added the proposed mod/glosary/filter.php above. Can you confirm if it's working ok under Oracle? And to replace all those !='' by is not null isn't a good approach because the rest of DBs differentiate them son empties aren't nulls. Will have to find one solution for each occurrence. Thanks!
Hide
Permalink
Eloy Lafuente (stronk7) added a comment - - edited

New filter alternative added (replacing the previous one).

Show
Eloy Lafuente (stronk7) added a comment - - edited New filter alternative added (replacing the previous one).
Hide
Permalink
Joseph Boiteau added a comment -

Hi Eloy,

Yes your version of filter.php is working perfectly !

Moreover your code is smarter than mine. Nice.

Joseph

Show
Joseph Boiteau added a comment - Hi Eloy, Yes your version of filter.php is working perfectly ! Moreover your code is smarter than mine. Nice. Joseph
Hide
Permalink
Eloy Lafuente (stronk7) added a comment -

Fixed filter is now in CVS (18_STABLE, 19_STABLE and HEAD).

Thanks for feedback! Ciao

Show
Eloy Lafuente (stronk7) added a comment - Fixed filter is now in CVS (18_STABLE, 19_STABLE and HEAD). Thanks for feedback! Ciao

People

Vote (0)
Watch (2)

Dates

  • Created:
    Updated:
    Resolved:
Atlassian JIRA (v4.4.3#663-r165197) | Report a problem
Powered by a free Atlassian JIRA open source license for Moodle. Try JIRA - bug tracking software for your team.