Bug #105014 query gets incorrect resultset from table with subpartitions
Submitted: 23 Sep 2021 3:32 Modified: 23 Sep 2021 6:10
Reporter: Brian Yue (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Partitions Severity:S1 (Critical)
Version:8.0.25, 8.0.26, 8.0.11, 5.7.35 OS:Any (rhel-7.4)
Assigned to: CPU Architecture:Any (x86-64)
Tags: incorrect resultset, partition, subpartition

[23 Sep 2021 3:32] Brian Yue
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)
[23 Sep 2021 6:10] MySQL Verification Team
Hello Brian Yue,

Thank you for the report and feedback.
Observed that 8.0.26 release build affected.

regards,
Umesh
[23 Sep 2021 15:33] Brian Yue
bug is fixed basing on version MySQL8.0.25

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug_fix_105014.txt (text/plain), 4.89 KiB.