Re: [Django] #9682: icontains can be case-sensitive on MySQL
Django
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
to.roma.from.djbug@… | Status: new
Type: Bug | Version: 1.0
Component: Database layer | Resolution:
(models, ORM) | Triage Stage:
Severity: Normal | Someday/Maybe
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by filias):
Just bumped into this recently and also want to vote for this patch.
How/When can we see it in django?
--
Ticket URL: <https://code.djangoproject.com/ticket/9682#comment:10>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
to.roma.from.djbug@… | Status: new
Type: Bug | Version: 1.0
Component: Database layer | Resolution:
(models, ORM) | Triage Stage:
Severity: Normal | Someday/Maybe
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by anonymous):
Just bumped into this recently as well. We have columns that are utf8_bin,
and iexact operators on Django mysql ORM do _not_ perform case-insensitive
lookups on these columns.
--
Ticket URL: <https://code.djangoproject.com/ticket/9682#comment:11>
Django
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
to.roma.from.djbug@… | Status: new
Type: Bug | Version: 1.0
Component: Database layer | Resolution:
(models, ORM) | Triage Stage:
Severity: Normal | Someday/Maybe
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by anonymous):
Same here: MySQL+utf8_bin. I spent an hour to find out that it is a bug in
Django...
--
Ticket URL: <https://code.djangoproject.com/ticket/9682#comment:12>
Django
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
to.roma.from.djbug@… | Status: new
Type: Bug | Version: 1.0
Component: Database layer | Resolution:
(models, ORM) | Triage Stage:
Severity: Normal | Someday/Maybe
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by anonymous):
Same problem here. Is there some workaround? It would be great to have
some warning in documentation.
--
Ticket URL: <https://code.djangoproject.com/ticket/9682#comment:13>
Django
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
to.roma.from.djbug@… |
Component: Database layer | Version: 1.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Someday/Maybe
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by mzaccariotto):
Hi, to support CI search with utf8_bin collation, wrap firstly your
queryset into the following ext() decorator and you're done in nearly
every case:
{{{
# coding: UTF-8
from django.db.models.query import EmptyQuerySet
try:
from django.db.backends.mysql.base import DatabaseOperations as
MySqlDatabaseOperations
from django.db.models.query import QuerySet
from django.db.models import sql
from django.db.models.sql.where import WhereNode
import copy
class ExtMySqlDatabaseOperations(MySqlDatabaseOperations):
def lookup_cast(self, lookup_type):
if lookup_type in ('iexact', 'icontains', 'istartswith',
'iendswith'):
return "LOWER(%s)"
return super(ExtMySqlDatabaseOperations,
self).lookup_cast(lookup_type)
class ExtWhereNode(WhereNode):
def make_atom(self, child, qn, connection):
lvalue, lookup_type, value_annotation, params_or_value = child
old_ops = None
if type(connection.ops) in (MySqlDatabaseOperations,
ExtMySqlDatabaseOperations):
if lookup_type in ('iexact', 'icontains', 'istartswith',
'iendswith'):
params_or_value = params_or_value.lower()
old_ops = connection.ops
connection.ops =
ExtMySqlDatabaseOperations(connection)
atom = WhereNode.make_atom(self, (lvalue, lookup_type,
value_annotation, params_or_value), qn, connection)
if old_ops:
connection.ops = old_ops
return atom
@classmethod
def deep_convert(cls, node):
if type(node) is WhereNode:
copy = ExtWhereNode(connector=node.connector,
negated=node.negated)
copy.children = map(cls.deep_convert, node.children)
copy.subtree_parents = map(cls.deep_convert,
node.subtree_parents)
return copy
return node
class ExtQuerySet(QuerySet):
def __init__(self, model=None, query=None, using=None):
if not query:
query = sql.Query(model, where = ExtWhereNode)
elif type(query.where) is not ExtWhereNode:
newquery = sql.Query(model, where = ExtWhereNode)
newquery.where = ExtWhereNode.deep_convert(query.where)
query = newquery
super(ExtQuerySet, self).__init__(model = model, query =
query, using = using)
def ext(qs):
if isinstance(qs, EmptyQuerySet):
return qs
else:
return ExtQuerySet(model=qs.model, query = qs.query,
using=qs._db)
except:
def ext(qs):
return qs
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/9682#comment:14>
Django
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
to.roma.from.djbug@… |
Type: Bug | Status: new
Component: Database layer | Version: 1.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Someday/Maybe
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by ringerc):
Readers should note that `COLLATE` is SQL-standard, though unfortunately
the collation ''names'' are not. You will be able to use a solution that
implements the `COLLATE` clause in PostgreSQL too, e.g.
`SELECT * FROM collate_demo WHERE test_col > 'A' COLLATE "C" ORDER BY
test_col COLLATE "C";`
to use POSIX byte-order based collation.
I ''strongly'' recommend a solution using the `COLLATE` clause, but keep
in mind that it's not a global query modifier. It affects the operator or
`ORDER BY` clause it's attached to, not the whole query.
''(I don't use Django, I contribute to PostgreSQL and I'm here because of
some research I was doing for a Stack Overflow user who runs Django on
Pg)''.
--
Ticket URL: <https://code.djangoproject.com/ticket/9682#comment:15>
Django
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
to.roma.from.djbug@… |
Type: Bug | Status: new
Component: Database layer | Version: 1.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Someday/Maybe
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: closed => new
* resolution: fixed =>
Comment:
The addition of {{{Collate()}}} is great, but it didn't solve the main
problem of this ticket. Django should properly use the existing lookups
independent of the database collation. I came to this ticket because I
realized that is not possible to use {{{ModelAdmin.search_fields}}} in the
way is presented in the docs:
''When somebody does a search in the admin search box, Django splits
the search query into words and returns all objects that contain each of
the words, case-insensitive...''
I discover this after the need of use MySQL instead of PostgreSQL.
--
Ticket URL: <https://code.djangoproject.com/ticket/9682#comment:17>