Why is MySQL select returning different result for the same value depending if it is quoted

Question

While querying my table of products like this:

SELECT product_id FROM products WHERE product_id = 1701114;

I get two rows as a result: 1701114 and 1701114B.

But after adding single quotation marks like this:

SELECT product_id FROM products WHERE product_id = '1701114';

I get expected single row result.

product_id column is a primary key of VARCHAR type and unquoted value is a digital which may be the cause, but it's quite surprising that equality sign suddenly changes to something like 'LIKE'

Answer 1

Your column product_id is a varchar column. It is a very bad idea to compare it to an integer (1701114) rather than to a string ('1701114').

What happens is that MySQL silently converts the product_id values to numbers in order to compare them with 1701114. '1701114B' is no number and should not be convertable to number, but raise an error, in my opinion. However, the MySQL developers have decided to convert it nonetheless starting from left to right and going as far as possible. So they convert '1701114B' to 1701114, hence the unexpected match.