MySQL Bugs: #106654: join on json key can not use index, but join on virtual column can use index

Bug #106654	join on json key can not use index, but join on virtual column can use index
Submitted:	7 Mar 2022 12:06	Modified:	10 Mar 2022 13:52
Reporter:	mingsen xie	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

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.