TiFlash读取数据返回空集

【 TiDB 使用环境】测试
【遇到的问题：问题现象及影响】
求问大佬们，我正在 TiFlash 中测试一个查询，期望它从 TiKV 中读取数据，而不是从 TiFlash 中读取。但在使用 hint 执行计划后，它显示查询从 TiFlash 中读取数据。 并且返回了一个空集。
我在执行查询后还收到了这些警告。这可能是导致空集的原因吗？

MySQL [airpayme]> show warnings;
±--------±-----±-----------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
±--------±-----±-----------------------------------------------------------------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: ‘’ |
| Warning | 1292 | Truncated incorrect DOUBLE value: ‘’

可以使用sqlbind 走tikv。
SELECT
/*+ READ_FROM_STORAGE(TIKV[t2]) /
count() from table t2

–创建绑定

– Hint 能生效的用法

CREATE GLOBAL BINDING for SELECT * FROM t2 as 别名HERE a > 1 AND b = 1

USING SELECT /*+ use_index(‘别名’, ‘索引名称’) */ * FROM t2 as 别名 WHERE a > 1 AND b = 1;

–查询绑定

SHOW GLOBAL BINDINGS

–删除绑定

DROP GLOBAL BINDING FOR

SELECT * FROM t2 WHERE a > 1 AND b = 1

sql能发一下吗？这个看着是sql写的有问题

message.txt (27.8 KB)
大佬这里是SQL

好的谢谢大佬，我试试

if(‘’,A.profileid=‘’,1=1)
你这个判断条件有问题吧，这条件不如直接不要呢。。。。

大佬我直接去掉这个吗

不要你看下还有没有warning

这里是sql大佬

