icontains case-sensitive on MySQL

[AndyB]

I've googled and the only problems seem to be with people trying to do
case-sensite lookups.

What could be going wrong here?:

>>> b.filter(name__icontains='Saff')
[<Building: 27276: Saffron Court>]
>>> b.filter(name__icontains='saff')
[]

MySql 5.0.51
Django 1.0
Tables are InnoDB UTF8 collation UTF8 bin
Tried it on a my dev machine and on the live site (webfaction) which
is running a pre 1.0 checkout.

[Ronny Haryanto]

On Fri, Oct 17, 2008 at 12:55 AM, AndyB <and...@gmail.com> wrote:
> I've googled and the only problems seem to be with people trying to do
> case-sensite lookups.
>
> What could be going wrong here?:
>
>>>> b.filter(name__icontains='Saff')
> [<Building: 27276: Saffron Court>]
>>>> b.filter(name__icontains='saff')
> []

Could you do:

from django.db import connection
connection.queries

to see the raw SQL queries?

Ronny

[AndyB]

Well - someone on #Django told me to check encoding settings and I did
but I failed to read the following:
http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

Using binary collation changes the default sensitivity of string
comparisons.

Is this not something that Django should be abstracting away?

On Oct 16, 7:10 pm, "Ronny Haryanto" <ro...@haryan.to> wrote:

[Malcolm Tredinnick]

On Thu, 2008-10-16 at 11:15 -0700, AndyB wrote:
> Well - someone on #Django told me to check encoding settings and I did
> but I failed to read the following:
> http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html
>
> Using binary collation changes the default sensitivity of string
> comparisons.
>
> Is this not something that Django should be abstracting away?

No. You set the database collation and Django respects that. We can't do
anything else, since the comparison takes place at the database level.

The other one is that we (particularly Karen Tracey, myself and a couple
of other people) spent a lot of time trying to see if we could make
things like that work regardless of collation (particularly
case-sensitive matching with the default utf_*_ci collations), but it's
really, really hard. Really hard. There are just too many side-effects
and, ultimately, it becomes hard to understand what's going on in a
practical matter, since we almost always defer to the database on
matters on how a particular SQL query will be executed and this would be
one situation when we didn't (sometimes). So it's entirely left up to
how you configure your database. One of the trade-offs the developer
chooses to make when selecting MySQL.

Regards,
Malcolm

[Jake Gordon]

There needs to at least be a warning in the documentation.  I spent a lot of time debugging only to find this was a problem with Django.

[Mike Dewhirst]

On 1/06/2015 5:57 AM, Jake Gordon wrote:
> There needs to at least be a warning in the documentation.

https://docs.djangoproject.com/en/1.8/ref/databases/#collation-settings

> --
> You received this message because you are subscribed to the Google
> Groups "Django users" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to django-users...@googlegroups.com
> <mailto:django-users...@googlegroups.com>.
> To post to this group, send email to django...@googlegroups.com
> <mailto:django...@googlegroups.com>.
> Visit this group at http://groups.google.com/group/django-users.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-users/d8bbe872-8182-421d-a1bf-eb9ea9dc6035%40googlegroups.com
> <https://groups.google.com/d/msgid/django-users/d8bbe872-8182-421d-a1bf-eb9ea9dc6035%40googlegroups.com?utm_medium=email&utm_source=footer>.
> For more options, visit https://groups.google.com/d/optout.