MySQL – Select on varchar column with 0 (zero) as criteria returns all rows?

Sorry in advance – this seems like a really dumb question. But…

If I run a select query on a varchar() column, but use zero for the column value in the where clause – why does it return all rows?

For example:

CREATE TABLE test (
  id INT,
  person varchar(50)
);
INSERT INTO test (id, person) VALUES (1, "Alice");
INSERT INTO test (id, person) VALUES (2, "Bob");

SELECT * FROM test where person = 0;

I would expect this to either return an error, or an empty result set. Instead, it returns all rows?!?!

Why is that?

Answer

The expression:

person = 0

contains 2 operands of different data types.
In this case MySql does implicit conversion as it is explained in 12.2 Type Conversion in Expression Evaluation
So a value like 'Bob'is implicitly converted to a floating-point value and this value is 0.
There are cases where a string will be converted to a value different than 0, like when the string begins with a numeric part and then that numeric part is its converted value, so:

• '12abc6' will be converted to 12 and
• '5.6xyz0' will be converted to 5.6

An exception to this rule is a string like '2e3' which is interpreted as 2 * 10^3 (scientific notation) so it is converted to 2000.

In your code all the names are converted to 0, so the condition becomes:

0 = 0 

which is always true.