Moodle

Hi Dan,

if I'm not wrong, the example you've posted above:

select * from mdl_user where firstname = "name"

only performs case-insensitive matches under MySQL (and that's an odd behaviour). All the rest of DBs perform case-sensitive comparisons, and that's the way things must work.

So, if there are parts of Moodle relying in that MySQL "feature" we should fix all them ASAP.

Another thing is the "LIKE" statement, yes, there are some parts executing it in case-sensitive (LIKE) and case-insensitive (ILIKE) modes... I remember I was looking at that stuff for Oracle in Moodle 1.7 times and the NLS + "LINGUISTIC" stuff had some undesired effects... I don't remember more, but I'm sure I prospect that way... uhmm. Perhaps it was that, once executed... all the session becomes case-insensitive... and that causes problems in other queries executed later.. uhmm... horrible memory!

In any case, it's only a LIKE think. Never an = thing IMO.

Ciao

yeah - that makes sense!

• our major issues are with the LIKE - especially when searching for users on the "browse list of users" page - it needs to be case in-sensitive there.

interested to hear of the issues you had if you remember them! - happy to close this bug off as "won't fix" if you think it's going to cause too many undesired effects!

Dan

Uhm... I've been reviewing my notes and:

1) Effectively, I didn't implemented the NLS tricks because it affected the whole session and it could affect other code.
2) I researched that one possible alternative could be the to use regexp expressions (REGEXP_LIKE) that are available since 10g (not sure if from the beginning). They support case-insensitive searches perfectly (slow, but works).

So, perhaps, the only way to fix this would be:

1) Detect if regular expressions are available in the DB.
2) Use them as sql_ilike() for those oracle DBs.

Ciao

For reference: http://www.adp-gmbh.ch/ora/sql/re/regexp_like.html

Eloy,

instead of using slow regular expressions just to make case insensitive comparisons, why don't we use the SQL standard upper() (or lower() for that matter) operator?

We've internally 'fixed' the glossary search code this way, and while there is an sql_ilike() function, its completely useless the way it's currently defined. You get back the operator name to perform case insensitive like operations, but those databases that lack them (like Oracle) can't work with that.

It would be more sensible to make the sql_ilike() function take two parameters (field name, value string to compare) and return the portion of SQL code you need to perform the case insensitive comparison. So it could return something like " field ILIKE 'value'" for those databases that support that operator, and " UPPER(field) LIKE UPPER('value')" for those that don't. And then you'd simply concatenate that portion of SQL with your regular sentence. E.g.,

"SELECT bla,bla,bla FROM table WHERE " . sql_ilike(field,'value')

Does that make sense?

Saludos. Iñaki.

Hi Iñaki,

agree about the upper-ing both the field and the value like one possible solution. 100%

What I'm not sure is if that will be really better (in terms of performance) than the use of regular expressions to fix this. I guess it'll be more or less, the same (aka full scans), because those upper() functions, like the use of regular expressions, will break indexes for sure.

But, in any case.... it seems that we need to pass both params (value and field) to the sql_ilike() function, yes!

And 2.0 seems to be a good moment to break that (not sure about 1.9). Perhaps we could redefine it as:

sql_ilike($value, $name=null)

so, if second parameter isn't specified, we'll run in BC mode, and if the second parameter is specified, we'll use the new constructor (no matter if it's upper() or regexp)...

or perhaps we should introduce a new function....

or perhpas we should radically change the exisiting one to use always both parameters and change all current uses.

3 alternatives... I vote for the last one.... (the radical one)... any comment?

I vote for a combination:

1) make a new function (different name) that works the new way

2) have the existing function keep working (as well as possible)...

...BUT put in a debugging() that tells you you should fix the code to use the new function

this will make our and other people's lives easier when updating private plugins after a 2.0 upgrade, I think. Also means you can fix it gradually in core too.

Sam's suggestion is sensible.

Of course, the much more serious bug is that MySQL is only capable of ILIKE (which is calls LIKE) when using UTF-8. That makes some things totally impossible. (E.g. MDL-9391) Do we have a tracker issue for that?

Referring back to Eloy's undesired effects...

