MySQL Bugs: #109127: skip scan retrieves incorrect result after delete

Bug #109127	skip scan retrieves incorrect result after delete
Submitted:	17 Nov 2022 17:53	Modified:	18 Nov 2022 13:58
Reporter:	Fan Wang	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.31	OS:	Linux (Red Hat, CentOS, Kylin)
Assigned to:		CPU Architecture:	Any
Tags:	Bug #109124

Description:
Contributors: Xuanwei Zhao, Fan Wang, Yayun Zhou, Xinxin Yue

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.31    |
+-----------+
1 row in set (0.00 sec)

mysql> explain select max(dt) from skip_scan where prj_id = 'TEST007'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: skip_scan
   partitions: NULL
         type: range
possible_keys: index_1
          key: index_1
      key_len: 516
          ref: NULL
         rows: 85056
     filtered: 100.00
        Extra: Using where; Using index for skip scan
1 row in set, 1 warning (0.00 sec)

mysql> select max(dt) from skip_scan where prj_id = 'TEST007';
+----------+
| max(dt)  |
+----------+
| 20220229 |
+----------+
1 row in set (0.01 sec)

mysql> delete from skip_scan where dt like '2022011%';
Query OK, 170400 rows affected (1.38 sec)

mysql> select max(dt) from skip_scan where prj_id = 'TEST007';
+----------+
| max(dt)  |
+----------+
| 20220109 |
+----------+
1 row in set (0.00 sec)

# The correct result of max(dt) is 20220229.

mysql> select max(dt) from skip_scan where prj_id = 'TEST007' and dt = '20220229';
+----------+
| max(dt)  |
+----------+
| 20220229 |
+----------+
1 row in set (0.01 sec)

# The result without using skip scan is correct.

mysql> select /*+ NO_SKIP_SCAN(skip_scan) */ max(dt) from skip_scan where prj_id = 'TEST007';
+----------+
| max(dt)  |
+----------+
| 20220229 |
+----------+
1 row in set (0.01 sec)

How to repeat:
mysql> source skip_scan.sql

mysql> explain select max(dt) from skip_scan where prj_id = 'TEST007'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: skip_scan
   partitions: NULL
         type: range
possible_keys: index_1
          key: index_1
      key_len: 516
          ref: NULL
         rows: 85056
     filtered: 100.00
        Extra: Using where; Using index for skip scan
1 row in set, 1 warning (0.00 sec)

mysql> select max(dt) from skip_scan where prj_id = 'TEST007';
+----------+
| max(dt)  |
+----------+
| 20220229 |
+----------+
1 row in set (0.01 sec)

mysql> delete from skip_scan where dt like '2022011%';
Query OK, 170400 rows affected (1.38 sec)

mysql> select max(dt) from skip_scan where prj_id = 'TEST007';
+----------+
| max(dt)  |
+----------+
| 20220109 |
+----------+
1 row in set (0.00 sec)

mysql> select max(dt) from skip_scan where prj_id = 'TEST007' and dt = '20220229';
+----------+
| max(dt)  |
+----------+
| 20220229 |
+----------+
1 row in set (0.01 sec)

mysql> select /*+ NO_SKIP_SCAN(skip_scan) */ max(dt) from skip_scan where prj_id = 'TEST007';
+----------+
| max(dt)  |
+----------+
| 20220229 |
+----------+
1 row in set (0.01 sec)

# The skip_scan.sql file:

-- This file is just to structure the data.

drop table if exists skip_scan_t;
drop table if exists skip_scan;

create table skip_scan_t(
id int auto_increment primary key,
dt varchar(8) not null,
prj_id varchar(120) not null,
index index_1(dt,prj_id));

insert into skip_scan_t (dt,prj_id) values('20220101','TEST001'),('20220101','TEST002'),('20220101','TEST003'),('20220101','TEST004'),('20220101','TEST005'),('20220101','TEST005'),('20220101','TEST006'),('20220101','TEST007'),('20220101','TEST008'),('20220101','TEST009'),('20220101','TEST010');

