MySQL Bugs: #86566: Comparing string column to 0 or FALSE, in the WHERE clause, condition is ignored

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

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 |
+------+-------+

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)

Thank you for the explanation Mr. tsubasa tanaka.

I consider this a very useful feature request.

MySQL should have stronger data typing.

Verified as feature request.