Code

Opened 6 years ago

Closed 6 years ago

Last modified 6 years ago

#8877 closed (invalid)

Very strange bug in MySQL backend for exact query.

Reported by: bear330 Owned by: nobody
Component: Core (Other) Version: 1.0
Severity: Keywords: get exact SQL ORM MySQL
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

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

Attachments (0)

Change History (2)

comment:1 follow-up: Changed 6 years ago by russellm

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to invalid
  • Status changed from new to closed

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.

comment:2 in reply to: ↑ 1 Changed 6 years ago by kmtracey

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

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.