Optimizer cannot correctly display the statistics info for "stored generated indexes" in the operator info

[0xPoe]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

1. Create a database and a table:

set @@global.tidb_enable_auto_analyze = 'OFF';

create database business_db;
USE business_db;
CREATE TABLE `event_log` (
    `id` varchar(255) NOT NULL,
    `created_time` datetime(3) DEFAULT CURRENT_TIMESTAMP(3),
    `modified_time` datetime(3) DEFAULT CURRENT_TIMESTAMP(3),
    `money_info` json DEFAULT NULL,
    `actors` json DEFAULT NULL,
    `recipient` varchar(255) GENERATED ALWAYS AS (json_extract(`actors`, _utf8mb4'$[*].recipient')) STORED,
    `operator_id` varchar(255) GENERATED ALWAYS AS (json_extract(`actors`, _utf8mb4'$[*].operator_id')) STORED,
    `category` varchar(255) DEFAULT NULL,
    `action` varchar(255) DEFAULT NULL,
    `cancelled_by_id` varchar(255) DEFAULT NULL,
    `cancellation_of_id` varchar(255) DEFAULT NULL,
    `metadata` json DEFAULT NULL,
    `money_details` json DEFAULT NULL,
    PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
    KEY `idx_operator_id` (`operator_id`),
    KEY `idx_created_time` (`created_time`),
    KEY `idx_category` (`category`),
    KEY `idx_action` (`action`),
    KEY `idx_recipient` (`recipient`),
    KEY `idx_cancelled_by_id` (`cancelled_by_id`),
    KEY `idx_cancellation_of_id` (`cancellation_of_id`),
    KEY `idx_metadata_location_id` ((
        cast(json_unquote(json_extract(`metadata`, _utf8mb4'$.location_id')) as char(255)) collate utf8mb4_bin))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

2. Load the stats json for the table:

business_db.event_log.json

LOAD STATS './TCOC-2724/business_db.event_log.json';

3. explain analyze the query:

explain analyze
SELECT SQL_NO_CACHE e.id, e.category, e.created_time, e.money_info
FROM `event_log` e
WHERE e.`category` = "payment"
AND e.`created_time` BETWEEN "2024-09-30 15:00:00.000" AND "2099-01-01 00:00:01.000"
AND (cast(json_unquote(json_extract(e.`metadata`, _utf8mb4'$.location_id')) as char(255)) collate utf8mb4_bin) = "498"
ORDER BY e.`created_time` DESC
LIMIT 70000;

2. What did you expect to see? (Required)

+--------------------------------+-------+-------+---------+-----------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+-------+
|id                              |estRows|actRows|task     |access object                                                                                                                                  |execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |operator info                                                                                                                                                                                                                                               |memory   |disk   |
+--------------------------------+-------+-------+---------+-----------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+-------+
|Projection_8                    |14.84  |0      |root     |                                                                                                                                               |time:3.13ms, loops:1, RU:0.964042, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |business_db.event_log.id, business_db.event_log.category, business_db.event_log.created_time, business_db.event_log.money_info                                                                                                                              |2.86 KB  |N/A    |
|└─TopN_11                       |14.84  |0      |root     |                                                                                                                                               |time:3.13ms, loops:1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |business_db.event_log.created_time:desc, offset:0, count:70000                                                                                                                                                                                              |0 Bytes  |0 Bytes|
|  └─Projection_26               |14.84  |0      |root     |                                                                                                                                               |time:3.1ms, loops:2, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |business_db.event_log.id, business_db.event_log.created_time, business_db.event_log.money_info, business_db.event_log.category, cast(json_unquote(cast(json_extract(business_db.event_log.metadata, $.location_id), var_string(16777216))), var_string(255))|3.48 KB  |N/A    |
|    └─IndexLookUp_25            |14.84  |0      |root     |                                                                                                                                               |time:3.1ms, loops:2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |                                                                                                                                                                                                                                                            |245 Bytes|N/A    |
|      ├─IndexRangeScan_22(Build)|827.01 |0      |cop[tikv]|table:e, index:idx_metadata_location_id(cast(json_unquote(json_extract(`metadata`, _utf8mb4'$.location_id')) as char(255)) collate utf8mb4_bin)|time:3.05ms, loops:1, cop_task: {num: 1, max: 509.4µs, proc_keys: 0, tot_proc: 42.1µs, tot_wait: 63.8µs, copr_cache_hit_ratio: 0.00, build_task_duration: 6.46µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:2, total_time:717.3µs}, rpc_errors:{epoch_not_match:1}}, backoff{regionMiss: 2ms}, tikv_task:{time:1ms, loops:1}, scan_detail: {total_keys: 1, get_snapshot_time: 30.8µs, rocksdb: {block: {}}}, time_detail: {total_process_time: 42.1µs, total_wait_time: 63.8µs, total_kv_read_wall_time: 1ms, tikv_wall_time: 208.1µs}|range:["498","498"], keep order:false, stats:partial[idx_metadata_location_id_0:AllLoaded]                                                                                                                                                          |N/A      |N/A    |
|      └─Selection_24(Probe)     |14.84  |0      |cop[tikv]|                                                                                                                                               |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |eq(business_db.event_log.category, "payment"), ge(business_db.event_log.created_time, 2024-09-30 15:00:00.000000), le(business_db.event_log.created_time, 2099-01-01 00:00:01.000000)                                                                       |N/A      |N/A    |
|        └─TableRowIDScan_23     |827.01 |0      |cop[tikv]|table:e                                                                                                                                        |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |keep order:false, stats:partial[_v$_idx_metadata_location_id_0:unInitialized]                                                                                                                                                                               |N/A      |N/A    |
+--------------------------------+-------+-------+---------+-----------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+-------+

