Abnormal Results Due to Duplicate Values in Subqueries

[translator_bot]

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

Original topic: in 子查询中，数值重复，结果异常

| username: 大飞哥online

---

Bug Report
Clearly and accurately describe the issue you found. Providing any steps to reproduce the problem can help the development team address it promptly.
[TiDB Version] v7.1.0+
[Impact of the Bug]
Incorrect query results
[Possible Steps to Reproduce the Issue]
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;

Insert data:

insert into in_test(id, name) 
values(6700402571, 'Zhang San'), (1624923463, 'Li Si'), (5837848666, 'Wang Wu'), (6542259574, 'Zhao Liu');

Query data:

SELECT id, `name` FROM `in_test` WHERE id IN 
(1624923463, 1624923463, 5837848666, 5837848666, 6700402571, 6700402571, 6542259574, 6542259574);

Query results:

Execution plan:

Third-party tool, SQLyog:

[translator_bot]

| username: zhanggame1 | Original post link

---

Is SQLyog used to query TiDB or MySQL?

[translator_bot]

| username: 大飞哥online | Original post link

---

Checked on the same TiDB server.

[translator_bot]

| username: 大飞哥online | Original post link

---

The table is a non-clustered table, and there are no issues when querying again.
It seems to be a bug with the clustered table.

[translator_bot]

| username: zhanggame1 | Original post link

---

The same SQL in the same database should not return different results. You can check the reason.

[translator_bot]

| username: Billmay表妹 | Original post link

---

Can other partners reproduce this?

[translator_bot]

| username: 有猫万事足 | Original post link

---

Version 7.3.0 can reproduce the issue. The result is normal after removing the hash PARTITION.

The subquery IN returns an error result

It seems that it has also been submitted on GitHub.

[translator_bot]

| username: 大飞哥online | Original post link

---

That is a bug caused by hash partition + clustered index.

[translator_bot]

| username: Hacker_ojLJ8Ndr | Original post link

---

Oh, you’re quick to jump on the question

in 子查询执行计划错误

The subquery IN returns an error result

[translator_bot]

| username: system | Original post link

---

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.