TIDB group by聚合 sum（加上having后）显示存在差异

MR-Abel · 2021 年12 月 7 日 03:12

【 TiDB 使用环境】
生产环境 tidb V5.1.1
【背景】做过哪些操作
基本SQL查询
【现象】业务和数据库现象
1、执行group by+ having后显示为负：
select collection_id,
SUM( CASE when projectName like ‘所有者%合计%’ or projectName like ‘净资产%’ THEN cast(replace(endBalance,‘,’,‘’) as decimal) ELSE 0 END) as equity
from tax_balance_sheet_record_item
GROUP BY collection_id HAVING collection_id=‘9ead706a6ee03bd6c548480abc351b8d’
显示数据为负的

2、只执行group by 确显示为正
select collection_id,
SUM( CASE when projectName like ‘所有者%合计%’ or projectName like ‘净资产%’ THEN cast(replace(endBalance,‘,’,‘’) as decimal) ELSE 0 END) as equity
from tax_balance_sheet_record_item
GROUP BY collection_id

表结构：
CREATE TABLE tax_balance_sheet_record_item (
id bigint(20) NOT NULL AUTO_INCREMENT,
tax_id varchar(20) DEFAULT NULL,
collection_id varchar(50) DEFAULT NULL,
endBalance varchar(50) DEFAULT NULL,
beginBalance varchar(50) DEFAULT NULL,
projectType varchar(200) DEFAULT NULL,
subprojectType varchar(200) DEFAULT NULL,
projectName varchar(100) DEFAULT NULL,
columnSequence varchar(50) DEFAULT NULL,
PRIMARY KEY (id),
KEY idx_collection_id (collection_id)
) ENGINE=InnoDB AUTO_INCREMENT=51670272 DEFAULT CHARSET=utf8;
【业务影响】
数据显示异常
【TiDB 版本】
tidb V5.1.1

Lucien-卢西恩 · 2021 年12 月 7 日 03:25

Hi，麻烦提供一下两个 SQL 的 explain analyze 的执行计划，先看一下执行计划，谢谢

Billmay表妹 · 2021 年12 月 7 日 04:01

执行计划上传规格参考

MR-Abel · 2021 年12 月 7 日 05:39

执行计划.xlsx (10.7 KB)

Lucien-卢西恩 · 2021 年12 月 7 日 08:58

你发的不是 explain analyze 缺少一些关键的 info 数据，建议重新收集一下，按照楼上同学提供的上传规范，重新上传一下。不用保留 excel 里面，放在 .sql 里面可以吧。

MR-Abel · 2021 年12 月 7 日 10:05

mysql> EXPLAIN select collection_id, SUM( CASE when projectName like '所有者%合计%' or projectName  like '净资产%'   THEN cast(replace(endBalance,',','') as decimal) ELSE 0 END) as equity from tax_balance_sheet_record_item  GROUP BY collection_id HAVING  collection_id='9ead706a6ee03bd6c548480abc351b8d';
+------------------------------------+---------+-----------+-----------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                 | estRows | task      | access object                                                               | operator info                                                                                                                                                                                                                                                                                                                                                        |
+------------------------------------+---------+-----------+-----------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_5                       | 1.10    | root      |                                                                             | data_lake.tax_balance_sheet_record_item.collection_id, Column#10                                                                                                                                                                                                                                                                                                     |
| └─HashAgg_22                   | 1.10    | root      |                                                                             | group by:data_lake.tax_balance_sheet_record_item.collection_id, funcs:sum(Column#14)->Column#10, funcs:firstrow(data_lake.tax_balance_sheet_record_item.collection_id)->data_lake.tax_balance_sheet_record_item.collection_id                                                                                                                                        |
|   └─IndexLookUp_23             | 1.10    | root      |                                                                             |                                                                                                                                                                                                                                                                                                                                                                      |
|     ├─IndexRangeScan_20(Build) | 79.53   | cop[tikv] | table:tax_balance_sheet_record_item, index:idx_collection_id(collection_id) | range:["9ead706a6ee03bd6c548480abc351b8d","9ead706a6ee03bd6c548480abc351b8d"], keep order:false                                                                                                                                                                                                                                                                      |
|     └─HashAgg_7(Probe)         | 1.10    | cop[tikv] |                                                                             | group by:data_lake.tax_balance_sheet_record_item.collection_id, funcs:sum(case(or(like(data_lake.tax_balance_sheet_record_item.projectname, "所有者%合计%", 92), like(data_lake.tax_balance_sheet_record_item.projectname, "净资产%", 92)), cast(replace(data_lake.tax_balance_sheet_record_item.endbalance, ",", ""), decimal(10,0) BINARY), 0))->Column#14 |
|       └─TableRowIDScan_21      | 79.53   | cop[tikv] | table:tax_balance_sheet_record_item                                         | keep order:false                                                                                                                                                                                                                                                                                                                                                     |
+------------------------------------+---------+-----------+-----------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.06 sec)
 
