Abnormal Results Due to Duplicate Values in Subqueries

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,
  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 

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:

| username: zhanggame1 | Original post link

Is SQLyog used to query TiDB or MySQL?

| username: 大飞哥online | Original post link

Checked on the same TiDB server.

| 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.

| username: zhanggame1 | Original post link

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

| username: Billmay表妹 | Original post link

Can other partners reproduce this?

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

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

It seems that it has also been submitted on GitHub.

| username: 大飞哥online | Original post link

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

| username: Hacker_ojLJ8Ndr | Original post link

Oh, you’re quick to jump on the question :thinking:

| username: system | Original post link

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