-range:["498","498"], keep order:false, stats:partial[_v$_idx_metadata_location_id_0:unInitialized]
+range:["498","498"], keep order:false, stats:partial[idx_metadata_location_id_0:AllLoaded]

3. What did you see instead (Required)

+--------------------------------+-------+-------+---------+-----------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+-------+
|id                              |estRows|actRows|task     |access object                                                                                                                                  |execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |operator info                                                                                                                                                                                                                                               |memory   |disk   |
+--------------------------------+-------+-------+---------+-----------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+-------+
|Projection_8                    |14.84  |0      |root     |                                                                                                                                               |time:3.13ms, loops:1, RU:0.964042, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |business_db.event_log.id, business_db.event_log.category, business_db.event_log.created_time, business_db.event_log.money_info                                                                                                                              |2.86 KB  |N/A    |
|└─TopN_11                       |14.84  |0      |root     |                                                                                                                                               |time:3.13ms, loops:1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |business_db.event_log.created_time:desc, offset:0, count:70000                                                                                                                                                                                              |0 Bytes  |0 Bytes|
|  └─Projection_26               |14.84  |0      |root     |                                                                                                                                               |time:3.1ms, loops:2, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |business_db.event_log.id, business_db.event_log.created_time, business_db.event_log.money_info, business_db.event_log.category, cast(json_unquote(cast(json_extract(business_db.event_log.metadata, $.location_id), var_string(16777216))), var_string(255))|3.48 KB  |N/A    |
|    └─IndexLookUp_25            |14.84  |0      |root     |                                                                                                                                               |time:3.1ms, loops:2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |                                                                                                                                                                                                                                                            |245 Bytes|N/A    |
|      ├─IndexRangeScan_22(Build)|827.01 |0      |cop[tikv]|table:e, index:idx_metadata_location_id(cast(json_unquote(json_extract(`metadata`, _utf8mb4'$.location_id')) as char(255)) collate utf8mb4_bin)|time:3.05ms, loops:1, cop_task: {num: 1, max: 509.4µs, proc_keys: 0, tot_proc: 42.1µs, tot_wait: 63.8µs, copr_cache_hit_ratio: 0.00, build_task_duration: 6.46µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:2, total_time:717.3µs}, rpc_errors:{epoch_not_match:1}}, backoff{regionMiss: 2ms}, tikv_task:{time:1ms, loops:1}, scan_detail: {total_keys: 1, get_snapshot_time: 30.8µs, rocksdb: {block: {}}}, time_detail: {total_process_time: 42.1µs, total_wait_time: 63.8µs, total_kv_read_wall_time: 1ms, tikv_wall_time: 208.1µs}|range:["498","498"], keep order:false, stats:partial[_v$_idx_metadata_location_id_0:unInitialized]                                                                                                                                                          |N/A      |N/A    |
|      └─Selection_24(Probe)     |14.84  |0      |cop[tikv]|                                                                                                                                               |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |eq(business_db.event_log.category, "payment"), ge(business_db.event_log.created_time, 2024-09-30 15:00:00.000000), le(business_db.event_log.created_time, 2099-01-01 00:00:01.000000)                                                                       |N/A      |N/A    |
|        └─TableRowIDScan_23     |827.01 |0      |cop[tikv]|table:e                                                                                                                                        |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |keep order:false, stats:partial[_v$_idx_metadata_location_id_0:unInitialized]                                                                                                                                                                               |N/A      |N/A    |
+--------------------------------+-------+-------+---------+-----------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+-------+

4. What is your TiDB version? (Required)

v8.3.0

[0xPoe]

After some investigation:
We generate the info here:

func (p *PhysicalIndexScan) ExplainInfo() string {
	return p.AccessObject().String() + ", " + p.OperatorInfo(false)
}

For AccessObject, we handle the hidden column correctly:

But in OperatorInfo, we displayed it directly, see more at:

// recordUsedItemStatsStatus only records un-FullLoad item load status during user query
func recordUsedItemStatsStatus(sctx planctx.PlanContext, stats any, tableID, id int64) {