MySQL [test]> explain SELECT /*+ read_from_storage(tiflash[a,b,c,d,e]) */ date_time DIV 60 * 60 AS “time”, b.status AS metric, count(TXN_STATUS) AS “count” FROM records A join payment_status b on b.statusid=A.TXN_STATUS JOIN channels c ON A.channel_id = c.channel_id JOIN channel_master d ON c.pg_id=d.pg_id JOIN payment_way e ON e.pgid = d.pgid WHERE date_time >= 1731389400 AND date_time <= 1731393000 AND if(‘’,A.profileid=‘’,1=1) and txn_status in(‘1’,‘2’,‘3’,‘4’,‘5’,‘6’,‘7’,‘8’,‘9’,‘10’,‘11’,‘12’,‘13’,‘14’,‘15’,‘16’,‘17’,‘18’,‘19’,‘20’,‘21’,‘22’,‘23’,‘24’,‘25’,‘26’,‘27’,‘28’,‘29’,‘30’,‘31’,‘32’,‘33’,‘34’,‘35’,‘36’,‘37’,‘38’,‘39’,‘40’,‘41’,‘42’,‘43’,‘44’,‘45’,‘46’,‘47’) and txn_type in(‘pg’,‘pos’,‘ppc’,‘remit’,‘rtgs’,‘upi’,‘va’,‘wallet’) AND c.pg_id in (‘48’,‘1’,‘2’,‘179’,‘233’,‘218’,‘33’,‘93’,‘166’,‘205’,‘248’,‘14’,‘153’,‘175’,‘60’,‘217’,‘32’,‘165’,‘201’,‘247’,‘77’,‘174’,‘230’,‘6’,‘216’,‘31’,‘281’,‘279’,‘180’,‘115’,‘198’,‘4’,‘193’,‘50’,‘100’,‘108’,‘269’,‘53’,‘8’,‘120’,‘78’,‘91’,‘143’,‘138’,‘112’,‘265’,‘232’,‘274’,‘57’,‘98’,‘263’,‘262’,‘183’,‘261’,‘210’,‘144’,‘260’,‘259’,‘258’,‘271’,‘273’,‘257’,‘256’,‘267’,‘255’,‘79’,‘266’,‘181’,‘264’,‘64’,‘275’,‘176’,‘85’,‘280’,‘94’,‘195’,‘90’,‘236’,‘253’,‘246’,‘278’,‘202’,‘113’,‘276’,‘116’,‘196’,‘235’,‘254’,‘139’,‘252’,‘142’,‘272’,‘239’,‘207’,‘199’,‘251’,‘277’) GROUP BY 1,2 ORDER BY date_time DIV 60 * 60;
±-----------------------------------------------------±------------±-------------±--------------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
±-----------------------------------------------------±------------±-------------±--------------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_24 | 1238.29 | root | | Column#94->Column#95, test.payment_status.status, Column#93->Column#97 |
| └─Projection_78 | 1238.29 | root | | Column#94, test.payment_status.status, Column#93, test.records.date_time |
| └─Sort_26 | 1238.29 | root | | Column#104 |
| └─Projection_79 | 1238.29 | root | | Column#94, test.payment_status.status, Column#93, test.records.date_time, mul(intdiv(cast(test.records.date_time, decimal(20,0) UNSIGNED BINARY), 60), 60)->Column#104 |
| └─Projection_27 | 1238.29 | root | | mul(intdiv(cast(test.records.date_time, decimal(20,0) UNSIGNED BINARY), 60), 60)->Column#94, test.payment_status.status, Column#93, test.records.date_time |
| └─HashAgg_30 | 1238.29 | root | | group by:Column#102, Column#103, funcs:count(Column#99)->Column#93, funcs:firstrow(Column#100)->test.records.date_time, funcs:firstrow(Column#101)->test.payment_status.status |
| └─Projection_77 | 8706.92 | root | | test.records.txn_status->Column#99, test.records.date_time->Column#100, test.payment_status.status->Column#101, mul(intdiv(cast(test.records.date_time, decimal(20,0) UNSIGNED BINARY), 60), 60)->Column#102, test.payment_status.status->Column#103 |
| └─Projection_31 | 8706.92 | root | | test.records.txn_status, test.records.date_time, test.payment_status.status |
| └─HashJoin_35 | 8706.92 | root | | inner join, equal:[eq(test.channel_master.pgid, test.payment_way.pgid)] |
| ├─TableReader_75(Build) | 232.00 | root | | MppVersion: 2, data:ExchangeSender_74 |
| │ └─ExchangeSender_74 | 232.00 | mpp[tiflash] | | ExchangeType: PassThrough |
| │ └─TableFullScan_73 | 232.00 | mpp[tiflash] | table:e | keep order:false |
| └─HashJoin_46(Probe) | 6965.54 | root | | inner join, equal:[eq(test.channels.pg_id, test.channel_master.pg_id)] |
| ├─TableDual_72(Build) | 175.00 | root | | rows:0 |
| └─HashJoin_54(Probe) | 8733.27 | root | | inner join, equal:[eq(test.records.channel_id, test.channels.channel_id)] |
| ├─HashJoin_61(Build) | 8689.47 | root | | inner join, equal:[eq(test.payment_status.statusid, test.records.txn_status)] |
| │ ├─TableDual_63(Build) | 47.00 | root | | rows:0 |
| │ └─TableReader_67(Probe) | 6951.58 | root | | MppVersion: 2, data:ExchangeSender_66 |
| │ └─ExchangeSender_66 | 6951.58 | mpp[tiflash] | | ExchangeType: PassThrough |
| │ └─Selection_65 | 6951.58 | mpp[tiflash] | | in(test.records.txn_status, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47), in(test.records.txn_type, “pg”, “pos”, “ppc”, “remit”, “rtgs”, “upi”, “va”, “wallet”), not(isnull(test.records.channel_id)) |
| │ └─TableFullScan_64 | 13774.77 | mpp[tiflash] | table:A | pushed down filter:ge(test.records.date_time, 1.7313894e+09), le(test.records.date_time, 1.731393e+09), keep order:false |
| └─TableReader_71(Probe) | 22163122.46 | root | | MppVersion: 2, data:ExchangeSender_70 |
| └─ExchangeSender_70 | 22163122.46 | mpp[tiflash] | | ExchangeType: PassThrough |
| └─Selection_69 | 22163122.46 | mpp[tiflash] | | in(test.channels.pg_id, 48, 1, 2, 179, 233, 218, 33, 93, 166, 205, 248, 14, 153, 175, 60, 217, 32, 165, 201, 247, 77, 174, 230, 6, 216, 31, 281, 279, 180, 115, 198, 4, 193, 50, 100, 108, 269, 53, 8, 120, 78, 91, 143, 138, 112, 265, 232, 274, 57, 98, 263, 262, 183, 261, 210, 144, 260, 259, 258, 271, 273, 257, 256, 267, 255, 79, 266, 181, 264, 64, 275, 176, 85, 280, 94, 195, 90, 236, 253, 246, 278, 202, 113, 276, 116, 196, 235, 254, 139, 252, 142, 272, 239, 207, 199, 251, 277) |
| └─TableFullScan_68 | 29440982.00 | mpp[tiflash] | table:c | pushed down filter:empty, keep order:false |
±-----------------------------------------------------±------------±-------------±--------------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
25 rows in set, 5 warnings (0.01 sec)

你是要解决这个问题么？你 hint 加的是走 tiflash 啊。

还是说你发现走 tikv 和走 tiflash 结果不一致？

大佬，是想要解决：
使用tikv读取返回结果不是空集，但是我想要用tiflash返回读取结果。用了hint从tiflash返回结果是空集。 这个问题

explain analyze tikv.txt (42.0 KB)
explain analyze.txt (40.5 KB)
explain anlayze tiflash.txt (14.8 KB)
explain analyze tikv.txt (42.0 KB)

大佬试过了，没有warning了但是返回的还是空集

那就是 tikv 和 tiflash 结果不一致问题了。
提供下版本和最小复现案例，我可以帮忙摇个研发看看。

你这是channels表的tiflash副本有问题啊，你不带条件查能查到数据吗？

工单版本是 7.5.0。尝试升级 754 后问题解决

此话题已在最后回复的 7 天后被自动关闭。不再允许新回复。