Issue of Inconsistent Return Results Due to Different Condition Positions When Counting in TiDB v5.3.0

Translated
1

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

Original topic: tidb v5.3.0统计数量时判断条件位置不同导致返回结果不一致问题

| username: Hacker_CsHliJiW

【TiDB Usage Environment】Production Environment / Testing / PoC
【TiDB Version】v5.3.0
【Encountered Problem】When counting the data volume of a single table, different results are returned due to the different order of conditions used.
【Reproduction Path】Operations performed that led to the problem
【Problem Phenomenon and Impact】

image
image854×490 15.2 KB

image
image964×396 15.7 KB

image
image1358×514 27.4 KB

image
image1363×481 25 KB

2
| username: Hacker_CsHliJiW | Original post link

At present, it seems that when order_status is placed at the end, the execution plan does not use the CRC32 condition.

3
| username: yilong | Original post link

Please provide the table structure for easier reproduction, thank you.

4
| username: Hacker_CsHliJiW | Original post link

Here is the table structure:

CREATE TABLE `order_center`.`Untitled` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `unique_incr_id` bigint(20) NOT NULL DEFAULT 0,
  `transaction_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `source_id` bigint(20) NOT NULL,
  `persist_table` tinyint(4) NOT NULL,
  `order_id` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `order_id_key` bigint(20) GENERATED ALWAYS AS (crc32(`order_id`)) VIRTUAL,
  `source_app_id` smallint(6) NOT NULL,
  `uid` bigint(20) NOT NULL DEFAULT 0,
  `deviceid` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `order_source` tinyint(4) NOT NULL,
  `order_channel` smallint(6) NOT NULL,
  `app_channel` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `order_status` tinyint(4) NOT NULL DEFAULT 1,
  `order_amount` int(11) NOT NULL DEFAULT 0,
  `pay_time` int(11) NOT NULL DEFAULT 0,
  `order_create_time` timestamp(0) NULL DEFAULT NULL,
  `pay_channel` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `pay_channel_code` tinyint(4) NOT NULL DEFAULT 20,
  `pay_amount` int(11) NOT NULL DEFAULT 0,
  `refund_amount` int(11) NOT NULL DEFAULT 0,
  `refund_time` int(11) NOT NULL DEFAULT 0,
  `transfer` tinyint(4) NOT NULL DEFAULT 0,
  `discount` bigint(20) NULL DEFAULT 0,
  `currency` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `quantity` int(11) NOT NULL DEFAULT 1,
  `product_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `product_code_key` bigint(20) GENERATED ALWAYS AS (crc32(`product_code`)) VIRTUAL NULL,
  `product_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT '',
  `product_name_key` bigint(20) GENERATED ALWAYS AS (crc32(`product_name`)) VIRTUAL NULL,
  `product_type` tinyint(4) NULL DEFAULT 0,
  `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
  `update_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0),
  `version` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `uid_index`(`uid`) USING BTREE,
  INDEX `pay_time_index`(`pay_time`) USING BTREE,
  INDEX `source_id_index`(`source_id`) USING BTREE,
  INDEX `pck_index`(`product_code_key`) USING BTREE,
  INDEX `pnk_index`(`product_name_key`) USING BTREE,
  UNIQUE INDEX `sid_tname_index`(`source_id`, `persist_table`) USING BTREE,
  INDEX `order_id_key`(`order_id_key`) USING BTREE,
  INDEX `idx_did`(`deviceid`) USING BTREE,
  INDEX `order_ct_idx`(`order_create_time`) USING BTREE,
  UNIQUE INDEX `unique_idx`(`unique_incr_id`) USING BTREE,
  INDEX `tid_idx`(`transaction_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 36589893 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin= 'Main Order Table' ROW_FORMAT = Compact;
5
| username: Hacker_CsHliJiW | Original post link

The same table structure, we only encountered this issue in one environment out of the two.

6
| username: yilong | Original post link

If the issue is not consistently reproducible, please refer to the following documentation and use sql-plan-replayer to export information from both the problematic cluster and the normal cluster.

7
| username: Jiawei | Original post link

I think separating the two conditions of “or” and then adding the result should be fine. It seems that too many “and” or “or” are causing issues with the execution plan.