insert into skip_scan_t (dt,prj_id) select dt,prj_id from skip_scan_t where prj_id = 'TEST009';
insert into skip_scan_t (dt,prj_id) select dt,prj_id from skip_scan_t where prj_id = 'TEST009';
insert into skip_scan_t (dt,prj_id) select dt,prj_id from skip_scan_t where prj_id = 'TEST009';
insert into skip_scan_t (dt,prj_id) select dt,prj_id from skip_scan_t where prj_id = 'TEST009';
insert into skip_scan_t (dt,prj_id) select dt,prj_id from skip_scan_t where prj_id = 'TEST009';

insert into skip_scan_t (dt,prj_id) select dt,prj_id from skip_scan_t where prj_id = 'TEST010';
insert into skip_scan_t (dt,prj_id) select dt,prj_id from skip_scan_t where prj_id = 'TEST010';
insert into skip_scan_t (dt,prj_id) select dt,prj_id from skip_scan_t where prj_id = 'TEST010';
insert into skip_scan_t (dt,prj_id) select dt,prj_id from skip_scan_t where prj_id = 'TEST010';
insert into skip_scan_t (dt,prj_id) select dt,prj_id from skip_scan_t where prj_id = 'TEST010';
insert into skip_scan_t (dt,prj_id) select dt,prj_id from skip_scan_t where prj_id = 'TEST010';
insert into skip_scan_t (dt,prj_id) select dt,prj_id from skip_scan_t where prj_id = 'TEST010';
insert into skip_scan_t (dt,prj_id) select dt,prj_id from skip_scan_t where prj_id = 'TEST010';
insert into skip_scan_t (dt,prj_id) select dt,prj_id from skip_scan_t where prj_id = 'TEST010';
insert into skip_scan_t (dt,prj_id) select dt,prj_id from skip_scan_t where prj_id = 'TEST010';

insert into skip_scan_t (dt,prj_id) select dt,prj_id from skip_scan_t;
insert into skip_scan_t (dt,prj_id) select dt,prj_id from skip_scan_t;
insert into skip_scan_t (dt,prj_id) select dt,prj_id from skip_scan_t;
insert into skip_scan_t (dt,prj_id) select dt,prj_id from skip_scan_t;

create table skip_scan like skip_scan_t;

insert into skip_scan (dt,prj_id) select '20220101',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220102',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220103',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220104',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220105',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220106',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220107',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220108',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220109',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220110',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220111',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220112',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220113',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220114',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220115',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220116',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220117',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220118',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220119',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220120',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220121',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220122',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220123',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220124',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220125',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220126',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220127',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220128',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220129',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220130',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220131',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220201',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220202',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220203',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220204',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220205',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220206',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220207',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220208',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220209',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220210',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220211',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220212',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220213',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220214',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220215',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220216',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220217',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220218',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220219',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220220',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220221',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220222',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220223',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220224',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220225',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220226',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220227',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220228',prj_id from skip_scan_t;
insert into skip_scan (dt,prj_id) select '20220229',prj_id from skip_scan_t;

analyze table skip_scan;

-- If the problem does not repeated, you can re-execute.

The problem is related to the amount of deleted data：

mysql> select max(dt) from skip_scan where prj_id = 'TEST007';
+----------+
| max(dt)  |
+----------+
| 20220229 |
+----------+
1 row in set (0.00 sec)

mysql> delete from skip_scan where dt like '2022011%' limit 100;
Query OK, 100 rows affected (0.02 sec)

mysql> select max(dt) from skip_scan where prj_id = 'TEST007';
+----------+
| max(dt)  |
+----------+
| 20220229 |
+----------+
1 row in set (0.01 sec)

mysql> delete from skip_scan where dt like '2022011%';
Query OK, 170300 rows affected (1.35 sec)

mysql> select max(dt) from skip_scan where prj_id = 'TEST007';
+----------+
| max(dt)  |
+----------+
| 20220109 |
+----------+
1 row in set (0.00 sec)

Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

Since Bug #109145 is already in verified state hence marking this as a duplicate of Bug #109145(Otherwise, in general newer one are marked as duplicate of older one.).