binarylogic/authlogic — pull request #339 by cris (2012-11-27 08:21:28+00:00)
Title: Rollback to LOWER comparison instead of (I)LIKE because of bug and poor Postgres performance

Hi, Ben.

I suggest to revert changes with LIKE back to LOWER, because this solution doesn't add any benefits and introduce subtle bug.

1) (I)LIKE is used without any quotiong. This leads to login/email collision. For example, user1 has email: user1@gmail.com and user2 has email: user_@gmail.com. If user1 is registered before user2, user2 isn't able to login. Because query:

```sql
SELECT "users".* FROM "users" WHERE ("users".email ILIKE 'user_@gmail.com') LIMIT 1;
```

allways returns user1 record. Take a look on issue 321, they already obtain this email collision: binarylogic/authlogic/issues/#321

2) For MySQL case-insensitive is usualy default, because Rails creates database with collation "utf8_general_ci", and with such collation search by string fields is case insensitive. So, MySQL doesn't benefit from LIKE, because it doesn't make sense for it to use such option. Plane find_by_#{field} does all in case-insensitive manner.

3) For Postgres ILIKE only brings new issue instead of solving something.
First of all, [ILIKE can't use indexes](http://archives.postgresql.org/pgsql-novice/2010-06/msg00136.php). You can clearly see this via EXPLAIN:

```sql
EXPLAIN SELECT "users".* FROM "users" WHERE ("users".email ILIKE 'user_@gmail.com') LIMIT 1;
QUERY PLAN
-------------------------------------------------------------------
Limit (cost=0.00..846.95 rows=1 width=1903)
-> Seq Scan on users (cost=0.00..32184.19 rows=38 width=1903)
Filter: ((email)::text ~~* 'user_@gmail.com'::text)
(3 rows)
```
What is worse, now I can't use Postgres ability to create index on function. For previous version of Authlogic I've created index on expression: LOWER(email) and it provides me with required functionality.

```sql
explain SELECT "users".* FROM "users" WHERE (LOWER("users".email) = 'user_@gmail.com') LIMIT 1;
QUERY PLAN
----------------------------------------------------------------------------------------------
Limit (cost=0.00..8.48 rows=1 width=1903)
-> Index Scan using index_users_lower_email on users (cost=0.00..8.48 rows=1 width=1903)
Index Cond: (lower((email)::text) = 'user_@gmail.com'::text)
```

After update to the latest version of AuthLogic I lost such ability.

Please, apply this pull-request to handle all caveats.