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 'FOO@email.com') LIMIT 1

This works completely wrong for me who has a DB with CI Collation. It will consider the 'FOO@email.com' valid, even if there is another user with 'foo@email.com' already in Users table. In other words, this means, that if the user of the application tries to create a new User with email 'FOO@email.com' 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('FOO@email.com') 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

Answer 3

validates :email, uniqueness: {case_sensitive: false}

Works like a charm in Rails 4.1.0.rc2

;)

Answer 4

After fighting with MySQL binary modifier, i found a way that removes that modifier from all queries comparing fields (not limited to uniqueness validation, but includes it).

First: Why MySQL adds that binary modifier? That's because by default MySQL compares fields in a case-insensitive way.

Second: Should I care? I always had design my systems to suppose that String comparison are made in a case-insensitive way, so that is a desired feature to me. Be warned if you don't

This is where is added the binary modifier:

https://github.com/rails/rails/blob/ee291b9b41a959e557b7732100d1ec3f27aae4f8/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb#L545

  def case_sensitive_modifier(node)
    Arel::Nodes::Bin.new(node)
  end

So i override this. I create an initializer (at config/initializers) named "mysql-case-sensitive-override.rb" with this code:

# mysql-case-sensitive-override.rb
class ActiveRecord::ConnectionAdapters::AbstractMysqlAdapter < ActiveRecord::ConnectionAdapters::AbstractAdapter
  def case_sensitive_modifier(node)
    node
  end
end

And that's it. No more binary modifier on my queries :D

Please notice that this does not explain why the "{case_sensitive: false}" option of the validator doesn't works, and does not solves it. It changes the default-and-unoverrideable-case-sensitive behavior for a default-and-unoverrideable-case-insensitive new behavior. I must insist, this also changes for any comparison that actually uses binary modifier for case-sensitive behavior (i hope).

Answer 5

Workaround
If you want a case-insensitive comparison do:

SELECT 1 FROM Users WHERE (Users.email LIKE 'FOO@email.com') LIMIT 1;

LIKE without wildcards always works like a case-insensitive =.
= can be either case sensitive or case-insensitive depending on various factors (casting, charset...)

Answer 6

starting with http://guides.rubyonrails.org/active_record_querying.html#finding-by-sql

then adding their input @Johan, @PanayotisMatsinopoulos and this http://guides.rubyonrails.org/active_record_validations_callbacks.html#custom-methods and http://www.w3schools.com/sql/sql_like.asp

then we have this:

 class User < ActiveRecord::Base
   validate :email_uniqueness

   protected

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