Re: [Django] #15659: Contains lookups give wrong results on SQLite3 backend

[Django]

#15659: Contains lookups give wrong results on SQLite3 backend
+---
   Reporter:  boxm  |  Owner:  nobody
   Type:  Bug   | Status:  closed
  Milestone:  1.3   |  Component:  Documentation
Version:  1.3   |   Severity:  Normal
 Resolution:  fixed |   Keywords:  sqlite
   Triage Stage:  Accepted  |  Has patch:  0
Needs documentation:  0 |Needs tests:  0
Patch needs improvement:  0 |  Easy pickings:  0
  UI/UX:  0 |
+---
Changes (by mtredinnick):

 * status:  reopened => closed
 * ui_ux:   => 0
 * resolution:   => fixed
 * easy:   => 0


Comment:

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

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To post to this group, send email to django-updates@googlegroups.com.
To unsubscribe from this group, send email to 
django-updates+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-updates?hl=en.

Re: [Django] #15659: Contains lookups give wrong results on SQLite3 backend

[Django]

#15659: Contains lookups give wrong results on SQLite3 backend
+-
   Reporter:  boxm  |Owner:  nobody
   Type:  Bug   |   Status:  reopened
  Milestone:  1.3   |Component:  Documentation
Version:  1.3   | Severity:  Normal
 Resolution:| Keywords:  sqlite
   Triage Stage:  Accepted  |Has patch:  0
Needs documentation:  0 |  Needs tests:  0
Patch needs improvement:  0 |
+-
Changes (by lukeplant):

 * component:  Database layer (models, ORM) => Documentation
 * stage:  Unreviewed => Accepted


Comment:

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

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To post to this group, send email to django-updates@googlegroups.com.
To unsubscribe from this group, send email to 
django-updates+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-updates?hl=en.

Re: [Django] #15659: Contains lookups give wrong results on SQLite3 backend

[Django]

#15659: Contains lookups give wrong results on SQLite3 backend
-+-
   Reporter:  boxm   |Owner:  nobody
 Status:  reopened   |Milestone:  1.3
  Component:  Database   |  Version:  1.3
  layer (models, ORM)| Keywords:  sqlite
 Resolution: |Has patch:  0
   Triage Stage: |  Needs tests:  0
  Unreviewed |
Needs documentation:  0  |
Patch needs improvement:  0  |
-+-
Changes (by boxm):

 * status:  closed => reopened
 * resolution:  needsinfo =>


-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To post to this group, send email to django-updates@googlegroups.com.
To unsubscribe from this group, send email to 
django-updates+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-updates?hl=en.

Re: [Django] #15659: Contains lookups give wrong results on SQLite3 backend

[Django]

#15659: Contains lookups give wrong results on SQLite3 backend
-+-
   Reporter:  boxm   |Owner:  nobody
 Status:  closed |Milestone:  1.3
  Component:  Database   |  Version:  1.3
  layer (models, ORM)| Keywords:  sqlite
 Resolution:  needsinfo  |Has patch:  0
   Triage Stage: |  Needs tests:  0
  Unreviewed |
Needs documentation:  0  |
Patch needs improvement:  0  |
-+-
Changes (by boxm):

 * version:  SVN => 1.3


Comment:

 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 lead to me missing
 it.

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To post to this group, send email to django-updates@googlegroups.com.
To unsubscribe from this group, send email to 
django-updates+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-updates?hl=en.

Re: [Django] #15659: Contains lookups give wrong results on SQLite3 backend

[Django]

#15659: Contains lookups give wrong results on SQLite3 backend
-+-
   Reporter:  boxm   |Owner:  nobody
 Status:  closed |Milestone:  1.3
  Component:  Database   |  Version:  SVN
  layer (models, ORM)| Keywords:  sqlite
 Resolution:  needsinfo  |Has patch:  0
   Triage Stage: |  Needs tests:  0
  Unreviewed |
Needs documentation:  0  |
Patch needs improvement:  0  |
-+-
Changes (by lukeplant):

 * status:  new => closed
 * resolution:   => needsinfo


Comment:

 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.

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To post to this group, send email to django-updates@googlegroups.com.
To unsubscribe from this group, send email to 
django-updates+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-updates?hl=en.

Re: [Django] #15659: Contains lookups give wrong results on SQLite3 backend

[Django]

#15659: Contains lookups give wrong results on SQLite3 backend
-+-
   Reporter:  boxm   |Owner:  nobody
 Status:  new|Milestone:  1.3
  Component:  Database   |  Version:  SVN
  layer (models, ORM)| Keywords:  sqlite
 Resolution: |Has patch:  0
   Triage Stage: |  Needs tests:  0
  Unreviewed |
Needs documentation:  0  |
Patch needs improvement:  0  |
-+-
Changes (by boxm):

 * version:  1.3-alpha => SVN


Comment:

 Retested with 1.3-rc1 and behaviour is still present.

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To post to this group, send email to django-updates@googlegroups.com.
To unsubscribe from this group, send email to 
django-updates+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-updates?hl=en.

Re: [Django] #15659: Contains lookups give wrong results on SQLite3 backend

[Django]

#15659: Contains lookups give wrong results on SQLite3 backend
-+-
   Reporter:  boxm   |Owner:  nobody
 Status:  new|Milestone:  1.3
  Component:  Database   |  Version:  1.3-alpha
  layer (models, ORM)| Keywords:  sqlite
 Resolution: |Has patch:  0
   Triage Stage: |  Needs tests:  0
  Unreviewed |
Needs documentation:  0  |
Patch needs improvement:  0  |
-+-
Changes (by boxm):

 * cc: boxm (added)
 * needs_better_patch:   => 0
 * needs_tests:   => 0
 * needs_docs:   => 0


-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To post to this group, send email to django-updates@googlegroups.com.
To unsubscribe from this group, send email to 
django-updates+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-updates?hl=en.

[Django] #15659: Contains lookups give wrong results on SQLite3 backend

[Django]

#15659: Contains lookups give wrong results on SQLite3 backend
--+---
 Reporter:  boxm  | Owner:  nobody
   Status:  new   | Milestone:  1.3
Component:  Database layer (models, ORM)  |   Version:  1.3-alpha
 Keywords:  sqlite|  Triage Stage:  Unreviewed
Has patch:  0 |
--+---
 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")
 []
 }}}

 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.

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To post to this group, send email to django-updates@googlegroups.com.
To unsubscribe from this group, send email to 
django-updates+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-updates?hl=en.