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

user_49383 0
Viewed 719

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?

where-clause select mysql sql
2 Answers

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.

user_104988280

What you are seeing is called implicit conversion. Since you are asking MySQL to compare a string with an integer, it convers the string to number, then compares the result.

And it turns out that, in MySQL, any string that does not start with digit is converted to 0. This matches on your where predicate, hence the results that you are seeing.

As a general rule of thumb: do not rely on implicit casting; when passing litteral values in your queries, do use the proper datatype (here, 0 should be '0'). When comparing table columns, do cast as needed.

Here is a small demo of int/string comparisons.

select 
    'foo' = '0',  -- false (as expected)
    'foo' = 0,    -- true!
    '0foo' = 0    -- true ("0foo" starts with "0")

'foo' = '0' | 'foo' = 0 | '0foo' = 0
----------: | --------: | ---------:
          0 |         1 |          1
user_106767160
Related