Very strange bug in MySQL backend for exact query.

[bear330]

I have a User object whose username is 'jumann'.

When I do this:

>>> User.objects.get(username__exact='Jumann')

I got a user object <User: jumann>. This is bug?

The SQL generated is:

>>>  connection.queries[-1]['sql']
u'SELECT `auth_user`.`id`, `auth_user`.`username`, `auth_user`.`first_name`, `auth_user`.`last_name`, `auth_user`.`email`, `auth_user`.`password`, `auth_user`.`is_staff`, `auth_user`.`is_active`, `auth_user`.`is_superuser`, `auth_user`.`last_login`, `auth_user`.`date_joined` FROM `auth_user` WHERE `auth_user`.`username` = Jumann '

This is a invalid SQL statement, because the string Jumann is not quoted.
But I don't know why it will pass and give me a wrong result.

This problem is very serious...

[Russell Keith-Magee]

This isn't a bug in Django. It's a problem with MySQL - or at least, with with way your MySQL server is configured.

Your MySQL installation is set up to use a text field collation that is case insensitive. In the alpha/beta series, we briefly toyed with working around this issue, but it turned out to cause more problems than it solved.

If you don't want case insensitive field comparisons, you need to set up your MySQL install to use a different collation.

[Karen Tracey]

Replying to russellm:

If you don't want case insensitive field comparisons, you need to set up your MySQL install to use a different collation.

And you'll want to be sure to read: ​http://docs.djangoproject.com/en/dev/ref/databases/#mysql-collation to avoid getting hit by a known gothca resulting from using a binary collation. Also it's not noted there (but is somewhere in the MySQL doc pointed to), but using a binary collation will change the ordering results. The default case-insensitive collation give dictionary ordering (a, A, z, Z) whereas binary collation gives binary ordering (A, Z, a, z).