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.

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.

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

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