The results of SQL queries using Tiflash are inconsistent with equivalent statements

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: SQL查询走Tiflash 等价语句得出来的结果不一致

| username: 天风来特

【TiDB Usage Environment】Production Environment
【TiDB Version】4.0.10
【Reproduction Path】What operations were performed to encounter the issue
【Encountered Issue: Phenomenon and Impact】

  1. Executing the following query:
SELECT /*+ read_from_storage(tiflash[o,oi]) */
  sum(oi.money) AS money
FROM
  order_item oi
  INNER JOIN orders o ON o.order_uuid = oi.order_uuid
  AND o.is_deleted = 0
WHERE
  oi.is_deleted = 0
  AND oi.create_time >= '2023-03-30 21:00:00'
  AND o.partner_delivery_type = 2
  AND '2023-05-30 21:00:00' > oi.create_time 

The result obtained is 645958.12

  1. Modifying the last condition to AND oi.create_time < '2023-05-30 21:00:00', i.e., the SQL becomes:
SELECT /*+ read_from_storage(tiflash[o,oi]) */
  sum(oi.money) AS money
FROM
  order_item oi
  INNER JOIN orders o ON o.order_uuid = oi.order_uuid
  AND o.is_deleted = 0
WHERE
  oi.is_deleted = 0
  AND oi.create_time >= '2023-04-30 21:00:00'
  AND o.partner_delivery_type = 2
  AND oi.create_time < '2023-05-30 21:00:00'

The result obtained is: 26858204.07

The results of equivalent SQL queries are inconsistent. If both use TiKV, the results are consistent.


Can anyone explain why?
【Resource Configuration】
【Attachments: Screenshots/Logs/Monitoring】

| username: Running | Original post link

Check if tiflash_fastscan is enabled. I encountered a similar issue before where the query SQL didn’t change, but the results were different each time. When this parameter is enabled, there can be some discrepancies in TiFlash queries. If you require higher query performance, you can enable the FastScan feature at the session or global level. You can choose whether to enable the FastScan feature by modifying the value of the tiflash_fastscan variable.

| username: 天风来特 | Original post link

Does version 4.0.10 have this value? show global variables like 'tiflash_fastscan'; doesn’t show any value.

| username: tidb菜鸟一只 | Original post link

The results of TiKV are the same every time. What exactly are the results? Do they not match with TiFlash?

| username: 天风来特 | Original post link

Yes, it’s different with Tiflash. Look at the screenshot results.

| username: 小王同学Plus | Original post link

Hello, have you tested the higher versions? Version 4.x is relatively old. There might be known bugs, so it’s recommended to use version 6.x.

| username: redgame | Original post link

You can try updating it.

| username: Anna | Original post link

Upgrade, upgrade.