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

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: