Description:
Hello, dear verification team,
We find a case that a SELECT statement may get incorrect resultset if the table is subpartitioned, similar with bug#97041 but actually it's caused by a different reason.
Please reference to `How to repeat` for details.
How to repeat:
create database partition_clear_db;
use partition_clear_db;
CREATE TABLE `hft_task_sub1` (
`MNTSK_ID` varchar(17) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`STSK_ID` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`TSK_PRGRS_STCD` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`LCKD_IND` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '0',
`RCRD_RGTM` datetime(3) DEFAULT NULL,
`LOCK_ECD` varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`RCRD_NO` decimal(9,0) DEFAULT NULL,
`LOGICAL_PART_NO` int(11) NOT NULL DEFAULT '0',
`GDB_BID` int(11) NOT NULL,
`GTID` bigint(20) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`MNTSK_ID`,`LOGICAL_PART_NO`,`STSK_ID`),
KEY `GDB_BID` (`GDB_BID`),
KEY `IDX_HFT_TASK_SUB1` (`MNTSK_ID`,`TSK_PRGRS_STCD`,`LCKD_IND`),
KEY `IDX_HFT_TASK_SUB2` (`MNTSK_ID`,`RCRD_NO`,`TSK_PRGRS_STCD`),
KEY `IDX_HFT_TASK_SUB3` (`MNTSK_ID`,`LOCK_ECD`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
/*!50100 PARTITION BY LIST (`LOGICAL_PART_NO`)
SUBPARTITION BY KEY (MNTSK_ID)
SUBPARTITIONS 4
(PARTITION P1 VALUES IN (0) ENGINE = InnoDB,
PARTITION P2 VALUES IN (1) ENGINE = InnoDB,
PARTITION P3 VALUES IN (2) ENGINE = InnoDB,
PARTITION P4 VALUES IN (3) ENGINE = InnoDB,
PARTITION P5 VALUES IN (4) ENGINE = InnoDB,
PARTITION P6 VALUES IN (5) ENGINE = InnoDB,
PARTITION P7 VALUES IN (6) ENGINE = InnoDB) */;
insert into hft_task_sub1(MNTSK_ID,LOGICAL_PART_NO,STSK_ID,GDB_BID) values('1234123412341234',0,'000000200',1);
insert into hft_task_sub1(MNTSK_ID,LOGICAL_PART_NO,STSK_ID,GDB_BID) values('1235123512351235',1,'000000300',1);
insert into hft_task_sub1(MNTSK_ID,LOGICAL_PART_NO,STSK_ID,GDB_BID) values('1236123612361236',2,'000000400',1);
insert into hft_task_sub1(MNTSK_ID,LOGICAL_PART_NO,STSK_ID,GDB_BID) values('1237123712371237',3,'000000500',1);
insert into hft_task_sub1(MNTSK_ID,LOGICAL_PART_NO,STSK_ID,GDB_BID) values('1237123712371237',4,'000000500',1);
insert into hft_task_sub1(MNTSK_ID,LOGICAL_PART_NO,STSK_ID,GDB_BID) values('1238123812381238',4,'000000600',1);
insert into hft_task_sub1(MNTSK_ID,LOGICAL_PART_NO,STSK_ID,GDB_BID) values('1239123912391239',5,'000000700',1);
insert into hft_task_sub1(MNTSK_ID,LOGICAL_PART_NO,STSK_ID,GDB_BID) values('1233123312331233',6,'000000800',1);
mysql> SELECT stsk_id, logical_part_no FROM hft_task_sub1 WHERE mntsk_id='1236123612361236';
+-----------+-----------------+
| stsk_id | logical_part_no |
+-----------+-----------------+
| 000000400 | 2 |
+-----------+-----------------+
1 row in set (0.00 sec)
mysql>
mysql> select count(0) from hft_task_sub1 force index(IDX_HFT_TASK_SUB3) where mntsk_id = '1237123712371237' and LOGICAL_PART_NO = '3' and LOCK_ECD is NULL;
+----------+
| count(0) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
# Incorrect resultset
mysql> SELECT stsk_id, logical_part_no FROM hft_task_sub1 WHERE mntsk_id='1236123612361236';
Empty set (0.00 sec)
