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?
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:
'6' or '6F' . . . don't. There's no reason for your application to search using the wrong data-type.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.)You are correct in your assumption: MySQL will treat a string which leads with an integer as though it is an integer and parse out any of the 'Char' values from your string. If your column is of type Integer then it will be trying to convert your string-literal.
To remedy this, you can use the CAST function to ensure that there is no ambiguity:
SELECT * FROM user WHERE CAST(user.id AS CHAR(9)) = '1F';
However strictly speaking you shouldn't really have your system ever be passing 1F into an ID field, since your ID field should be an integer (with the understanding that user-error or input should be handled before it gets to the query)
If you want to get all the ID's that start with 6, try doing:
WHERE Cast(P.id as varchar(20)) LIKE '6%'