Why MySQL Returns Unexpected Rows When Comparing a VARCHAR Column to the Integer 0

Background

A colleague encountered a puzzling MySQL behavior: a a=0 condition on a VARCHAR column a that contains NULL and string values (but no literal "0") returned all rows with non‑NULL strings.

Problem Analysis

To illustrate the issue, the same table and data were created in MySQL, Oracle, and SQL Server.

Test Table Creation

SQL statements used to create the table and insert two rows (one with a string, one with NULL) are shown for each database.

create table test (id int, a varchar(3000), b varchar(2000));
insert into test values (1, '测试a', '测试b'), (2, NULL, '测试');

Query Results Comparison

Running select * from test where a = 0; yields different outcomes:

MySQL : Returns the row with the string value and issues a warning about truncating an incorrect DOUBLE value.

Oracle : Throws ORA-01722 "invalid number" because it attempts to convert the VARCHAR to a number.

SQL Server : Throws error 245 indicating the VARCHAR value cannot be converted to INT.

Root Cause in MySQL

MySQL’s type‑conversion rules automatically truncate a string when converting it to a numeric type. For example, "1测试a" becomes 1, and a completely non‑numeric string becomes 0 (empty string). Therefore, the expression a = 0 matches rows where the truncated numeric value of a is 0, which includes non‑empty strings after truncation, leading to the unexpected result set.

select 1 = "1测试a";  -- returns 1 with a warning
select 0 = "测试a";   -- returns 1 with a warning

The warning "Truncated incorrect DOUBLE value" confirms the implicit conversion.

Conclusion

MySQL’s permissive implicit conversion can produce wrong query results, whereas Oracle and SQL Server correctly reject the operation. To avoid such issues, ensure that both sides of a comparison have compatible data types, e.g., store numeric data in INT columns or compare VARCHAR to quoted string literals.

Proper database design and consistent type usage are essential to prevent hidden bugs caused by implicit casting.

References

[1] MySQL Type Conversion Documentation: https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html