Execution Plan Error in Subquery

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

Original topic: in 子查询执行计划错误

| username: Hacker_ojLJ8Ndr

[TiDB Version]
7.1.1 / 7.1.0

Issue: Incorrect execution plan for in subquery, actual results do not match returned results

  1. Create table:
CREATE TABLE `in_test` (
  `id` bigint(20) unsigned NOT NULL,
  `name` varchar(45) NOT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED*/,
  KEY `inx_name` (`name`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin 
PARTITION BY HASH (`id`) PARTITIONS 128;
  1. Insert data:
insert into in_test(id, name) 
values(6700402571, 'Zhang San'), (1624923463, 'Li Si'), (5837848666, 'Wang Wu'), (6542259574, 'Zhao Liu');
  1. Query statement:
select id,
       `name`
from `in_test`
where id in
      (1624923463, 1624923463, 5837848666, 5837848666, 6700402571, 6700402571, 6542259574, 6542259574);
  1. v7.1.1 Execution plan (incorrect return):
id               |estRows|actRows|task|access object                            |execution info                                                                                                                                                                                                                                             |operator info                                                                                                                 |memory|disk|
-----------------+-------+-------+----+-----------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------+------+----+
Batch_Point_Get_1|8.00   |1      |root|table:in_test, partition:p11,p71,p90,p118|time:918.7µs, loops:2, RU:0.500977, BatchGet:{num_rpc:2, total_time:1.58ms}, tikv_wall_time: 988.5µs, scan_detail: {total_process_keys: 1, total_process_keys_size: 64, total_keys: 4, get_snapshot_time: 617.1µs, rocksdb: {block: {cache_hit_count: 25}}}|handle:[1624923463 1624923463 5837848666 5837848666 6700402571 6700402571 6542259574 6542259574], keep order:false, desc:false|N/A   |N/A |
  1. v6.5.2 Execution plan (correct return):
id               |estRows|actRows|task|access object                            |execution info                                                                                                                                                                                                            |operator info                                                                                                                 |memory|disk|
-----------------+-------+-------+----+-----------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------+------+----+
Batch_Point_Get_1|8.00   |4      |root|table:in_test, partition:p11,p71,p90,p118|time:2.66ms, loops:2, BatchGet:{num_rpc:4, total_time:6.66ms}, tikv_wall_time: 1.48ms, scan_detail: {total_process_keys: 4, total_process_keys_size: 256, total_keys: 4, get_snapshot_time: 217.9µs, rocksdb: {block: {}}}|handle:[1624923463 1624923463 5837848666 5837848666 6700402571 6700402571 6542259574 6542259574], keep order:false, desc:false|N/A   |N/A |
| username: 大飞哥online | Original post link

Are you referring to actrows? Is that incorrect?

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

I tried it on 7.1.1:
image
The result changes when the same value is repeated in the IN condition…

| username: Hacker_ojLJ8Ndr | Original post link

Yes, the actual return is also incorrect. It should correctly return 4 rows, but it only returned 1 row.

| username: Hacker_ojLJ8Ndr | Original post link

Yes, versions prior to 7.1.0 did not have this issue; it was likely introduced in 7.1.0.

| username: 大飞哥online | Original post link

Mine is 7.1.0, no issues.

| username: Hacker_ojLJ8Ndr | Original post link

v7.0.0


v7.1.0

| username: Hacker_ojLJ8Ndr | Original post link

Can you take a screenshot? You can look at my screenshot.

| username: 大飞哥online | Original post link

One moment, I’ll take a screenshot.

| username: 大飞哥online | Original post link

I have no issues running it on SQLyog, but there are problems when using the command line.

| username: 大飞哥online | Original post link

Sorry, I can’t translate images. Please provide the text you need translated.

| username: Hacker_ojLJ8Ndr | Original post link

It is not possible to use third-party software to execute it. There might be processing related to SQL, so it needs to be executed using the client.

| username: 大飞哥online | Original post link

Black screen. There is a problem when executing the command line.

| username: 大飞哥online | Original post link

Submit a bug :joy:

| username: zhanggame1 | Original post link

It seems to be a bug. There appear to be quite a few bugs related to partition table partition value queries.

| username: 人如其名 | Original post link

It should be a BUG. You can try creating a non-clustered index table to see if you can bypass it first.

| username: 大飞哥online | Original post link

Yes, there is no problem with non-clustered tables.

| username: Kongdom | Original post link

I executed it, and it actually reproduced. It’s hard to understand, hard to understand. Waiting for the official conclusion.

| username: Billmay表妹 | Original post link

I’ll provide some feedback!

| username: Billmay表妹 | Original post link

I saw the related issue: The subquery IN returns an error result · Issue #46779 · pingcap/tidb · GitHub