mysql> EXPLAIN select collection_id, SUM( CASE when projectName like '所有者%合计%' or projectName  like '净资产%'   THEN cast(replace(endBalance,',','') as decimal) ELSE 0 END) as equity from tax_balance_sheet_record_item  GROUP BY collection_id;
+----------------------------------------+--------------+-------------------+-------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                     | estRows      | task              | access object                       | operator info                                                                                                                                                                                                                                                                                                                            |
+----------------------------------------+--------------+-------------------+-------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_4                           | 1875637.08   | root              |                                     | data_lake.tax_balance_sheet_record_item.collection_id, Column#10                                                                                                                                                                                                                                                                         |
| └─TableReader_33                   | 1875637.08   | root              |                                     | data:ExchangeSender_32                                                                                                                                                                                                                                                                                                                   |
|   └─ExchangeSender_32              | 1875637.08   | batchCop[tiflash] |                                     | ExchangeType: PassThrough                                                                                                                                                                                                                                                                                                                |
|     └─Projection_28                | 1875637.08   | batchCop[tiflash] |                                     | Column#10, data_lake.tax_balance_sheet_record_item.collection_id                                                                                                                                                                                                                                                                         |
|       └─HashAgg_29                 | 1875637.08   | batchCop[tiflash] |                                     | group by:data_lake.tax_balance_sheet_record_item.collection_id, funcs:sum(Column#13)->Column#10, funcs:firstrow(data_lake.tax_balance_sheet_record_item.collection_id)->data_lake.tax_balance_sheet_record_item.collection_id                                                                                                            |
|         └─ExchangeReceiver_31      | 1875637.08   | batchCop[tiflash] |                                     |                                                                                                                                                                                                                                                                                                                                          |
|           └─ExchangeSender_30      | 1875637.08   | batchCop[tiflash] |                                     | ExchangeType: HashPartition, Hash Cols: data_lake.tax_balance_sheet_record_item.collection_id                                                                                                                                                                                                                                            |
|             └─HashAgg_9            | 1875637.08   | batchCop[tiflash] |                                     | group by:Column#20, funcs:sum(Column#19)->Column#13                                                                                                                                                                                                                                                                                      |
|               └─Projection_41      | 108652949.00 | batchCop[tiflash] |                                     | case(or(like(data_lake.tax_balance_sheet_record_item.projectname, 所有者%合计%, 92), like(data_lake.tax_balance_sheet_record_item.projectname, 净资产%, 92)), cast(replace(data_lake.tax_balance_sheet_record_item.endbalance, ,, ), decimal(10,0) BINARY), 0)->Column#19, data_lake.tax_balance_sheet_record_item.collection_id |
|                 └─TableFullScan_27 | 108652949.00 | batchCop[tiflash] | table:tax_balance_sheet_record_item | keep order:false                                                                                                                                                                                                                                                                                                                         |
+----------------------------------------+--------------+-------------------+-------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.05 sec)

Billmay表妹 · 2021 年12 月 7 日 13:06

传 explain analyze

Billmay表妹 · 2021 年12 月 7 日 13:07

Lucien-卢西恩 · 2021 年12 月 7 日 13:07

@MR-Abel 辛苦按照楼上的操作，收集一下 “explain analyze” 结果，https://docs.pingcap.com/zh/tidb/v5.2/sql-statement-explain-analyze#explain-analyze

