Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
vivid392845427 opened this issue Aug 18, 2021 · 6 comments
Assignees
Labels
component/tablepartition This issue is related to Table Partition of TiDB. severity/moderate sig/planner SIG: Planner type/bug The issue is confirmed as a bug.

Comments

@vivid392845427
Copy link

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


@vivid392845427 vivid392845427 added the type/bug The issue is confirmed as a bug. label Aug 18, 2021
@vivid392845427 vivid392845427 changed the title set @@tidb_enable_index_merge=1,@@tidb_partition_prune_mode='dynamic'; select use index merge return incorrect result set @@tidb_enable_index_merge=1,@@tidb_partition_prune_mode='dynamic'; select use index merge return duplicate result Aug 18, 2021
@qw4990
Copy link
Contributor

qw4990 commented Aug 18, 2021

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

@mjonss
Copy link
Contributor

mjonss commented Feb 14, 2022

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
Copy link
Contributor

mjonss commented Feb 14, 2022

/component tablepartition

@ti-chi-bot ti-chi-bot added the component/tablepartition This issue is related to Table Partition of TiDB. label Feb 14, 2022
mjonss added a commit to mjonss/tidb that referenced this issue Feb 14, 2022
@mjonss
Copy link
Contributor

mjonss commented Mar 3, 2022

Fixed by #31634.

@mjonss
Copy link
Contributor

mjonss commented Mar 3, 2022

/close

@ti-chi-bot
Copy link
Member

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
component/tablepartition This issue is related to Table Partition of TiDB. severity/moderate sig/planner SIG: Planner type/bug The issue is confirmed as a bug.
Projects
None yet
Development

No branches or pull requests

7 participants