I have been trying to trace a number of bugs in our Moodle-Oracle installation. I have found that using the recommended settings of:
NLS_SORT=BINARY_CI and
NLS_COMP=LINGUISTIC,
I get unexpected result sets for subqueries. Subqueries are generated for Oracle when adodb SelectLimit is called. For example, using the blog_tags block:

select * from
(
SELECT t.id, t.rawname, t.name, COUNT(DISTINCT ti.id) AS ct
FROM mdb.m_tag t, mdb.m_tag_instance ti, mdb.m_post p
WHERE t.id = ti.tagid
AND p.id = ti.itemid
AND ti.itemtype = 'post'
AND (p.publishstate = 'site' or p.publishstate='public')
group by t.id, t.rawname, t.name
ORDER BY
ct DESC,
t.name ASC)
WHERE rownum <= 20

Using the NLS settings above, this query returns the correct values for id and ct, but the values of name and rawname are the same for every row (the value of the entry with the largest id number). If you run the subquery independently, the result set is as expected.

If NLS_SORT=BINARY is set, then the result set is also as expected.

As this discussion is looking at dealing with case sensitivity at query level, would I be correct in setting:
NLS_SORT=BINARY and
NLS_COMP=LINGUISTIC

at session level?

Your advice would be much appreciated as this has been giving me a headache for two weeks now!

I have found out that my problem is a known bug:

Wrong result with NLS settings and inline view

Details: Wrong results are possible when NLS_SORT is set to any value other than 'BINARY', NLS_COMP to be 'LINGUISTIC', and the query has a nested query or inline view.

Fixed releases: 10.2.0.5 11.1.0.6

I am not working on Sam's suggestion and have this so far:

/**

• Returns the proper SQL to do LIKE in a case-insensitive way for oracle
• and Postgres
• Note the LIKE are case sensitive for Oracle. Oracle 10g is required to use
• the caseinsensitive search using regexp_like() or NLS_COMP=LINGUISTIC
• See http://docs.moodle.org/en/XMLDB_Problems#Case-insensitive_searches
  *
• @uses $CFG
• @param string $field Number of records per page
• @param string $data Number of records per page
• @param integer $operator Number of records per page
• @return string
  */
  function sql_olike($field, $data, $operator=0) {
  global $CFG;
  $not = '';

switch ($operator) { case 0: // contains $value = "'%$data%'"; $oci8_value = "'$data'"; break; case 1: // does not contain $not = 'NOT'; $value = "'%$data%'"; $oci8_value = "'$data'"; break; case 2: // equal to $value = "'$data'"; $oci8_value = "'^$data$'"; break; case 3: // starts with $value = "'$data%'"; $oci8_value = "'^$data'"; break; case 4: // ends with $value = "'%$data'"; $oci8_value = "'$data$'"; break; case 5: // empty $value = "''"; $oci8_value = "''"; break; case 7: // is defined break; }

switch ($CFG->dbfamily) { case 'postgres': return " $field $not ILIKE $value "; case 'oracle': return "$not regexp_like($field, $oci8_value, 'i') "; default: return " $field $not LIKE $value "; }
}

I am still fairly new to Moodle, and finding bug fixing for Oracle a real challenge. Any advice or comments would be appreciated.

Ooh I really should have previewed that before posting. Here is a file instead

This my complete patch file for incorporating case insensitive 'LIKE' for Oracle. Any comments or advice welcomed.

Hopefully solved now, there is a new sql_like() in 2.0dev. Please test it and file new issues if you have any suggestions for improvements.
Thanks everybody!

Petr Skoda

What about 1.9? All of our 1.9.n upgrades are horrendous because of our oracle tweaks. We will be using 1.9 for at least twelve months after Moodle 2.0 is released, to allow us to fully test the upgrade. I'm sure there are many others that will be using it for some time too. Could it be fixed for 1.9 please?

I am afraid you can not expect any major Oracle fixing in 1.9.x because the changes might create regressions. Anybody who wants Oracle usable in 2.0 should be already testing it now and reporting any problems found. Core developers are not testing for oracle at all, we rely on bug reports from Oracle users (MS SQL Server status is similar).

If you need to support sites that use oracle I would recommend you to create your own git branch with Oracle minifork for tracking of 1.9.x stable, there are other people that might be interested and help a bit too, you can try to post in the moodle.org forums. The good news for you is that no more changes are planned for 1.9.x - only critical and security fixes.

Petr Skoda