data with underscore in it is being selected rather than being excluded

Question

I hope someone could help me to clarify some doubts on this question.

I have got a table with this schema:

CREATE TABLE IF NOT EXISTS `entities` (
      `id` int(6) unsigned NOT NULL,
      `external_vendor_id` varchar(200) NOT NULL,
      PRIMARY KEY (`id`)
    ) DEFAULT CHARSET=utf8;

Then I insert a record in this table:

INSERT INTO `entities` 
    (`id`, `external_vendor_id`) 
    VALUES 
    ('1', '64835_122334');

When I select a record with the following statement, then this record is selected, even though the external_vendor_id in the WHERE clause does not match the external_vendor_id that was inserted via the INSERT statement.

select id, external_vendor_id 
from `entities` 
where `external_vendor_id` = 64835;

This is happening on MySQL 5.7.12.

Questions

• Does anybody know if this is an expected result? (if so why?)
• Or is this a bug in this MySQL version?

Any help will be much appreciated. And a modification of the query to avoid this result will be very helpful to understand what's going on.

Answer 1

There is some form of implicit conversion happening in the background which is converting your input value 64835 to a char value that results in the record being selected.

This could be a bug, but could also be a feature. People with more internal knowledge on the workings of MySQL will be able to provide a better explanation.

What you can do is add single quotes to your statement to ensure you are passing on a varchar and not an int that is getting converted:

-- quoting "characters"
select id, external_vendor_id 
  from `entities` 
  where `external_vendor_id` = '64835';

You could also cast the value to a char(5):

-- cast value to char(5) 
select id, external_vendor_id 
  from `entities` 
  where `external_vendor_id` = cast(64835 as char(5));

---

Full DB Fiddle w/ examples

Attached below the full db<>fiddle showing what happens in the various situations (incl. your initial findings).

CREATE TABLE IF NOT EXISTS `entities` (
  `id` int(6) unsigned NOT NULL,
  `external_vendor_id` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `entities` 
  (`id`, `external_vendor_id`) 
  VALUES 
  ('1', '64835_122334');

select id, external_vendor_id 
  from `entities` 
  where `external_vendor_id` = 64835;

id	external_vendor_id
1	64835_122334

-- cast value to char(5) 
select id, external_vendor_id 
  from `entities` 
  where `external_vendor_id` = cast(64835 as char(5));

id	external_vendor_id

-- quoting "text"
select id, external_vendor_id 
  from `entities` 
  where `external_vendor_id` = '64835';

id	external_vendor_id

fiddle

---

Bug Reporting

If you feel this could be a bug, then head over to MySQL | Report a Bug and enter the information you have already summarised in your question.

Answer 2

Short answer: You are mixing and matching Data Types here.
Data in a char field, being queried with a numeric [literal] value.

This forces MySQL to [wrongly] try and convert your char value into a number.
In doing so, it stops at the first, non-numeric character.

Always compare "like with like" Data Types:

select id, external_vendor_id 
from entities 
where external_vendor_id = '64835'

(This will return zero rows, of course).

Also, read up about Type Conversions in the Manual: