db lookup methods not case sensitive on mysql

[derelm]

i noticed that the methods field__exact and field__iexact match case insensitive on mysql
given you have a field (choices=(('a','small a'),('A','capital a'))) field__exact will always match both choices.

possible solution:
in django.db.backends.mysql.base

OPERATOR_MAPPING = {
    'exact': '= BINARY %s',
    ...
}

oh and will iexact, icontains, istatswith, iendswith all match exactly the same rows anyways?

[Matt McClanahan <cardinal@…>]

This came up in IRC today, when somebody noted that it's resulting in an exception if you do FooModel.objects.get(charfield='foo') and have both 'foo' and 'Foo' in that field (Two rows returned instead of one as expected).

[James Bennett]

Fixing formatting in description.

[saintsjd@…]

I just updated the patch to work with the latest SVN. I can confirm that this works for me and seems to solve case sensitivity issues in mysql.

[Philippe Raoult]

Actually this is dependent on the field's collation but the default collation for most mysql installation is to be case-insensitive. So in short this is a real bug that has been undetected for a long time.

[Philippe Raoult]

added a regression test

[Malcolm Tredinnick]

(In [7798]) Fixed #2170 -- "exact" lookups in MySQL are now case-sensitive (the same as other backends).

This is a backwards incompatible change if you were relying on 'exact' being
case-insensitive. For that, you should be using 'iexact'.

[arne]

Even though the patch fixes the original problems, it now fails to lookup keys with non-ASCII characters:

>>> from sprint.julian.models import Julian
>>> x = Julian(name=u'\xf6')
>>> x.save()
>>> y = Julian.objects.get(name=u'\xf6')
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/Library/Python/2.5/site-packages/django/db/models/manager.py", line 81, in get
    return self.get_query_set().get(*args, **kwargs)
  File "/Library/Python/2.5/site-packages/django/db/models/query.py", line 300, in get
    % self.model._meta.object_name)
DoesNotExist: Julian matching query does not exist.
>>> 

Omitting the BINARY in 'exact' allows such lookup.

Indeed, it appears that MySQL consistently fails to perform lookup of non-ASCII strings across various front-ends (tested with phpMyAdmin and mysql command line).

[arne]

Follow-up: It turns out that MySQL *will* correctly lookup non-ASCII characters even for =BINARY, as long as the database character set is UTF-8 (i.e. the same as the connection character set). As the documentation already suggests to create MySQL databases with character set utf8, there probably is nothing more that can be done. Perhaps this point about using UTF-8 can be emphasized a bit more, explaining that it otherwise just won't work.

[Karen Tracey <kmtracey@…>]

Actually the non-ASCII problem is really a dup of #8102 and I'm still somewhat hopeful that something can be done about it. It can work if Django will only specify a collation, not flat-out BINARY for the comparison.

[Malcolm Tredinnick]

(In [8319]) Changed "exact" matches in MySQL to use the database's native collation.

This effectively reverses the change in [7798]. It was proving too difficult to
successfully manage all the side effects here and provide a satisfactory
solution for everybody. Many thanks to arne, Martin von Löwis and, particular,
Karen Tracey, for doing a lot of research and proto-patches here to establish what was possible and practical.

This is backwards incompatible if you were relying on the behaviour after
[7798]. The docs have been updated to indicate the solution.

Refs #2170, #7789, #8102.

[hrauch]

As noted in the documentation, I tried to change the collation within my Django-database. So I dumped the database and changed "utf8_german_ci" to "utf8_bin". After restoring the data and trying to open one page, I'll get the following error:

[Fri Aug 15 08:52:23 2008] [error] [client 192.168.0.222] mod_python (pid=2259, interpreter='dms', phase='PythonHandler', handler='django.core.handlers.modpython'): Application error
[Fri Aug 15 08:52:23 2008] [error] [client 192.168.0.222] ServerName: 'www.dms.bildung.hessen.de'
[Fri Aug 15 08:52:23 2008] [error] [client 192.168.0.222] DocumentRoot: '/srv/www/htdocs'
[Fri Aug 15 08:52:23 2008] [error] [client 192.168.0.222] URI: '/news/index.html'
[Fri Aug 15 08:52:23 2008] [error] [client 192.168.0.222] Location: None
[Fri Aug 15 08:52:23 2008] [error] [client 192.168.0.222] Directory: None
[Fri Aug 15 08:52:23 2008] [error] [client 192.168.0.222] Filename: '/srv/www/htdocs/news'
[Fri Aug 15 08:52:23 2008] [error] [client 192.168.0.222] PathInfo: '/index.html'
[Fri Aug 15 08:52:23 2008] [error] [client 192.168.0.222] Traceback (most recent call last):

