MySQL BINARY WHERE Clauses

[David Cramer]

SELECT * FROM `spider_discoveredlink` WHERE
`spider_discoveredlink`.`url` = BINARY 'http://www.starcraft.org/maps/
scums/multiplayerums/multiplayerrpg/Shinhwa+2008';

is NOT the same as

SELECT * FROM `spider_discoveredlink` WHERE
`spider_discoveredlink`.`url` = 'http://www.starcraft.org/maps/scums/
multiplayerums/multiplayerrpg/Shinhwa+2008';

Just FYI, you are breaking all of my queries :)

[David Cramer]

I was using utf8_general. I'm swapping to utf8_bin to attempt to fix
it, but binary encodings cause problems as well with unique indexes or
something similar (can't remember what my test case was from Curse).

[Karen Tracey]

This topic would seem better suited to django-users, since you are not discussing developing django, but using it.  You are also not really providing enough information to help you.  I do not know which of those queries you actually want, what Django lookup methods you have tried to achieve the results you are looking for, etc.  If you follow up on django-users with specifics of what Django code you are running, what results you were hoping for and what you are seeing instead, someone might be able to suggest something.

Karen

[David Cramer]

I don't want to seem harsh Karen, but I understand the differences in the user lists. This is not an issue with how I'm using Django, it's an issue with what Django's doing. This may be better suited as a ticket, but I'd rather not end up with another trac ticket that emails me daily because it turns into a discussion on how it should work.

In summary, this is, in fact, a problem in the Django codebase, and need's addressed, as it's causing issues for myself, and probably a number of other people, even if they haven't realized it yet.

--
David Cramer
Director of Technology
iBegin
http://www.ibegin.com/

[Mike Scott]

David,

We know you know the difference, but you should also know how much we
love detail. More detail is also needed here.

--
James Thurber - "Women are wiser than men because they know less and
understand more."

[Malcolm Tredinnick]

On Tue, 2008-07-22 at 01:29 -0500, David Cramer wrote:
[...]

> In summary, this is, in fact, a problem in the Django codebase, and
> need's addressed, as it's causing issues for myself, and probably a
> number of other people, even if they haven't realized it yet.

So let's start off by assuming that we understand the two are different.
If they were the same, it wouldn't have been necessary to make any
changes. Let's also assume you've read
http://code.djangoproject.com/wiki/BackwardsIncompatibleChanges#ExactComparisonsRespectCaseInMySQL which is the relevant change.

Now, given the purpose of that change, what is the problem you perceive
here and why isn't it fixed by moving to an iexact query if you want the
non-binary form of matching?

You haven't actually reported a problem yet. You've noted that two
non-identical queries behave differently. That's usually the case with
two different queries.

Regards,
Malcolm

[David Cramer]

Sorry, to be more clear, that is an *exact* match on what is in the database, but using the BINARY form does not return the result.

[Malcolm Tredinnick]

David,

On Tue, 2008-07-22 at 01:51 -0500, David Cramer wrote:
> Sorry, to be more clear, that is an *exact* match on what is in the
> database, but using the BINARY form does not return the result.

Please open a ticket for this so it doesn't get forgotten. You'll
somehow have to manage the mail that it generates (which might be as
high as a dozen pieces of email in a an entire week; less than the pace
of mail in this particular thread). We're obviously not going to merely
revert the change without further work since it was made to fix a bug
and changing something that hides an unknown problem and reintroduces a
known one is not progress. This is certainly something that would be
good to fix before 1.0 and fixing bugs is what we do. So once we have
repeatable information in a place that doesn't get overlooked, we can
make sure it's handled appropriately.

You have a failing example for your setup, so a small patch against
regressiontests/string_lookups/models.py that demonstrates the problem
will, I'm sure, be helpful to the people who volunteer their time to
help fix this problem.

Thanks,
Malcolm