How to instruct Rails to generate the correct SQL on uniqueness validation when case insensitive

Question

Assume Rails 3 with MySQL DB with Case Insensitive collation

What's the story:

Rails allows you to validate an attribute of a Model with the "uniqueness" validator. BUT the default comparison is CASE SENSITIVE according to Rails documentation.

Which means that on validation it executes SQL like the following:

SELECT 1 FROM `Users` WHERE (`Users`.`email` = BINARY '[email protected]') LIMIT 1

This works completely wrong for me who has a DB with CI Collation. It will consider the '[email protected]' valid, even if there is another user with '[email protected]' already in Users table. In other words, this means, that if the user of the application tries to create a new User with email '[email protected]' this would have been completely VALID (by default) for Rails and INSERT will be sent to db. If you do not happen to have unique index on e-mail then you are boomed - row will be inserted without problem. If you happen to have a unique index, then exception will be thrown.

Ok. Rails says: since your DB has case insensitive collation, carry out a case insensitive uniqueness validation. How is this done? It tells that you can override the default uniqueness comparison sensitivity by setting ":case_sensitive => false" on the particular attribute validator. On validation it creates the following SQL:

SELECT 1 FROM `Users` WHERE (LOWER(`Users`.`email`) = LOWER('[email protected]') LIMIT 1

which is a DISASTER on a database table Users that you have designed to have a unique index on the email field, because it DOES NOT USE the index, does full table scan.

I now see that the LOWER functions in SQL are inserted by the UniquenessValidator of ActiveRecord (file uniqueness.rb, module ActiveRecord, module Validations class UniquenessValidator). Here is the piece of code that does this:

if value.nil? || (options[:case_sensitive] || !column.text?)
  sql = "#{sql_attribute} #{operator}"
else
  sql = "LOWER(#{sql_attribute}) = LOWER(?)"
end

So Question goes to Rails/ActiveRecord and not to MySQL Adapter.

QUESTION: Is there a way to tell Rails to pass the requirement about uniqueness validation case sensitivity to MySQL adapter and not be 'clever' about it to alter the query? OR QUESTION REPHRASED FOR CLARIFICATION: Is there another way to implement uniqueness validation on an attribute (PLEASE, CAREFUL...I AM NOT TALKING ABOUT e-mail ONLY, e-mail was given as an example) with case sensitivity OFF and with generation of a query that will use a simple unique index on the corresponding column?

These two questions are equivalent. I hope that now, I make myself more clear in order to get more accurate answers.

Answer 1

Validate uniqueness without regard to case

If you want to stick to storing email in upper or lower case then you can use the following to enforce uniqueness regardless of case:

validates_uniqueness_of :email, case_sensitive: false

(Also see this question: Rails "validates_uniqueness_of" Case Sensitivity)

Remove the issue of case altogether

Rather than doing a case insensitive match, why not downcase the email before validating (and therefore also):

before_validation {self.email = email.downcase}

Since case is irrelevant to email this will simplify everything that you do as well and will head off any future comparisons or database searches you might be doing

Answer 2

I have searched around and the only answer, according to my knowledge today, that can be acceptable is to create a validation method that does the correct query and checks. In other words, stop using :uniqueness => true and do something like the following:

class User
  validate :email_uniqueness

  protected

  def email_uniqueness
    entries = User.where('email = ?', email)
    if entries.count >= 2 || entries.count == 1 && (new_record? || entries.first.id != self.id )
      errors[:email] << _('already taken')
    end
  end
end

This will definitely use my index on email and works both on create and update (or at least it does up to the point that I have tested that ok).

After asking on the RubyOnRails Core Google group

I have taken the following answer from RubyOnRails Core Google Group: Rails is fixing this problem on 3.2. Read this: https://github.com/rails/rails/commit/c90e5ce779dbf9bd0ee53b68aee9fde2997be123