Billmay表妹 · 2021 年12 月 7 日 13:07

提供一下两个 SQL 的 explain analyze 的执行计划

MR-Abel · 2021 年12 月 8 日 01:20

已经重新上传，麻烦帮忙分析，谢谢

mysql> EXPLAIN ANALYZE select collection_id, SUM( CASE when projectName like '所有者%合计%' or projectName  like '净资产%'   THEN cast(replace(endBalance,',','') as decimal) ELSE 0 END) as equity from tax_balance_sheet_record_item  GROUP BY collection_id HAVING  collection_id='9ead706a6ee03bd6c548480abc351b8d';
+------------------------------------+---------+---------+-----------+-----------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| id                                 | estRows | actRows | task      | access object                                                               | execution info                                                                                                                                                                                                                                                                                                                                         | operator info                                                                                                                                                                                                                                                                                                                                                        | memory  | disk |
+------------------------------------+---------+---------+-----------+-----------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| Projection_5                       | 1.10    | 1       | root      |                                                                             | time:2.32ms, loops:2, Concurrency:OFF                                                                                                                                                                                                                                                                                                                  | data_lake.tax_balance_sheet_record_item.collection_id, Column#10                                                                                                                                                                                                                                                                                                     | 2.01 KB | N/A  |
| └─HashAgg_22                   | 1.10    | 1       | root      |                                                                             | time:2.31ms, loops:2, partial_worker:{wall_time:2.256081ms, concurrency:5, task_num:1, tot_wait:10.819646ms, tot_exec:18.523µs, tot_time:10.849151ms, max:2.192575ms, p95:2.192575ms}, final_worker:{wall_time:2.300285ms, concurrency:5, task_num:1, tot_wait:11.063453ms, tot_exec:29.535µs, tot_time:11.095601ms, max:2.236367ms, p95:2.236367ms} | group by:data_lake.tax_balance_sheet_record_item.collection_id, funcs:sum(Column#14)->Column#10, funcs:firstrow(data_lake.tax_balance_sheet_record_item.collection_id)->data_lake.tax_balance_sheet_record_item.collection_id                                                                                                                                        | 24.2 KB | N/A  |
|   └─IndexLookUp_23             | 1.10    | 1       | root      |                                                                             | time:2.18ms, loops:2, index_task: {total_time: 1.09ms, fetch_handle: 1.08ms, build: 861ns, wait: 5.79µs}, table_task: {total_time: 6.93ms, num: 1, concurrency: 5}                                                                                                                                                                                    |                                                                                                                                                                                                                                                                                                                                                                      | 10.8 KB | N/A  |
|     ├─IndexRangeScan_20(Build) | 79.53   | 53      | cop[tikv] | table:tax_balance_sheet_record_item, index:idx_collection_id(collection_id) | time:1.07ms, loops:3, cop_task: {num: 1, max: 993.1µs, proc_keys: 53, rpc_num: 1, rpc_time: 979.1µs, copr_cache_hit_ratio: 0.00}, tikv_task:{time:0s, loops:2}, scan_detail: {total_process_keys: 53, total_keys: 54, rocksdb: {delete_skipped_count: 0, key_skipped_count: 53, block: {cache_hit_count: 13, read_count: 0, read_byte: 0 Bytes}}}    | range:["9ead706a6ee03bd6c548480abc351b8d","9ead706a6ee03bd6c548480abc351b8d"], keep order:false                                                                                                                                                                                                                                                                      | N/A     | N/A  |
|     └─HashAgg_7(Probe)         | 1.10    | 1       | cop[tikv] |                                                                             | time:847.2µs, loops:2, cop_task: {num: 1, max: 761.2µs, proc_keys: 0, rpc_num: 1, rpc_time: 751.5µs, copr_cache_hit_ratio: 1.00}, tikv_task:{time:13ms, loops:1}                                                                                                                                                                                    | group by:data_lake.tax_balance_sheet_record_item.collection_id, funcs:sum(case(or(like(data_lake.tax_balance_sheet_record_item.projectname, "所有者%合计%", 92), like(data_lake.tax_balance_sheet_record_item.projectname, "净资产%", 92)), cast(replace(data_lake.tax_balance_sheet_record_item.endbalance, ",", ""), decimal(10,0) BINARY), 0))->Column#14 | N/A     | N/A  |
|       └─TableRowIDScan_21      | 79.53   | 53      | cop[tikv] | table:tax_balance_sheet_record_item                                         | tikv_task:{time:7ms, loops:1}                                                                                                                                                                                                                                                                                                                          | keep order:false                                                                                                                                                                                                                                                                                                                                                     | N/A     | N/A  |
+------------------------------------+---------+---------+-----------+-----------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
6 rows in set (0.03 sec)
 
mysql> EXPLAIN ANALYZE  select collection_id, SUM( CASE when projectName like '所有者%合计%' or projectName  like '净资产%'   THEN cast(replace(endBalance,',','') as decimal) ELSE 0 END) as equity from tax_balance_sheet_record_item GROUP BY collection_id ;
+----------------------------------------+--------------+-----------+-------------------+-------------------------------------+---------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| id                                     | estRows      | actRows   | task              | access object                       | execution info                                                                                                | operator info                                                                                                                                                                                                                                                                                                                            | memory   | disk |
+----------------------------------------+--------------+-----------+-------------------+-------------------------------------+---------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| Projection_4                           | 1875637.08   | 1884317   | root              |                                     | time:1m50.1s, loops:1879, Concurrency:5                                                                       | data_lake.tax_balance_sheet_record_item.collection_id, Column#10                                                                                                                                                                                                                                                                         | 805.9 KB | N/A  |
| └─TableReader_33                   | 1875637.08   | 1884317   | root              |                                     | time:1m50.1s, loops:1879, cop_task: {num: 87, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | data:ExchangeSender_32                                                                                                                                                                                                                                                                                                                   | N/A      | N/A  |
|   └─ExchangeSender_32              | 1875637.08   | 1884317   | batchCop[tiflash] |                                     | tiflash_task:{time:1m48.8s, loops:256, threads:1}                                                             | ExchangeType: PassThrough                                                                                                                                                                                                                                                                                                                | N/A      | N/A  |
|     └─Projection_28                | 1875637.08   | 1884317   | batchCop[tiflash] |                                     | tiflash_task:{time:1m48.8s, loops:256, threads:1}                                                             | Column#10, data_lake.tax_balance_sheet_record_item.collection_id                                                                                                                                                                                                                                                                         | N/A      | N/A  |
|       └─HashAgg_29                 | 1875637.08   | 1884317   | batchCop[tiflash] |                                     | tiflash_task:{time:1m48.8s, loops:256, threads:1}                                                             | group by:data_lake.tax_balance_sheet_record_item.collection_id, funcs:sum(Column#13)->Column#10, funcs:firstrow(data_lake.tax_balance_sheet_record_item.collection_id)->data_lake.tax_balance_sheet_record_item.collection_id                                                                                                            | N/A      | N/A  |
|         └─ExchangeReceiver_31      | 1875637.08   | 1884317   | batchCop[tiflash] |                                     | tiflash_task:{time:1m48.7s, loops:86, threads:32}                                                             |                                                                                                                                                                                                                                                                                                                                          | N/A      | N/A  |
|           └─ExchangeSender_30      | 1875637.08   | 1884317   | batchCop[tiflash] |                                     | tiflash_task:{time:1m48.5s, loops:256, threads:32}                                                            | ExchangeType: HashPartition, Hash Cols: data_lake.tax_balance_sheet_record_item.collection_id                                                                                                                                                                                                                                            | N/A      | N/A  |
|             └─HashAgg_9            | 1875637.08   | 1884317   | batchCop[tiflash] |                                     | tiflash_task:{time:1m48.5s, loops:256, threads:1}                                                             | group by:Column#20, funcs:sum(Column#19)->Column#13                                                                                                                                                                                                                                                                                      | N/A      | N/A  |
|               └─Projection_41      | 108652949.00 | 108644706 | batchCop[tiflash] |                                     | tiflash_task:{time:1m48.1s, loops:1857, threads:32}                                                           | case(or(like(data_lake.tax_balance_sheet_record_item.projectname, 所有者%合计%, 92), like(data_lake.tax_balance_sheet_record_item.projectname, 净资产%, 92)), cast(replace(data_lake.tax_balance_sheet_record_item.endbalance, ,, ), decimal(10,0) BINARY), 0)->Column#19, data_lake.tax_balance_sheet_record_item.collection_id | N/A      | N/A  |
|                 └─TableFullScan_27 | 108652949.00 | 108644706 | batchCop[tiflash] | table:tax_balance_sheet_record_item | tiflash_task:{time:1.15s, loops:1857, threads:32}                                                             | keep order:false                                                                                                                                                                                                                                                                                                                         | N/A      | N/A  |
+----------------------------------------+--------------+-----------+-------------------+-------------------------------------+---------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
10 rows in set (110.25 sec)```

xuyifangreeneyes · 2021 年12 月 8 日 07:51

请问 HAVING collection_id=‘9ead706a6ee03bd6c548480abc351b8d’ 子句等号右边换成其他的字符串常量也会出现这种错误吗

xuyifangreeneyes · 2021 年12 月 8 日 07:56

另外想问一下能否判断是数据为负是正确的还是数据为正是正确的？（比如带 having 子句的那个 sql 改成 where 子句看一下输出结果）

MR-Abel · 2021 年12 月 8 日 08:02

改成其他也有，group by不加过滤子句，就全是正数（实际应该有不少是负的）

mysql> select collection_id, SUM( CASE when projectName like '所有者%合计%' or projectName  like '净资产%'   THEN cast(replace(endBalance,',','') as decimal) ELSE 0 END) as equity from tax_balance_sheet_record_item  where collection_id='9ead706a6ee03bd6c548480abc351b8d'
    -> ;
+----------------------------------+---------+
| collection_id                    | equity  |
+----------------------------------+---------+
| 9ead706a6ee03bd6c548480abc351b8d | -565330 |
+----------------------------------+---------+
1 row in set (0.03 sec)
 
mysql> select collection_id, CASE when projectName like '所有者%合计%' or projectName  like '净资产%'   THEN cast(replace(endBalance,',','') as decimal) ELSE 0 END as equity from tax_balance_sheet_record_item  where collection_id='9ead706a6ee03bd6c548480abc351b8d';
+----------------------------------+---------+
| collection_id                    | equity  |
+----------------------------------+---------+
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
| 9ead706a6ee03bd6c548480abc351b8d | -565330 |
| 9ead706a6ee03bd6c548480abc351b8d | 0       |
+----------------------------------+---------+
53 rows in set (0.03 sec)

MR-Abel · 2021 年12 月 8 日 08:05

这是换成其他的 collection_id=“05a246734ccd8a86c9f11d235d98dfd7”

xuyifangreeneyes · 2021 年12 月 8 日 09:13

这样看第二条 sql 全部为正的结果是错误的。该 sql 从执行计划上看走了 tiflash。可以在 sql 中加注释 /*+ READ_FROM_STORAGE(TIKV[tax_balance_sheet_record_item]) */ 试一下，看看执行结果是否不同。

SELECT /*+ READ_FROM_STORAGE(TIKV[tax_balance_sheet_record_item]) */ collection_id,
       Sum(CASE
             WHEN projectname LIKE '所有者%合计%'
                   OR projectname LIKE '净资产%' THEN Cast(
             Replace(endbalance, ',', '') AS DECIMAL)
             ELSE 0
           END) AS equity
FROM   tax_balance_sheet_record_item
GROUP  BY collection_id;

MR-Abel · 2021 年12 月 8 日 09:36

从结果来看这里是正确的

tiflash同步进度

xuyifangreeneyes · 2021 年12 月 8 日 09:46

这应该是一个 bug。开了一个 issue 记录：https://github.com/pingcap/tidb/issues/30542 。

MR-Abel · 2021 年12 月 8 日 09:48

好的，感谢协助，后续我跟踪下这个issue

neolithic · 2021 年12 月 24 日 01:28

竟然还有这种bug，orz