Bug #106654 join on json key can not use index, but join on virtual column can use index

Description:
join on json key can not use index, but join on virtual column can use index.

mysql index is `idx_onePosition`(`virtual_onePosition`), virtual_onePosition is a virtual column, which is equal to json_unquote(json_extract(p.extend_field_varchar, '$.k0')).

join on json key can not use index, sql is：

SELECT * FROM `t1` AS prole LEFT JOIN `t2` AS p ON CAST(prole.id AS CHAR) = json_unquote(json_extract(p.extend_field_varchar, '$.k0'));

join on virtual column can use index, sql is:
SELECT * FROM `t1` AS prole LEFT JOIN `t2` AS p ON CAST(prole.id AS CHAR) = p.virtual_onePosition;

How to repeat:
CREATE TABLE `t1` (
  `id` bigint NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `t2` (
  `id` bigint NOT NULL,
  `extend_field_varchar` json DEFAULT NULL,
  `virtual_onePosition` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci GENERATED ALWAYS AS (json_unquote(json_extract(`extend_field_varchar`,_utf8mb3'$.k0'))) VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `idx_onePosition` (`virtual_onePosition`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Suggested fix:
join on json key can use index

CAST(prole.id AS CHAR) change to CONVERT(prole.id using utf8mb4) also not work

Hi Mr. xie,

Thank you for your bug report.

However, this is not a bug.

You are using functions in your join query which, regardless of the data type,  prevents totally usage of indices . However, virtual column that uses that function can be used, since its index is built with that function included.

This is all described in our Reference Manual.

Not a bug.

HI Mr. xie,

We have analysed further your report and it turns out that there is a deviation in the LEFT JOIN, which does not exist with an inner join.

For that reason, this report is now a verified bug.