Compare integer with string in MySQL

Lets say we have a table with the following structure:

CREATE TABLE people 
(
    id INT NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age TINYINT,
    PRIMARY KEY (id)
)

Let us also insert some sample data to have something to play with:

INSERT INTO people (first_name, last_name, age) 
VALUES
('Giorgos', 'Betsos', 49),
('Tim', 'Smith', 35),
('Bob', 'No Age', 0);

Note that there is no need to specify id value in the INSERT as this is an AUTO INCREMENT key. Also, no age is available for 'Bob', so we choose to represent this fact with a 0 value.

If we run this query:

SELECT *
FROM people
WHERE age = 'old';

we get:

id	first_name	last_name	age
3	Bob	No age	0

Click here for a live demo.

So, one record is returned despite there is no record with age='old' in the table. How can this be possible?

We start to realize what is going on here if we execute simple query:

SELECT 'old' + 0

This seems not to make sense. However the query executes and returns a value of 0. This is because of implicit type conversion. String value 'old' is converted to 0 and then added to 0 and thus 0 is returned.

The same thing happens with our query. 'old' is converted to 0 when compared with a field of type int.