Bug #117931 virtual column index record wrong data

• Submitted: 10 Apr 9:57
• Modified: 11 Apr 10:43
• Reporter: zongyi chen
• Status: Analyzing
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 8.0.22
• OS: Any
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[10 Apr 9:57] zongyi chen

Description:
table like:
CREATE TABLE `freeflow_flowPlanConfig_old` (
  `$json` json NOT NULL,
  `active` bit(1) GENERATED ALWAYS AS ((json_extract(`$json`,_utf8mb4'$."active"') = true)) VIRTUAL NOT NULL,
  `name` varchar(80) CHARACTER SET gb18030 COLLATE gb18030_chinese_ci GENERATED ALWAYS AS (json_unquote(json_extract(`$json`,_utf8mb4'$."name"'))) VIRTUAL NOT NULL,
  `corporationId` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin GENERATED ALWAYS AS (json_unquote(json_extract(`$json`,_utf8mb4'$."corporationId"'))) VIRTUAL NOT NULL,
  `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `$createTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `$updateTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `by_corporationId_active_name` (`corporationId`,`active`,`name`),
  KEY `by_active` (`active`),
  KEY `idx_corporationId` (`corporationId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='?????' 

table has index in virtual column, index name is by_corporationId_active_name and idx_corporationId.

sql use 2 index

-- 6
select count(1) from freeflow_flowPlanConfig_old force index(by_corporationId_active_name) where corporationId = "ID01ygzRCQ24wv" 

-- 15
select count(1) from freeflow_flowPlanConfig_old force index(idx_corporationId) where corporationId = "ID01ygzRCQ24wv"

The data retrieved using the idx_corporationId index is accurate, while the data retrieved using the by_corporationId_active_name index is missing some parts. After dumping the indexes for comparison, it was found that there are indeed errors in the data in the by_corporationId_active_name index.

such as:
idx_corporationId 	by_corporationId_active_name(corporationId column)
ID01ygzRCQ24wv		ID01ygzRCQ24wv
ID01ygzRCQ24wv		ID01ygzRCQ24wv
ID01ygzRCQ24wv		ID01ygzRCQ24wv
ID01ygzRCQ24wv		ID01ygzRCQ24wv
ID01ygzRCQ24wv		ID01ygzRCQ24wv
ID01ygzRCQ24wv		ID01ygzRCQ24wv
ID01ygzRCQ24wv		??????????-20240527
ID01ygzRCQ24wv		ID01ygzRCQ24wv
ID01ygzRCQ24wv		ID01ygzRCQ24wv
ID01ygzRCQ24wv		ID01ygzRCQ24wv
ID01ygzRCQ24wv		ID01ygzRCQ24wv
ID01ygzRCQ24wv		ID01ygzRCQ24wv
ID01ygzRCQ24wv		ID01ygzRCQ24wv
ID01ygzRCQ24wv		ID01ygzRCQ24wv
ID01ygzRCQ24wv		ID01ygzRCQ24wv

How to repeat:
can not repeat