| Bug #109124 | skip scan retrieves incorrect result after delete | ||
|---|---|---|---|
| Submitted: | 17 Nov 2022 14:58 | Modified: | 18 Nov 2022 18:26 |
| Reporter: | Fan Wang | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 8.0 | OS: | Linux (Redhat, CentOS, Kylin) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | skip scan | ||
[17 Nov 2022 15:03]
Fan Wang
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)
[17 Nov 2022 16:25]
MySQL Verification Team
Hi Mr. Wang, Thank you, very much, for your bug report. We see that we have a complete test case, but we fail to see what is skip_scan_t there for, since this could all be accomplished with a single table. We are waiting on your feedback.
[17 Nov 2022 16:30]
Fan Wang
This table(skip_scan_t) is just to structure the data.
[17 Nov 2022 16:48]
Fan Wang
This table(skip_scan_t) and file(skip_scan.sql) is just to structure the data. And we found the bug in all of these systems: Redhat, CentOS and Kylin. 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) mysql> select max(dt) from skip_scan where prj_id = 'TEST007'; +----------+ | max(dt) | +----------+ | 20220229 | +----------+ 1 row in set (0.01 sec)
[17 Nov 2022 16:57]
MySQL Verification Team
HI Mr. Wang, We followed your test case up to the last comma on 8.0.31. We can't repeat it. Here are the most important commands with their results: analyze table skip_scan; Table Op Msg_type Msg_text test.skip_scan analyze status OK select max(dt) from skip_scan where prj_id = 'TEST007'; max(dt) 20220229 delete from skip_scan where dt like '2022011%'; select max(dt) from skip_scan where prj_id = 'TEST007'; max(dt) 20220109 select max(dt) from skip_scan where prj_id = 'TEST007'; max(dt) 20220109 We used a binary from our download site ........ Can't repeat.
[17 Nov 2022 17:16]
Fan Wang
But you have repeated successfully, the correct result of max(dt) is 20220229. In my environment the issue resumes after a few seconds, with successive queries giving different results. You can execute these two commands consecutively： select max(dt) from skip_scan where prj_id = 'TEST007'; select /*+ NO_SKIP_SCAN(skip_scan) */ max(dt) from skip_scan where prj_id = 'TEST007';
[17 Nov 2022 17:20]
Fan Wang
Alternatively, you can execute these two commands consecutively: select max(dt) from skip_scan where prj_id = 'TEST007'; select max(dt) from skip_scan where prj_id = 'TEST007' and dt = '20220229';
[17 Nov 2022 17:55]
Fan Wang
https://bugs.mysql.com/bug.php?id=109127
[18 Nov 2022 13:57]
MySQL Verification Team
Hi Mr. Wang, You have sent us a full test case, which we can't reproduce. If you think your test case should be different, then send us here, in this bug, a new fully reproducible test case. We ran your test case with SKIP_SCAN and still got same results. Also, please explain what results are we supposed to get with those two commands: select max(dt) from skip_scan where prj_id = 'TEST007'; select max(dt) from skip_scan where prj_id = 'TEST007' and dt = '20220229'; Also, please explain what is wrong with those results. Can't repeat.
[18 Nov 2022 14:06]
Fan Wang
[17 Nov 16:57] MySQL Verification Team HI Mr. Wang, We followed your test case up to the last comma on 8.0.31. We can't repeat it. Here are the most important commands with their results: analyze table skip_scan; Table Op Msg_type Msg_text test.skip_scan analyze status OK select max(dt) from skip_scan where prj_id = 'TEST007'; max(dt) 20220229 delete from skip_scan where dt like '2022011%'; select max(dt) from skip_scan where prj_id = 'TEST007'; max(dt) 20220109 select max(dt) from skip_scan where prj_id = 'TEST007'; max(dt) 20220109 We used a binary from our download site ........ Can't repeat. ______________________________________________________________ The correct result of select max(dt) from skip_scan where prj_id = 'TEST007'; is 20220229！！！！！！！！！！！！！！ But your result is 20220109
[18 Nov 2022 16:51]
Fan Wang
skip_scan.sql
Attachment: skip_scan.sql (application/sql, text), 6.80 KiB.
[18 Nov 2022 16:53]
Fan Wang
How to repeat:
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.31 |
+-----------+
1 row in set (0.00 sec)
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)
# 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)
[18 Nov 2022 18:07]
MySQL Verification Team
Hi, Let us explain to you why is this bug in the "Can't repeat" state. You have got the following results: ------------------------------------------------------ select max(dt) from skip_scan where prj_id = 'TEST007'; +----------+ | max(dt) | +----------+ | 20220109 | +----------+ 1 row in set (0.00 sec) select max(dt) from skip_scan where prj_id = 'TEST007'; +----------+ | max(dt) | +----------+ | 20220229 | +----------+ 1 row in set (0.01 sec) ------------------------------------------------------ Which means, that two identical and consecutive queries returned different number of rows. We have got the following results: ------------------------------------------------------ select max(dt) from skip_scan where prj_id = 'TEST007'; max(dt) 20220109 select max(dt) from skip_scan where prj_id = 'TEST007'; max(dt) 20220109 ------------------------------------------------------ Which means that two identical and consecutive queries return the same number of rows. Hence, we can't repeat your results. You replied that we repeated the results, while we obviously have not repeated, as clearly depicted from the results of the two identical consecutive queries ..... Then we asked for full explanation of what is the problem that you are trying to report. We never got reply from you on our questions.
[18 Nov 2022 18:22]
Fan Wang
Do you think this result is correct? This result is wrong! The correct result is 20220229.My system returned the correct result after a few seconds, yours didn't. You have repeated my problem —— the incorrect result 20220109. The correct result is 20220229. mysql> delete from skip_scan where dt like '2022011%'; Query OK, 170400 rows affected (1.38 sec) The largest date in the table is 20220229 after delete. But you got 20220109.
[18 Nov 2022 18:26]
Fan Wang
This means, the query returned incorrect result. The point is that the result is wrong. Whether it is a single query that gets incorrect result, or consecutive queries that get incorrect results.
[22 Nov 2022 6:44]
MySQL Verification Team
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.).
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> 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'; +----------+ | max(dt) | +----------+ | 20220229 | +----------+ 1 row in set (0.01 sec) How to repeat: mysql> source skip_scan.sql 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'; +----------+ | max(dt) | +----------+ | 20220229 | +----------+ 1 row in set (0.01 sec) -- The skip_scan.sql file: 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.