Bug #86566 Comparing string column to 0 or FALSE, in the WHERE clause, condition is ignored
Submitted: 2 Jun 2017 17:06 Modified: 6 Jun 2017 12:45
Reporter: Alexander Mikhailov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S4 (Feature request)
Version:5.7.16 OS:Ubuntu
Assigned to: CPU Architecture:Any

[2 Jun 2017 17:06] Alexander Mikhailov
Description:
Trying to match the string column to 0 or FALSE, in the where clause,
results in the predicate being completely ignored (returns all the rows).

How to repeat:
CREATE TABLE Test (
     name VARCHAR(32) NOT NULL PRIMARY KEY,
     value INT(10) NOT NULL
) ENGINE InnoDB;

INSERT INTO Test(name,value) VALUES ('a',1),('b',2),('c',3);

SELECT * FROM Test WHERE name=0;

+------+-------+
| name | value |
+------+-------+
| a    |     1 |
| b    |     2 |
| c    |     3 |
+------+-------+
[3 Jun 2017 13:24] Tsubasa Tanaka
You faces a behavior of implicit type conversion.
Your query compares between "decimal value"(without floating-point) and "varchar", then MySQL compares them as "integer" vs "integer".

And 'a', 'b' and 'c' are cast to 0, so name=0 returns true( (CAST('a' AS INT) = 0) IS TRUE).

> If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.

https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html

Tsubasa (Not Oracle person)
[3 Jun 2017 19:21] MySQL Verification Team
Thank you for the explanation Mr. tsubasa tanaka.
[6 Jun 2017 12:45] MySQL Verification Team
I consider this a very useful feature request.

MySQL should have stronger data typing.

Verified as feature request.