MySQL Bugs: #113868: Unreasonable warning "Cannot use range access on index"

Bug #113868	Unreasonable warning "Cannot use range access on index"
Submitted:	2 Feb 2024 12:03	Modified:	5 Feb 2024 12:03
Reporter:	Eugeny Nt	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	enum, INDEX

Description:
I am getting warnings when using integer indexes instead of strings in queries.

> Cannot use range access on index 'UK_type' due to type or collation conversion on field 'type'

It does not happen using strings.

How to repeat:
CREATE TABLE IF NOT EXISTS `enum_test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `type` ENUM ('aaa','bbb','ccc','ddd') NOT NULL DEFAULT 'aaa',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_type` (`name`, `type`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8MB4;

SELECT * FROM enum_test WHERE NAME = 'a' 
	AND `type` not IN (1, 3);

SHOW WARNINGS;

Hi Mr. Nt,

Thank you very much for your bug report.

However, we can not repat your test case with 8.0.36:

mysql> CREATE TABLE IF NOT EXISTS enum_test (   `id` int NOT NULL AUTO_INCREMENT,   `name` varchar(200) NOT NULL,   `type` ENUM ('aaa','bbb','ccc','ddd') NOT NULL DEFAULT 'aaa',   PRIMARY KEY (`id`),   UNIQUE KEY `UK_type` (`name`, `type`) ) ENGINE=InnoDB DEFAULT CHARSET=UTF8MB4;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> SELECT * FROM enum_test  WHERE NAME = 'a'      AND `type` not IN (1, 3);
Empty set (0.03 sec)

mysql>
mysql> SHOW WARNINGS;
Empty set (0.00 sec)

Not a bug.

Got it. In any case, thanks for the quick response.

You are truly welcome .......

Hi Mr. Nt,

Thank you for your bug report.

We managed to repeat it with latest 8.0 release.

Since this is  a warning on the ENUM data type, this is a feature request. 

This is now a verified report.