MySQL Bugs: #101883: Index is not using if we are using the wrong type in WHERE condition

Bug #101883	Index is not using if we are using the wrong type in WHERE condition
Submitted:	6 Dec 2020 19:14	Modified:	8 Dec 2020 13:06
Reporter:	Mikhail Balashov	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	8	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	INDEX

Description:
An index is not using if we have a unique index on the varchar column but searching as an int value. However, the row will be found

It is important because the user could try to search the value without quotes by mistake and if the table is too large this behavior potentially could slow down the database.

How to repeat:
DROP TABLE IF EXISTS `fakes`;
CREATE TABLE `fakes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `fakes` VALUES 
('71','1008662'),
('397','1028512'),
('795','1030447'),
('43','1037445'),
('260','1039262'),
('154','1039816'),
('366','1041783'),
('104','1095927'),
('59','1098953'),
('987','1104098'),
('372','1104948'),
('416','1126676');

-- index would not be used here
EXPLAIN SELECT * FROM fakes WHERE name = 1098953;

-- but here will
EXPLAIN SELECT * FROM fakes WHERE name = '1098953';

-- but the value would be found in both cases

Suggested fix:
CAST type in WHERE arguments for index or throw an exception if the type is not correct

Hi Mr. Balashov,

Thank you for your bug report.

However, this is not a bug.

An index can not be sorted, both as a string or as an integer. Simply, the sorting rules are totally, totally incompatible. For what you want to achieve you need two columns with separate indices.

Not a bug.