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

[7 Mar 2022 12:06] mingsen xie
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
[7 Mar 2022 13:01] mingsen xie
CAST(prole.id AS CHAR) change to CONVERT(prole.id using utf8mb4) also not work
[8 Mar 2022 14:01] MySQL Verification Team
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.
[10 Mar 2022 13:52] MySQL Verification Team
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.