Rails 3.0.3 - validates_uniqueness_of case_sensitive fails on UTF8 fields

[lighthouse-import]

Imported from Lighthouse. Original ticket at: http://rails.lighthouseapp.com/projects/8994/tickets/6691
Created by Shawn - 2011-04-08 23:35:29 UTC

Hi,

Sorry if this has been fixed in a later release, or is an issue in MySQL, but I'm experiencing an issue with validates_uniqueness_of in my app. Specifically, when I try to violate the rule the validation does not return an error.

Database is MYSQL 5.0.1 (have tried on later versions as well)
Rails Version is 3.0.3
Ruby Version = 1.9.2p180
Connection encoding is; UTF8
MySQL collation on table / field is: utf8_general_ci
Platforms are Windows and Linux

Example code:

On my User model:

validates_uniqueness_of :login

In Rails console I find a user record and set it's login to a login that's in use on another record. Then check user.valid? and it's true when it should be false.

The validator emits the following statement:

SELECT users.id FROM users WHERE (users.login = BINARY 'my-dupe-id') AND (users.id <> 66664) LIMIT 1

This query returns no results.

Adding the :case_sensitive => true option gets things working as the validator is forcing both values to LOWER() and not using the BINARY qualifier.

Again, sorry if this is something wrong with MySQL (as in the product of how I have the database set up) and thanks for reading this and supporting Rails.

Shawn