text field convert to json result data query inconsistency

[yongman]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE event_log1 (
  event_timestamp bigint NOT NULL,
  approximate_arrival_timestamp datetime NOT NULL,
  action_params text DEFAULT NULL -- text type
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T! SHARD_ROW_ID_BITS=4 PRE_SPLIT_REGIONS=2 */
PARTITION BY RANGE COLUMNS(approximate_arrival_timestamp)
(PARTITION p202603 VALUES LESS THAN ('2026-04-01 00:00:00'),
 PARTITION p202604 VALUES LESS THAN ('2026-05-01 00:00:00'),
 PARTITION p202605 VALUES LESS THAN ('2026-06-01 00:00:00'),
 PARTITION p202606 VALUES LESS THAN ('2026-07-01 00:00:00'));
INSERT INTO event_log1
  (event_timestamp,approximate_arrival_timestamp,action_params) VALUES
  (1747312496000,'2026-05-15 12:34:56','{"popup_id":"123"}'),
  (1747312556000,'2026-05-15 12:35:56','{"popup_id":"123"}'),
  (1747312616000,'2026-05-15 12:36:56','{"popup_id":"0"}'),
  (1747312676000,'2026-05-15 12:37:56','{"popup_id":"456"}'),
  (1747312736000,'2026-05-15 12:38:56','{"popup_id":"789"}');
ALTER TABLE event_log1 SET TIFLASH REPLICA 1;

CREATE TABLE event_log2 (
  event_timestamp bigint NOT NULL,
  approximate_arrival_timestamp datetime NOT NULL,
  action_params JSON DEFAULT NULL -- JSON type
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T! SHARD_ROW_ID_BITS=4 PRE_SPLIT_REGIONS=2 */
PARTITION BY RANGE COLUMNS(approximate_arrival_timestamp)
(PARTITION p202603 VALUES LESS THAN ('2026-04-01 00:00:00'),
 PARTITION p202604 VALUES LESS THAN ('2026-05-01 00:00:00'),
 PARTITION p202605 VALUES LESS THAN ('2026-06-01 00:00:00'),
 PARTITION p202606 VALUES LESS THAN ('2026-07-01 00:00:00'));
INSERT INTO event_log2
  (event_timestamp,approximate_arrival_timestamp,action_params) VALUES
  (1747312496000,'2026-05-15 12:34:56','{"popup_id":"123"}'),
  (1747312556000,'2026-05-15 12:35:56','{"popup_id":"123"}'),
  (1747312616000,'2026-05-15 12:36:56','{"popup_id":"0"}'),
  (1747312676000,'2026-05-15 12:37:56','{"popup_id":"456"}'),
  (1747312736000,'2026-05-15 12:38:56','{"popup_id":"789"}');
ALTER TABLE event_log2 SET TIFLASH REPLICA 1;

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

Both query on event_log1 (with action_params is TEXT) and event_log2 (with action_params is JSON), the SQL below should be the same.

SET SESSION tidb_isolation_read_engines='tiflash';
SELECT 'tiflash',action_params,JSON_EXTRACT(action_params, '$.popup_id')
FROM test.event_log1
WHERE JSON_EXTRACT(action_params, '$.popup_id') is null;
+---------+---------------+-------------------------------------------+
| tiflash | action_params | JSON_EXTRACT(action_params, '$.popup_id') |
+---------+---------------+-------------------------------------------+
+---------+---------------+-------------------------------------------+

SET SESSION tidb_isolation_read_engines='tiflash';
SELECT 'tiflash',action_params,JSON_EXTRACT(action_params, '$.popup_id')
FROM test.event_log1
WHERE JSON_EXTRACT(action_params, '$.popup_id') is not null;
+---------+---------------------+-------------------------------------------+
| tiflash | action_params       | JSON_EXTRACT(action_params, '$.popup_id') |
+---------+---------------------+-------------------------------------------+
| tiflash | {"popup_id": "123"} | "123"                                     |
| tiflash | {"popup_id": "123"} | "123"                                     |
| tiflash | {"popup_id": "0"}   | "0"                                       |
| tiflash | {"popup_id": "456"} | "456"                                     |
| tiflash | {"popup_id": "789"} | "789"                                     |
+---------+---------------------+-------------------------------------------+

SET SESSION tidb_isolation_read_engines='tiflash';
SELECT 'tiflash',action_params,JSON_EXTRACT(action_params, '$.popup_id')
FROM test.event_log2
WHERE JSON_EXTRACT(action_params, '$.popup_id') is null;
+---------+---------------+-------------------------------------------+
| tiflash | action_params | JSON_EXTRACT(action_params, '$.popup_id') |
+---------+---------------+-------------------------------------------+
+---------+---------------+-------------------------------------------+

SET SESSION tidb_isolation_read_engines='tiflash';
SELECT 'tiflash',action_params,JSON_EXTRACT(action_params, '$.popup_id')
FROM test.event_log2
WHERE JSON_EXTRACT(action_params, '$.popup_id') is not null;
+---------+---------------------+-------------------------------------------+
| tiflash | action_params       | JSON_EXTRACT(action_params, '$.popup_id') |
+---------+---------------------+-------------------------------------------+
| tiflash | {"popup_id": "123"} | "123"                                     |
| tiflash | {"popup_id": "123"} | "123"                                     |
| tiflash | {"popup_id": "0"}   | "0"                                       |
| tiflash | {"popup_id": "456"} | "456"                                     |
| tiflash | {"popup_id": "789"} | "789"                                     |
+---------+---------------------+-------------------------------------------+

3. What did you see instead (Required)

when action_params is type 'TEXT', the query result is not expected

SET SESSION tidb_isolation_read_engines='tiflash';
SELECT 'tiflash',action_params,JSON_EXTRACT(action_params, '$.popup_id')
FROM test.event_log1
WHERE JSON_EXTRACT(action_params, '$.popup_id') is null;
+---------+--------------------+-------------------------------------------+
| tiflash | action_params      | JSON_EXTRACT(action_params, '$.popup_id') |
+---------+--------------------+-------------------------------------------+
| tiflash | {"popup_id":"123"} | "123"                                     |
| tiflash | {"popup_id":"123"} | "123"                                     |
| tiflash | {"popup_id":"0"}   | "0"                                       |
| tiflash | {"popup_id":"456"} | "456"                                     |
| tiflash | {"popup_id":"789"} | "789"                                     |
+---------+--------------------+-------------------------------------------+

SET SESSION tidb_isolation_read_engines='tiflash';
SELECT 'tiflash',action_params,JSON_EXTRACT(action_params, '$.popup_id')
FROM test.event_log1
WHERE JSON_EXTRACT(action_params, '$.popup_id') is not null;
+---------+---------------+-------------------------------------------+
| tiflash | action_params | JSON_EXTRACT(action_params, '$.popup_id') |
+---------+---------------+-------------------------------------------+
+---------+---------------+-------------------------------------------+

when action_params is type 'JSON', the query result is as expected

SET SESSION tidb_isolation_read_engines='tiflash';
SELECT 'tiflash',action_params,JSON_EXTRACT(action_params, '$.popup_id')
FROM test.event_log2
WHERE JSON_EXTRACT(action_params, '$.popup_id') is null;
+---------+---------------+-------------------------------------------+
| tiflash | action_params | JSON_EXTRACT(action_params, '$.popup_id') |
+---------+---------------+-------------------------------------------+
+---------+---------------+-------------------------------------------+

SET SESSION tidb_isolation_read_engines='tiflash';
SELECT 'tiflash',action_params,JSON_EXTRACT(action_params, '$.popup_id')
FROM test.event_log2
WHERE JSON_EXTRACT(action_params, '$.popup_id') is not null;
+---------+---------------------+-------------------------------------------+
| tiflash | action_params       | JSON_EXTRACT(action_params, '$.popup_id') |
+---------+---------------------+-------------------------------------------+
| tiflash | {"popup_id": "123"} | "123"                                     |
| tiflash | {"popup_id": "123"} | "123"                                     |
| tiflash | {"popup_id": "0"}   | "0"                                       |
| tiflash | {"popup_id": "456"} | "456"                                     |
| tiflash | {"popup_id": "789"} | "789"                                     |
+---------+---------------------+-------------------------------------------+

4. What is your TiFlash version? (Required)

[JaySon-Huang]

Reproduce execution plan compare:

-- text type
-- cast text as json, json_extract, cast as var_string, then run isnull
SET SESSION tidb_isolation_read_engines='tiflash';
explain analyze SELECT 'tiflash',action_params,JSON_EXTRACT(action_params, '$.popup_id')
FROM test.event_log1
WHERE JSON_EXTRACT(action_params, '$.popup_id') is null;
+-------------------------+---------+---------+--------------+------------------+----------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------+-----------+------+
| id                      | estRows | actRows | task         | access object    | execution info                                                                                           | operator info                                                                                                           | memory    | disk |
+-------------------------+---------+---------+--------------+------------------+----------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------+-----------+------+
| Projection_4            | 4.00    | 5       | root         |                  | time:38.8ms, loops:2, RU:0.34, Concurrency:OFF                                                           | ?->Column#5, test.event_log1.action_params, json_extract(cast(test.event_log1.action_params, json BINARY), ?)->Column#6 | 636 Bytes | N/A  |
| └─TableReader_9         | 4.00    | 5       | root         | partition:all    | time:38.7ms, loops:2, cop_task: {num: 2, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | MppVersion: 2, data:ExchangeSender_8                                                                                    | 821 Bytes | N/A  |
|   └─ExchangeSender_8    | 4.00    | 5       | mpp[tiflash] |                  | tiflash_task:{time:29.8ms, loops:1, threads:16}                                                          | ExchangeType: PassThrough                                                                                               | N/A       | N/A  |
|     └─Selection_7       | 4.00    | 5       | mpp[tiflash] |                  | tiflash_task:{time:29.8ms, loops:1, threads:16}                                                          | isnull(cast(json_extract(cast(test.event_log1.action_params, json BINARY), ?), var_string(16777216)))                   | N/A       | N/A  |
|       └─TableFullScan_6 | 5.00    | 5       | mpp[tiflash] | table:event_log1 | tiflash_task:{time:29.8ms, loops:1, threads:16}                                                          | pushed down filter:empty, keep order:false, stats:pseudo, PartitionTableScan:true                                       | N/A       | N/A  |
+-------------------------+---------+---------+--------------+------------------+----------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------+-----------+------+

-- json type
-- json_extract, cast as var_string, then run isnull
SET SESSION tidb_isolation_read_engines='tiflash';
explain analyze SELECT 'tiflash',action_params,JSON_EXTRACT(action_params, '$.popup_id')
FROM test.event_log2
WHERE JSON_EXTRACT(action_params, '$.popup_id') is null;
+-------------------------+---------+---------+--------------+------------------+---------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+-----------+------+
| id                      | estRows | actRows | task         | access object    | execution info                                                                              | operator info                                                                                        | memory    | disk |
+-------------------------+---------+---------+--------------+------------------+---------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+-----------+------+
| Projection_4            | 4.00    | 0       | root         |                  | time:32.1ms, loops:1, RU:0.00, Concurrency:OFF                                              | ?->Column#5, test.event_log2.action_params, json_extract(test.event_log2.action_params, ?)->Column#6 | 636 Bytes | N/A  |
| └─TableReader_9         | 4.00    | 0       | root         | partition:all    | time:32.1ms, loops:1, cop_task: {num: 1, max: 0s, proc_keys: 0, copr_cache_hit_ratio: 0.00} | MppVersion: 2, data:ExchangeSender_8                                                                 | 812 Bytes | N/A  |
|   └─ExchangeSender_8    | 4.00    | 0       | mpp[tiflash] |                  | tiflash_task:{time:22.9ms, loops:0, threads:16}                                             | ExchangeType: PassThrough                                                                            | N/A       | N/A  |
|     └─Selection_7       | 4.00    | 0       | mpp[tiflash] |                  | tiflash_task:{time:22.9ms, loops:0, threads:16}                                             | isnull(cast(json_extract(test.event_log2.action_params, ?), var_string(16777216)))                   | N/A       | N/A  |
|       └─TableFullScan_6 | 5.00    | 0       | mpp[tiflash] | table:event_log2 | tiflash_task:{time:22.9ms, loops:0, threads:16}                                             | pushed down filter:empty, keep order:false, stats:pseudo, PartitionTableScan:true                    | N/A       | N/A  |
+-------------------------+---------+---------+--------------+------------------+---------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+-----------+------+

[JaySon-Huang]

The root cause is the StorageDisaggregatedColumnar (added in #10842) will create a temporary FilterConditions and build the FunctionCastStringAsJson. FunctionCastStringAsJson only keep a ptr to the tipb::FieldType which will be invalid after the temporary instance are out of scope.

https://github.com/yongman/tiflash/blob/013f968ce2f2ac27c91cd72b8c4c89a3da3ba6f5/dbms/src/Storages/StorageDisaggregatedColumnar.cpp#L326-L352