Django

Code

Ticket #2170 (closed: fixed)

Opened 2 years ago

Last modified 3 months ago

db lookup methods not case sensitive on mysql

Reported by: derelm Assigned to: PhiR
Milestone: 1.0 Component: Database layer (models, ORM)
Version: SVN Keywords:
Cc: Triage Stage: Ready for checkin
Has patch: 1 Needs documentation: 0
Needs tests: 0 Patch needs improvement: 0

Description (Last modified by ubernostrum)

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?

Attachments

2170-1.diff (0.8 kB) - added by Matt McClanahan <cardinal@dodds.net> on 06/22/07 01:16:06.
fix-for-mysql-case-sensitivity.diff (0.9 kB) - added by anonymous on 09/20/07 12:53:54.
2170-fix-with-test.diff (1.7 kB) - added by PhiR on 03/18/08 12:25:43.
added a regression test

Change History

02/17/07 11:32:13 changed by Simon G. <dev@simon.net.nz>

  • stage changed from Unreviewed to Accepted.

06/22/07 01:16:06 changed by Matt McClanahan <cardinal@dodds.net>

  • attachment 2170-1.diff added.

06/22/07 01:18:09 changed by Matt McClanahan <cardinal@dodds.net>

  • has_patch set to 1.

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

06/22/07 01:23:24 changed by ubernostrum

  • description changed.

Fixing formatting in description.

09/20/07 12:53:33 changed by saintsjd@gmail.com

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.

09/20/07 12:53:54 changed by anonymous

  • attachment fix-for-mysql-case-sensitivity.diff added.

03/18/08 12:25:02 changed by PhiR

  • owner changed from nobody to PhiR.
  • status changed from new to assigned.
  • stage changed from Accepted to Ready for checkin.

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.

03/18/08 12:25:43 changed by PhiR

  • attachment 2170-fix-with-test.diff added.

added a regression test

06/16/08 12:25:54 changed by telenieko

  • milestone set to 1.0.

06/30/08 05:25:36 changed by mtredinnick

  • status changed from assigned to closed.
  • resolution set to fixed.

(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'.

08/09/08 05:55:33 changed by arne

  • status changed from closed to reopened.
  • resolution deleted.

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

08/09/08 06:10:04 changed by arne

  • status changed from reopened to closed.
  • resolution set to fixed.

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.

08/09/08 09:50:14 changed by Karen Tracey <kmtracey@gmail.com>

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.

08/12/08 02:52:33 changed by mtredinnick

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

08/15/08 02:09:18 changed by hrauch

  • status changed from closed to reopened.
  • resolution deleted.
  • milestone changed from 1.0 to 1.0 beta.

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)

08/15/08 02:14:31 changed by russellm

  • status changed from reopened to closed.
  • resolution set to fixed.
  • milestone changed from 1.0 beta to 1.0.

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.

Add/Change #2170 (db lookup methods not case sensitive on mysql)




Change Properties
Action