Does equal really means equal in MySQL?

Question

I am learning Node JS using MySQL as the backend DB in a Linux server and I have encountered something I do not understand and it is all related to MySQL itself.

I have the following table with one row of data. The id of this row is 6

CREATE TABLE `posts` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `authorID` int(11) DEFAULT NULL,
      `title` varchar(90) COLLATE utf8_bin DEFAULT NULL,
      `body` mediumtext COLLATE utf8_bin,
      `createdDate` datetime DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

So the following query works fine and returns the one row in the table.

 SELECT P.id,U.userName as `authorName`,U.userEmail as `authorEmail`,P.title,P.body,P.createdDate 
FROM posts P LEFt JOIN users U ON P.authorID = U.id WHERE P.id = '6'

This other query also works (returns no data):

    SELECT P.id,U.userName as `authorName`,U.userEmail as `authorEmail`,P.title,P.body,P.createdDate 
FROM posts P LEFt JOIN users U ON P.authorID = U.id WHERE P.id = '60'
 

But...! The following query is where I am lost (it return the row with ID 6!!!):

    SELECT P.id,U.userName as `authorName`,U.userEmail as `authorEmail`,P.title,P.body,P.createdDate 
FROM posts P LEFt JOIN users U ON P.authorID = U.id WHERE P.id = '6F'

Why? I am passing a literal 6F inside of single quotes (to make sure) and it should not return anything. There is no post with ID 6F. By the way, I can substitute 6F with 6whatever and the problem will persist. F6 however will not return data as expected.

Why is this? Is it because F is a character and MySQL is expecting an integer and therefore truncate the anything that is not part of the integer?

And more importantly, How can I eliminate this ambiguity?

Answer 1

As explained in the MySQL 8.0 Reference Manual, §12.2 "Type Conversion in Expression Evaluation":

When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly. […]

and:

The following rules describe how conversion occurs for comparison operations:

• […]
• In all other cases, the arguments are compared as floating-point (real) numbers. For example, a comparison of string and numeric operands takes place as a comparison of floating-point numbers.

So your integer 6 and string '6F' are both getting converted to the real number 6.0, and they compare equal.

To fix this, I can think of two options:

1. Instead of searching using strings such as '6' or '6F' . . . don't. There's no reason for your application to search using the wrong data-type.
2. If you really want to be able to search using strings, you can write CAST(P.id AS CHAR) = '6' [link]. But I think this may have significant performance impact, because I think this might force MySQL to scan all rows to find the ones where CAST(P.id AS CHAR) evaluates to the right result. (But, I'm really not sure. Maybe the optimizer is smart enough to figure out that CAST(P.id AS CHAR) = '6' is equivalent to P.id = 6 and that CAST(P.id AS CHAR) = '6F' is always false.)