[Fri Aug 15 08:52:23 2008] [error] [client 192.168.0.222] File "/usr/lib64/python2.5/site-packages/mod_python/importer.py", line 1537, in HandlerDispatch\n default=default_handler, arg=req, silent=hlist.silent)
[Fri Aug 15 08:52:23 2008] [error] [client 192.168.0.222] File "/usr/lib64/python2.5/site-packages/mod_python/importer.py", line 1229, in _process_target\n result = _execute_target(config, req, object, arg)
[Fri Aug 15 08:52:23 2008] [error] [client 192.168.0.222] File "/usr/lib64/python2.5/site-packages/mod_python/importer.py", line 1128, in _execute_target\n result = object(arg)
[Fri Aug 15 08:52:23 2008] [error] [client 192.168.0.222] File "/usr/local/lib64/python2.5/site-packages/django/core/handlers/modpython.py", line 210, in handler\n return ModPythonHandler()(req)
[Fri Aug 15 08:52:23 2008] [error] [client 192.168.0.222] File "/usr/local/lib64/python2.5/site-packages/django/core/handlers/modpython.py", line 183, in call\n response = self.get_response(request)
[Fri Aug 15 08:52:23 2008] [error] [client 192.168.0.222] File "/usr/local/lib64/python2.5/site-packages/django/core/handlers/base.py", line 67, in get_response\n response = middleware_method(request)
[Fri Aug 15 08:52:23 2008] [error] [client 192.168.0.222] File "/usr/local/lib64/python2.5/site-packages/django/middleware/locale.py", line 16, in process_request\n language = translation.get_language_from_request(request)
[Fri Aug 15 08:52:23 2008] [error] [client 192.168.0.222] File "/usr/local/lib64/python2.5/site-packages/django/utils/translation/init.py", line 97, in get_language_from_request\n return real_get_language_from_request(request)
[Fri Aug 15 08:52:23 2008] [error] [client 192.168.0.222] File "/usr/local/lib64/python2.5/site-packages/django/utils/translation/trans_real.py", line 351, in get_language_from_request\n lang_code = request.session.get('django_language', None)
[Fri Aug 15 08:52:23 2008] [error] [client 192.168.0.222] File "/usr/local/lib64/python2.5/site-packages/django/contrib/sessions/backends/base.py", line 50, in get\n return self._session.get(key, default)
[Fri Aug 15 08:52:23 2008] [error] [client 192.168.0.222] File "/usr/local/lib64/python2.5/site-packages/django/contrib/sessions/backends/base.py", line 147, in _get_session\n self._session_cache = self.load()
[Fri Aug 15 08:52:23 2008] [error] [client 192.168.0.222] File "/usr/local/lib64/python2.5/site-packages/django/contrib/sessions/backends/db.py", line 19, in load\n return self.decode(s.session_data)
[Fri Aug 15 08:52:23 2008] [error] [client 192.168.0.222] File "/usr/local/lib64/python2.5/site-packages/django/contrib/sessions/backends/base.py", line 80, in decode\n encoded_data = base64.decodestring(session_data)

[Russell Keith-Magee]

Please don't revert tickets that have been closed unless you are certain that the bug in question has not been resolved. If you want to help working out why an error message is occurring, please ask on the mailing list.

Marking for v1.0 beta won't help either - 1.0 beta has been shipped.

[Brandon Carl]

This bug is somehow still present in the latest trunk release. (currently 1.2 beta 1)

The same bug fix that was applied before can be applied again. Don't know how the bug re-appeared, but it's really annoying hah.

[Karen Tracey]

Yes, the fix for this was reverted, after much investigation and attempt to make it work. The problems it introduced were worse than the problem it fixed. So this bug is essentially wontfix. (Though if I'm remembering right there may be another one open to look for a better fix -- but the fix here is definitely not viable.) See: ​http://code.djangoproject.com/ticket/2170#comment:11 and the other tickets linked.

[Brandon Carl]

Just to note: this "fix" was reverted in ​http://code.djangoproject.com/changeset/8319
To get the functionally correctly, change the Collation of the MySql database to latin1_swedish_cs or utf8_bin for case sensitive comparisons.