Contains lookups give wrong results on SQLite3 backend

[Malcolm Box]

Queryset filters using contains for a case-sensitive match give the wrong results on SQLite3 backend, as a case-insensitive compare is done.

Example:

class Test(models.Model):
  string = models.TextField()

test = Test.objects.create(string="CASE sensitive")

Test.objects.filter(string__contains="case")
[<Test>]

But this should not have found the object as contains is supposed to be case sensitive.

Seen using SQLite3 2.4.1

According to the SQLite documentation here: ​http://www.sqlite.org/pragma.html#pragma_case_sensitive_like the fix is to issue a PRAGMA case_sensitive_like = yes; command on the session to get it to do things properly.

[Malcolm Box]

Retested with 1.3-rc1 and behaviour is still present.

[Luke Plant]

That fix does not sound practical, as it simply turns case-sensitivity on or off, which we could afford to do once per connection, but not more than that. And if we were to fix this, we would still need to be able to run case insensitive queries, and even both at once e.g. Test.objects.filter(string1__contains="Something", string2__icontains="something")

If it is not possible to do the above, we will have to close INVALID as it is simply a limitation of the underlying database, and not a bug in Django, and changing the behaviour would produce the opposite bug with icontains.

So I'm closing NEEDSINFO for now - please re-open if you can provide information about how a proper fix might be implemented in Django.

Thanks.

[Malcolm Box]

The GLOB operator is case-sensitive so could be used in place of LIKE for contains queries - it uses unix wildcards rather than SQL ones, but that's containable in the operators() array.

Alternatively, and probably too hard, it looks like this is fixable using the sqlite3_create_function() to replace the definition of LIKE with one that does the right thing (presumably including working for icontains as well).

A minimal fix would be to document this limitations in the SQLite database notes - at the moment the info is split between the contains/icontains filter documentation and the SQLite db notes - which led to me missing it.

[Luke Plant]

Having looked at the documentation for GLOB and LIKE, we've got some problems, which I may as well document here.

Without the case_sensitive_like pragma, we have this:

	ASCII	Non ASCII
LIKE	Case-insensitive	Case sensitive
GLOB	Case-sensitive	Case sensitive

(With the case_sensitive_like=true, just the top left is changed, which is not an improvement). Unfortunately, this means we've got problems with Unicode whichever way we go, because we don't have a way of doing case insensitive matching.

Being able to specify case-sensitive matching for ASCII is still a useful feature, but looking at GLOB, this doesn't seem possible, because it cannot be used as a drop in replacement for LIKE with different wildcards, since it lacks the ESCAPE clause that LIKE has, which we need. See ​http://www.sqlite.org/lang_corefunc.html

A documentation improvement would be helpful though, and I'm accepting on that basis. The icontains lookup, the contains lookup, the iexact lookup and the SQLite DB notes could all do with consistent or cross referenced information (some of it is there already).

[Malcolm Tredinnick]

This was all mostly documented already, but I've committed a more explicit callout of the problem in a couple of places in r16694 (and promptly attributed it to the wrong ticket number so it didn't show up here).

[Jacob]

Milestone 1.3 deleted