Opened 5 years ago

Closed 4 years ago

Last modified 4 years ago

#15659 closed Bug (fixed)

Contains lookups give wrong results on SQLite3 backend

Reported by: boxm Owned by: nobody
Component: Documentation Version: 1.3
Severity: Normal Keywords: sqlite
Cc: boxm 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 5 years ago by boxm

  • Cc boxm added
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

comment:2 Changed 5 years ago by boxm

  • Version changed from 1.3-alpha to SVN

Retested with 1.3-rc1 and behaviour is still present.

comment:3 Changed 5 years ago by lukeplant

  • Resolution set to needsinfo
  • Status changed from new to closed

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 5 years ago by boxm

  • Version changed from SVN to 1.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 5 years ago by boxm (previous) (diff)

comment:5 Changed 5 years ago by boxm

  • Resolution needsinfo deleted
  • Status changed from closed to reopened

comment:6 Changed 5 years ago by lukeplant

  • Type set to Bug

comment:7 Changed 5 years ago by lukeplant

  • Severity set to Normal

comment:8 Changed 5 years ago by lukeplant

  • Component changed from Database layer (models, ORM) to Documentation
  • Triage Stage changed from Unreviewed to Accepted

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 4 years ago by mtredinnick

  • Easy pickings unset
  • Resolution set to fixed
  • Status changed from reopened to closed
  • 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 4 years ago by jacob

  • milestone 1.3 deleted

Milestone 1.3 deleted

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