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: | |
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
[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.