WTF of the day: MySQL integer<->string comparison gotcha

wtf.md

To implement Ratchet.io's Person Tracking feature, we have a MySQL table like this:

create table person (
  id int unsigned not null,
  project_id int unsigned not null
  environment varchar(255) not null,
  person_id varchar(40) not null,
  username varchar(255),
  email varchar(255),
  primary key (id),
  unique (project_id, environment, person_id)
) engine=innodb default charset=utf8;

I was getting some grief today from the following SQLAlchemy query:

person = model.Person.query().filter_by(project_id=project_id, environment=environment, 
    person_id=person_id).one()

It's filtering on all of the columns in the unique key, so it should always return just one row, right? As it turns out, not necessarily:

mysql> select * from person where project_id=1 and person_id=1 and environment='production';
+--------+------------+-------------+-----------+------------+----------------------+
| id     | project_id | environment | person_id | username   | email                |
+--------+------------+-------------+-----------+------------+----------------------+
|      2 |          1 | production  | 1         | brianr     | [email protected]   |
| 542982 |          1 | production  | 1test     | brianrtest | [email protected] |
+--------+------------+-------------+-----------+------------+----------------------+

The bug here is that in my query I have the person_id as an int, but the column type is varchar, so mysql converts the varchar to an int before doing the comparison. And apparently the string '1test' converts to the integer 1.

The fix: cast to a string. Works as expected:

mysql> select * from person where project_id=1 and person_id='1' and environment='production';
+----+------------+-------------+-----------+----------+--------------------+
| id | project_id | environment | person_id | username | email              |
+----+------------+-------------+-----------+----------+--------------------+
|  2 |          1 | production  | 1         | brianr   | [email protected] |
+----+------------+-------------+-----------+----------+--------------------+
1 row in set (0.00 sec)

And in python:

person = model.Person.query().filter_by(project_id=project_id, environment=environment, 
    person_id=str(person_id)).one()

Thanks for writing this up and confirming that (today) I am not mad; fell over the same problem in a query which was entirely wrong, but still returned results. Phew...