MySQL Bugs: #118455: Cannot query JSON type data using BETWEEN '{"key1": 1}' AND '{"key1": 1}'

Bug #118455	Cannot query JSON type data using BETWEEN '{"key1": 1}' AND '{"key1": 1}'
Submitted:	16 Jun 9:52	Modified:	17 Jun 12:05
Reporter:	fent allen	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0.41	OS:	Linux
Assigned to:		CPU Architecture:	x86

Description:
Cannot query JSON type data using BETWEEN '{"key1": 1}'  AND '{"key1": 1}'

How to repeat:
drop table `json_table`;
CREATE TABLE `json_table` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `json_col` json NOT NULL
);
INSERT INTO `json_table`(json_col) VALUES ('{"key1": 1}'),('{"key1": 2}'),('{"key2": 1}'),('{"key2": 2}');
SELECT * FROM `json_table` WHERE  json_col BETWEEN '{"key1": 1}' AND '{"key1": 1}';

Hello Fent Allen,

Thank you for the bug report.
Imho this is not a bug,  quoting from the manual - 

JSON values can be compared using the =, <, <=, >, >=, <>, !=, and <=> operators.

The following comparison operators and functions are not yet supported with JSON values:

    BETWEEN

    IN()

    GREATEST()

    LEAST() 

For more info, please see https://dev.mysql.com/doc/refman/8.0/en/json.html

Regards,
Ashwini Patil