Opened 8 years ago

Closed 7 years ago

Last modified 7 years ago

#15659 closed Bug (fixed)

Contains lookups give wrong results on SQLite3 backend

Reported by: Malcolm Box Owned by: nobody
Component: Documentation Version: 1.3
Severity: Normal Keywords: sqlite
Cc: Malcolm Box Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


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


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

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


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: the fix is to issue a PRAGMA case_sensitive_like = yes; command on the session to get it to do things properly.

Change History (10)

comment:1 Changed 8 years ago by Malcolm Box

Cc: Malcolm Box added

comment:2 Changed 8 years ago by Malcolm Box

Version: 1.3-alphaSVN

Retested with 1.3-rc1 and behaviour is still present.

comment:3 Changed 8 years ago by Luke Plant

Resolution: needsinfo
Status: newclosed

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.


comment:4 Changed 8 years ago by Malcolm Box

Version: SVN1.3

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.

Last edited 8 years ago by Malcolm Box (previous) (diff)

comment:5 Changed 8 years ago by Malcolm Box

Resolution: needsinfo
Status: closedreopened

comment:6 Changed 8 years ago by Luke Plant

Type: Bug

comment:7 Changed 8 years ago by Luke Plant

Severity: Normal

comment:8 Changed 8 years ago by Luke Plant

Component: Database layer (models, ORM)Documentation
Triage Stage: UnreviewedAccepted

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:

LIKE Case-insensitiveCase 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

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

comment:9 Changed 7 years ago by Malcolm Tredinnick

Easy pickings: unset
Resolution: fixed
Status: reopenedclosed
UI/UX: unset

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

comment:10 Changed 7 years ago by Jacob

milestone: 1.3

Milestone 1.3 deleted

Note: See TracTickets for help on using tickets.
Back to Top