Opened 13 years ago

Closed 13 years ago

Last modified 12 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

Description

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.

Change History (10)

comment:1 by Malcolm Box, 13 years ago

Cc: Malcolm Box added

comment:2 by Malcolm Box, 13 years ago

Version: 1.3-alphaSVN

Retested with 1.3-rc1 and behaviour is still present.

comment:3 by Luke Plant, 13 years ago

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.

Thanks.

comment:4 by Malcolm Box, 13 years ago

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 13 years ago by Malcolm Box (previous) (diff)

comment:5 by Malcolm Box, 13 years ago

Resolution: needsinfo
Status: closedreopened

comment:6 by Luke Plant, 13 years ago

Type: Bug

comment:7 by Luke Plant, 13 years ago

Severity: Normal

comment:8 by Luke Plant, 13 years ago

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:

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

comment:9 by Malcolm Tredinnick, 13 years ago

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 by Jacob, 12 years ago

milestone: 1.3

Milestone 1.3 deleted

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