Several different results returned in case of "exact" case-sensitive query in django

Question

I have a django 1.2.1 instance running on ubuntu with mysql 5 backend. I am trying to make a case-sensitive query that should return only one result, but I get two results back that have the same content but with different cases.

I want to get an entry with the following heading: Cat Syndrome

So, I am using the following query:

c = Change.objects.filter(change_type='new',title__exact='Cat on the Internet syndrome') 

and I get the following results:

 >>> c [<Change: Change object>, <Change: Change object>] 

The headers of each change object:

 >>> for i in c: ... print i.title ... Cat on the Internet Syndrome Cat on the Internet syndrome 

As you can see, the “S” in the syndrome inside each object name has a different case of the Syndrome S. It seemed to me that I read the documentation [0] that all queries were specified by the “exact” type by default. I get the same results when I don't specify title__exact='Cat on the Internet syndrome' .

How do I ensure that case sensitivity is used in the query I set out above?

[0] http://docs.djangoproject.com/en/dev/ref/models/querysets/#std:fieldlookup-exact

Edit: Mysql version:

 mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1 

Answer 1

http://code.djangoproject.com/ticket/2170

To function correctly, change the sorting of MySql? Databases for latin1_swedish_cs or utf8_bin for case sensitivity comparisons.

Answer 2

This is probably due to tuning at the MySQL level. From the documentation :

By default, in the UTF-8 database, MySQL will use the utf8_general_ci_swedish command. This causes all string value comparisons to be case insensitive. That is, "Fred" and "freD" are considered equal at the database level. If you have a unique field constraint, it would be illegal to insert both “aa” and “AA” in the same column, as they are compared as equal (and therefore not unique) with the default value.

In many cases, this default value will not be a problem. However, if you really need case comparisons for a specific column or table, you must modify the column or table to use utf8_bin sorting.

Answer 3

There is a note about the link you gave regarding the comparison in MySQL:

MySQL Comparison

In MySQL, the sorting option on a database table determines whether exact comparisons are case sensitive. This is a database setup, not a Django setup. You can configure MySQL tables to be case-sensitive, but some trade-offs are involved. For more information about this, see the section in the database documentation.