JSON查询异常，部分数据查询不到

[qihjun]

@FutureDB

set @@session.tidb_isolation_read_engines='tikv';

只走tikv是一样的，结果都是全的

[qihjun]

上面有我最原始的建表语句，你建表导进去数据之后开启一下TiFlash副本就可以复现。

[TiDBer_xTvoCh2f]

复现不了，所以建议你把有问题的行导出来

CREATE TABLE test (
id int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘ID’,
trackid json NOT NULL COMMENT ‘赛道ID’,
name varchar(45) NOT NULL COMMENT ‘名称’,
createtime datetime NOT NULL COMMENT ‘创建时间’,
updatetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
PRIMARY KEY (id) /*T![clustered_index] CLUSTERED */,
UNIQUE KEY uniq_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T![auto_id_cache] AUTO_ID_CACHE=1 */ COMMENT=‘信息表’ /*T![placement] PLACEMENT POLICY=storageonnvme */

insert into test(id,trackid,name,create_time) select 1,‘[9,1,2]’,‘a’,now();
insert into test(id,trackid,name,createtime) select 2,‘[8,1,2]’,‘b’,now();
insert into test(id,trackid,name,createtime) select 3,‘[7,1,2]’,‘c’,now();
insert into test(id,trackid,name,createtime) select 4,‘[9,2,3]’,‘d’,now();

[127.0.0.1] [test] 12:16:56 >set tidb_isolation_read_engines =“tikv”;
Query OK, 0 rows affected (0.00 sec)

[127.0.0.1] [test] 12:24:54 >select *
→ from test
→ where json_extract(trackid, ‘$[0]’) = 9
→ order by id;
±—±----------±-----±--------------------±--------------------+
| id | trackid | name | createtime | updatetime |
±—±----------±-----±--------------------±--------------------+
| 1 | [9, 1, 2] | a | 2024-09-09 12:12:54 | 2024-09-09 12:12:54 |
| 4 | [9, 2, 3] | d | 2024-09-09 12:13:39 | 2024-09-09 12:13:39 |
±—±----------±-----±--------------------±--------------------+
2 rows in set (0.01 sec)

[127.0.0.1] [test] 12:25:02 >select *
→ from test
→ where json_extract(trackid, ‘$[0]’) = 9;
±—±----------±-----±--------------------±--------------------+
| id | trackid | name | createtime | updatetime |
±—±----------±-----±--------------------±--------------------+
| 1 | [9, 1, 2] | a | 2024-09-09 12:12:54 | 2024-09-09 12:12:54 |
| 4 | [9, 2, 3] | d | 2024-09-09 12:13:39 | 2024-09-09 12:13:39 |
±—±----------±-----±--------------------±--------------------+
2 rows in set (0.00 sec)

[127.0.0.1] [test] 12:25:11 >explain select *
→ from test
→ where json_extract(trackid, ‘$[0]’) = 9;
±------------------------±--------±----------±--------------±-------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
±------------------------±--------±----------±--------------±-------------------------------------------------------------------------+
| TableReader_7 | 3.20 | root | | data:Selection_6 |
| └─Selection_6 | 3.20 | cop[tikv] | | eq(json_extract(test.test.trackid, “$[0]”), cast(9, json BINARY)) |
| └─TableFullScan_5 | 4.00 | cop[tikv] | table:test | keep order:false, stats:partial[id:unInitialized, trackid:unInitialized] |
±------------------------±--------±----------±--------------±-------------------------------------------------------------------------+
3 rows in set (0.00 sec)

[127.0.0.1] [test] 12:25:30 >explain select *
→ from test
→ where json_extract(trackid, ‘$[0]’) = 9
→ order by id;
±-------------------------±--------±----------±--------------±------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
±-------------------------±--------±----------±--------------±------------------------------------------------------------------------+
| TableReader_14 | 3.20 | root | | data:Selection_13 |
| └─Selection_13 | 3.20 | cop[tikv] | | eq(json_extract(test.test.trackid, “$[0]”), cast(9, json BINARY)) |
| └─TableFullScan_12 | 4.00 | cop[tikv] | table:test | keep order:true, stats:partial[id:unInitialized, trackid:unInitialized] |
±-------------------------±--------±----------±--------------±------------------------------------------------------------------------+
3 rows in set (0.00 sec)

[127.0.0.1] [test] 12:25:35 >select *
→ from test
→ where json_extract(trackid, ‘$[0]’) = 9;
±—±----------±-----±--------------------±--------------------+
| id | trackid | name | createtime | updatetime |
±—±----------±-----±--------------------±--------------------+
| 1 | [9, 1, 2] | a | 2024-09-09 12:12:54 | 2024-09-09 12:12:54 |
| 4 | [9, 2, 3] | d | 2024-09-09 12:13:39 | 2024-09-09 12:13:39 |
±—±----------±-----±--------------------±--------------------+
2 rows in set (0.01 sec)

[127.0.0.1] [test] 12:25:56 >select *
→ from test
→ where json_extract(trackid, ‘$[0]’) = 9
→ order by id;
±—±----------±-----±--------------------±--------------------+
| id | trackid | name | createtime | updatetime |
±—±----------±-----±--------------------±--------------------+
| 1 | [9, 1, 2] | a | 2024-09-09 12:12:54 | 2024-09-09 12:12:54 |
| 4 | [9, 2, 3] | d | 2024-09-09 12:13:39 | 2024-09-09 12:13:39 |
±—±----------±-----±--------------------±--------------------+
2 rows in set (0.00 sec)

[127.0.0.1] [test] 12:26:01 >set tidb_isolation_read_engines =“tiflash”;
Query OK, 0 rows affected (0.00 sec)

[127.0.0.1] [test] 12:26:14 >select *
→ from test
→ where json_extract(trackid, ‘$[0]’) = 9;
±—±----------±-----±--------------------±--------------------+
| id | trackid | name | createtime | updatetime |
±—±----------±-----±--------------------±--------------------+
| 1 | [9, 1, 2] | a | 2024-09-09 12:12:54 | 2024-09-09 12:12:54 |
| 4 | [9, 2, 3] | d | 2024-09-09 12:13:39 | 2024-09-09 12:13:39 |
±—±----------±-----±--------------------±--------------------+
2 rows in set (0.22 sec)

[127.0.0.1] [test] 12:26:22 >select *
→ from test
→ where json_extract(trackid, ‘$[0]’) = 9
→ order by id;
±—±----------±-----±--------------------±--------------------+
| id | trackid | name | createtime | updatetime |
±—±----------±-----±--------------------±--------------------+
| 1 | [9, 1, 2] | a | 2024-09-09 12:12:54 | 2024-09-09 12:12:54 |
| 4 | [9, 2, 3] | d | 2024-09-09 12:13:39 | 2024-09-09 12:13:39 |
±—±----------±-----±--------------------±--------------------+
2 rows in set (0.01 sec)

[127.0.0.1] [test] 12:26:27 >
[127.0.0.1] [test] 12:26:27 >explain select *
→ from test
→ where json_extract(trackid, ‘$[0]’) = 9;
±--------------------------±--------±-------------±--------------±-------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
±--------------------------±--------±-------------±--------------±-------------------------------------------------------------------------+
| Selection_9 | 3.20 | root | | eq(json_extract(test.test.trackid, “$[0]”), cast(9, json BINARY)) |
| └─TableReader_8 | 4.00 | root | | MppVersion: 2, data:ExchangeSender_7 |
| └─ExchangeSender_7 | 4.00 | mpp[tiflash] | | ExchangeType: PassThrough |
| └─TableFullScan_6 | 4.00 | mpp[tiflash] | table:test | keep order:false, stats:partial[id:unInitialized, trackid:unInitialized] |
±--------------------------±--------±-------------±--------------±-------------------------------------------------------------------------+
4 rows in set, 2 warnings (0.01 sec)

[127.0.0.1] [test] 12:26:32 >explain select *
→ from test
→ where json_extract(trackid, ‘$[0]’) = 9
→ order by id;
±----------------------------±--------±-------------±--------------±-------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
±----------------------------±--------±-------------±--------------±-------------------------------------------------------------------------+
| Sort_5 | 3.20 | root | | test.test.id |
| └─Selection_12 | 3.20 | root | | eq(json_extract(test.test.trackid, “$[0]”), cast(9, json BINARY)) |
| └─TableReader_11 | 4.00 | root | | MppVersion: 2, data:ExchangeSender_10 |
| └─ExchangeSender_10 | 4.00 | mpp[tiflash] | | ExchangeType: PassThrough |
| └─TableFullScan_9 | 4.00 | mpp[tiflash] | table:test | keep order:false, stats:partial[id:unInitialized, trackid:unInitialized] |
±----------------------------±--------±-------------±--------------±-------------------------------------------------------------------------+
5 rows in set, 2 warnings (0.00 sec)

[Hacker_xUwtuKxa]

您这里有2个warnings，能否再拉一下warnings信息？
谢谢~~

[TiDBer_xTvoCh2f]

±--------±-----±------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
±--------±-----±------------------------------------------------------------------------------------------------------------------+
| Warning | 1105 | Scalar function ‘cast’(signature: CastIntAsJson, return type: json) is not supported to push down to tiflash now. |
| Warning | 1105 | Scalar function ‘cast’(signature: CastIntAsJson, return type: json) is not supported to push down to tiflash now. |

说下推问题

[TiDBer_xTvoCh2f]

explain select *
from test
where JSON_UNQUOTE(JSON_EXTRACT(trackid, ‘$[0]’)) = 9
order by id;

explain select *
from test
where trackid->>‘$[0]’ = 9
order by id;

±----------------------------±--------±-------------±--------------±--------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
±----------------------------±--------±-------------±--------------±--------------------------------------------------------------------------------------------------------------+
| Sort_5 | 3.20 | root | | test.test.id |
| └─TableReader_12 | 3.20 | root | | MppVersion: 2, data:ExchangeSender_11 |
| └─ExchangeSender_11 | 3.20 | mpp[tiflash] | | ExchangeType: PassThrough |
| └─Selection_10 | 3.20 | mpp[tiflash] | | eq(cast(json_unquote(cast(json_extract(test.test.trackid, “$[0]”), var_string(16777216))), double BINARY), 9) |
| └─TableFullScan_9 | 4.00 | mpp[tiflash] | table:test | pushed down filter:empty, keep order:false, stats:partial[id:unInitialized, trackid:unInitialized] |
±----------------------------±--------±-------------±--------------±--------------------------------------------------------------------------------------------------------------+
5 rows in set (0.01 sec)

这几种没有warnings

[洪七表哥 (Ti D Ber Z Un Fk8 Og)]

tiflash数据和表数据不一致了？

[qihuajun]

Result_104.sql (25.5 KB)
导出的数据，我们的json字段存储的只有一个值

[TiDBer_xTvoCh2f]

导入也没有重现，你试试上面换个方法行不
where JSON_UNQUOTE(JSON_EXTRACT(trackid, ‘$[0]’)) = 9
where trackid->>‘$[0]’ = 9

[qihuajun]

@TiDBer_xTvoCh2f

trackid->>‘$[0]’ = 9

你想办法让它走TiFlash查询，我这边走TiFlash必重现，查询计划：

TableReader_16	17588.00	root		MppVersion: 2, data:ExchangeSender_15
└─ExchangeSender_15	17588.00	mpp[tiflash]		ExchangeType: PassThrough
  └─Selection_14	17588.00	mpp[tiflash]		eq(json_extract(mbase.test.trackid, "$[0]"), cast(9, json BINARY))
    └─TableFullScan_13	21985.00	mpp[tiflash]	table:test	pushed down filter:empty, keep order:false, stats:pseudo

[qihuajun]

查询结果截图：

[TiDBer_xTvoCh2f]

[127.0.0.1] [test] 15:52:21 >explain select *
→ from MY_TABLE
→ where json_extract(trackid, ‘$[0]’) = 9
→ order by id;
±----------------------------±--------±-------------±---------------±-------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
±----------------------------±--------±-------------±---------------±-------------------------------------------------------------------------+
| Sort_5 | 244.80 | root | | test.my_table.id |
| └─Selection_12 | 244.80 | root | | eq(json_extract(test.my_table.trackid, “$[0]”), cast(9, json BINARY)) |
| └─TableReader_11 | 306.00 | root | | MppVersion: 2, data:ExchangeSender_10 |
| └─ExchangeSender_10 | 306.00 | mpp[tiflash] | | ExchangeType: PassThrough |
| └─TableFullScan_9 | 306.00 | mpp[tiflash] | table:MY_TABLE | keep order:false, stats:partial[id:unInitialized, trackid:unInitialized] |
±----------------------------±--------±-------------±---------------±-------------------------------------------------------------------------+
5 rows in set, 2 warnings (0.00 sec)

[127.0.0.1] [test] 15:52:34 >
[127.0.0.1] [test] 15:52:34 >select @@version;
±-------------------+
| @@version |
±-------------------+
| 8.0.11-TiDB-v7.5.2 |
±-------------------+
1 row in set (0.00 sec)

你是什么版本？不能下推的消息显示不一样

[qihuajun]

【 TiDB 版本】8.1.0

[qihuajun]

8.0.11-TiDB-v8.1.0

[qihuajun]

我们这是最近才升级到8.1.0，之前7.5的版本也没这个问题

[TiDBer_xTvoCh2f]

测试环境装了8.1，还是没问题。

[FutureDB]

那有可能是Bug了

[qihuajun]

你查询走的是TiFlash吗？

[TiDBer_xTvoCh2f]

是的，都kv和flash都测试了每次

[qihuajun]

好的，我们的TiFlash节点之前挂过一次，我们先排查一下那个节点的问题