set @@tidb_enable_index_merge=1,@@tidb_partition_prune_mode='dynamic'; select use index merge return duplicate result

[vivid392845427]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

set @@tidb_enable_index_merge=1,@@tidb_partition_prune_mode='dynamic';

DROP TABLE IF EXISTS `tbl_18`;
CREATE TABLE `tbl_18` (`col_119` binary(16) NOT NULL DEFAULT 'skPoKiwYUi\0\0\0\0\0\0',`col_120` int(10) unsigned NOT NULL,`col_121` timestamp NOT NULL,`col_122` double NOT NULL DEFAULT '3937.1887880628115',`col_123` bigint(20) NOT NULL DEFAULT '3550098074891542725',PRIMARY KEY (`col_123`,`col_121`,`col_122`,`col_120`) CLUSTERED,UNIQUE KEY `idx_103` (`col_123`,`col_119`,`col_120`),UNIQUE KEY `idx_104` (`col_122`,`col_120`),UNIQUE KEY `idx_105` (`col_119`,`col_120`),KEY `idx_106` (`col_121`,`col_120`,`col_122`,`col_119`),KEY `idx_107` (`col_121`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci PARTITION BY HASH( `col_120` ) PARTITIONS 3;
INSERT INTO tbl_18 (`col_119`, `col_120`, `col_121`, `col_122`, `col_123`) VALUES (X'736b506f4b6977595569000000000000', 672436701, '1974-02-24 00:00:00', 3937.1887880628115e0, -7373106839136381229), (X'736b506f4b6977595569000000000000', 2637316689, '1993-10-29 00:00:00', 3937.1887880628115e0, -4522626077860026631), (X'736b506f4b6977595569000000000000', 831809724, '1995-11-20 00:00:00', 3937.1887880628115e0, -4426441253940231780), (X'736b506f4b6977595569000000000000', 1588592628, '2001-03-28 00:00:00', 3937.1887880628115e0, 1329207475772244999), (X'736b506f4b6977595569000000000000', 3908038471, '2031-06-06 00:00:00', 3937.1887880628115e0, -6562815696723135786), (X'736b506f4b6977595569000000000000', 1674237178, '2001-10-24 00:00:00', 3937.1887880628115e0, -6459065549188938772), (X'736b506f4b6977595569000000000000', 3507075493, '2010-03-25 00:00:00', 3937.1887880628115e0, -4329597025765326929), (X'736b506f4b6977595569000000000000', 1276461709, '2019-07-20 00:00:00', 3937.1887880628115e0, 3550098074891542725);

select col_120,col_122,col_123 from tbl_18 where tbl_18.col_122 = 4763.320888074281 and not( tbl_18.col_121 in ( '2032-11-01' , '1975-05-21' , '1994-05-16' , '1984-01-15' ) ) or not( tbl_18.col_121 >= '2008-10-24' ) order by tbl_18.col_119,tbl_18.col_120,tbl_18.col_121,tbl_18.col_122,tbl_18.col_123 limit 919 for update;
select /*+ use_index_merge( tbl_18 ) */ col_120,col_122,col_123 from tbl_18 where tbl_18.col_122 = 4763.320888074281 and not( tbl_18.col_121 in ( '2032-11-01' , '1975-05-21' , '1994-05-16' , '1984-01-15' ) ) or not( tbl_18.col_121 >= '2008-10-24' ) order by tbl_18.col_119,tbl_18.col_120,tbl_18.col_121,tbl_18.col_122,tbl_18.col_123 limit 919 for update;

2. What did you expect to see? (Required)

two select return the same result

3. What did you see instead (Required)

two select return different result,as follow:
mysql> select col_120,col_122,col_123 from tbl_18 where tbl_18.col_122 = 4763.320888074281 and not( tbl_18.col_121 in ( '2032-11-01' , '1975-05-21' , '1994-05-16' , '1984-01-15' ) ) or not( tbl_18.col_121 >= '2008-10-24' ) order by tbl_18.col_119,tbl_18.col_120,tbl_18.col_121,tbl_18.col_122,tbl_18.col_123 limit 919 for update;
+------------+--------------------+----------------------+
| col_120    | col_122            | col_123              |
+------------+--------------------+----------------------+
|  672436701 | 3937.1887880628115 | -7373106839136381229 |
|  831809724 | 3937.1887880628115 | -4426441253940231780 |
| 1588592628 | 3937.1887880628115 |  1329207475772244999 |
| 1674237178 | 3937.1887880628115 | -6459065549188938772 |
| 2637316689 | 3937.1887880628115 | -4522626077860026631 |
+------------+--------------------+----------------------+
5 rows in set (0.02 sec)

mysql> select /*+ use_index_merge( tbl_18 ) */ col_120,col_122,col_123 from tbl_18 where tbl_18.col_122 = 4763.320888074281 and not( tbl_18.col_121 in ( '2032-11-01' , '1975-05-21' , '1994-05-16' , '1984-01-15' ) ) or not( tbl_18.col_121 >= '2008-10-24' ) order by tbl_18.col_119,tbl_18.col_120,tbl_18.col_121,tbl_18.col_122,tbl_18.col_123 limit 919 for update;
+------------+--------------------+----------------------+
| col_120    | col_122            | col_123              |
+------------+--------------------+----------------------+
|  672436701 | 3937.1887880628115 | -7373106839136381229 |
|  672436701 | 3937.1887880628115 | -7373106839136381229 |
|  672436701 | 3937.1887880628115 | -7373106839136381229 |
|  831809724 | 3937.1887880628115 | -4426441253940231780 |
|  831809724 | 3937.1887880628115 | -4426441253940231780 |
|  831809724 | 3937.1887880628115 | -4426441253940231780 |
| 1588592628 | 3937.1887880628115 |  1329207475772244999 |
| 1588592628 | 3937.1887880628115 |  1329207475772244999 |
| 1588592628 | 3937.1887880628115 |  1329207475772244999 |
| 1674237178 | 3937.1887880628115 | -6459065549188938772 |
| 1674237178 | 3937.1887880628115 | -6459065549188938772 |
| 1674237178 | 3937.1887880628115 | -6459065549188938772 |
| 2637316689 | 3937.1887880628115 | -4522626077860026631 |
| 2637316689 | 3937.1887880628115 | -4522626077860026631 |
| 2637316689 | 3937.1887880628115 | -4522626077860026631 |
+------------+--------------------+----------------------+
15 rows in set (0.02 sec)

4. What is your TiDB version? (Required)

Release Version: v5.2.0-nightly
Edition: Community
Git Commit Hash: b2a1d21284b75e3137f499d8954071a7b32f7b3b
Git Branch: heads/refs/tags/v5.2.0-nightly
UTC Build Time: 2021-08-17 21:42:09
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[qw4990]

dynamic-mode is not GA, so I degrade its severity to moderate and I'll fix it the next Sprint.

[mjonss]

Similar to other SelectLock bugs for dynamic partition prune mode (maybe #28073 ).

Work in progress in #31634 which seems to solve this issue too. I will add the test case there too, for completeness.

[mjonss]

/component tablepartition

[mjonss]

Fixed by #31634.

[mjonss]

/close

[ti-chi-bot]

@mjonss: Closing this issue.

In response to this:

/close

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the kubernetes/test-infra repository.