Description:
Hello,
Recently we find a case that a query produces incorrect resultset while skip_scan optimization is used (with optimizer hint `SKIP_SCAN(t)`), and correct resultset is produced when skip_scan is not used (with optimizer hint `NO_SKIP_SCAN(t)`), which is tested on MySQL8.0.29 version.
How to repeat:
[session1]
create database if not exists bugtest;
use bugtest;
set @@session.transaction_isolation = 'REPEATABLE-READ';
drop table if exists skip_scan;
CREATE TABLE `skip_scan` ( `id` int NOT NULL AUTO_INCREMENT, `dt` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, `prj_id` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, PRIMARY KEY (`id`), KEY `index_1` (`dt`,`prj_id`) ) ENGINE=InnoDB AUTO_INCREMENT=262201 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
insert into skip_scan (dt, prj_id) values (20220101, 'TEST001'), (20220101, 'TEST002'), (20220101, 'TEST003'), (20220101, 'TEST004');
insert into skip_scan (dt, prj_id) select dt, prj_id from skip_scan;
insert into skip_scan (dt, prj_id) select dt, prj_id from skip_scan;
insert into skip_scan (dt, prj_id) select dt, prj_id from skip_scan;
insert into skip_scan (dt, prj_id) select dt, prj_id from skip_scan;
insert into skip_scan (dt, prj_id) select 20220102, prj_id from skip_scan where dt = 20220101;
insert into skip_scan (dt, prj_id) select 20220103, prj_id from skip_scan where dt = 20220101;
insert into skip_scan (dt, prj_id) select 20220104, prj_id from skip_scan where dt = 20220101;
insert into skip_scan (dt, prj_id) select 20220105, prj_id from skip_scan where dt = 20220101;
insert into skip_scan (dt, prj_id) select 20220106, prj_id from skip_scan where dt = 20220101;
insert into skip_scan (dt, prj_id) select 20220107, prj_id from skip_scan where dt = 20220101;
insert into skip_scan (dt, prj_id) select 20220108, prj_id from skip_scan where dt = 20220101;
insert into skip_scan (dt, prj_id) select 20220109, prj_id from skip_scan where dt = 20220101;
insert into skip_scan (dt, prj_id) select 20220110, prj_id from skip_scan where dt = 20220101;
insert into skip_scan (dt, prj_id) select 20220111, prj_id from skip_scan where dt = 20220101;
insert into skip_scan (dt, prj_id) select 20220112, prj_id from skip_scan where dt = 20220101;
insert into skip_scan (dt, prj_id) select 20220113, prj_id from skip_scan where dt = 20220101;
insert into skip_scan (dt, prj_id) select 20220114, prj_id from skip_scan where dt = 20220101;
insert into skip_scan (dt, prj_id) select 20220115, prj_id from skip_scan where dt = 20220101;
insert into skip_scan (dt, prj_id) select 20220116, prj_id from skip_scan where dt = 20220101;
insert into skip_scan (dt, prj_id) select 20220117, prj_id from skip_scan where dt = 20220101;
insert into skip_scan (dt, prj_id) select 20220118, prj_id from skip_scan where dt = 20220101;
insert into skip_scan (dt, prj_id) select 20220119, prj_id from skip_scan where dt = 20220101;
insert into skip_scan (dt, prj_id) select 20220120, prj_id from skip_scan where dt = 20220101;
drop table if exists t1;
create table t1 like skip_scan;
[session2]
set @@session.transaction_isolation = 'REPEATABLE-READ';
begin; select * from bugtest.t1 limit 1;
[session1]
insert into t1 select * from skip_scan where prj_id <= 'TEST004' and dt <= '20220120';
delete from t1 where dt > '20220101' and dt < '20220119';
mysql> select /*+ NO_SKIP_SCAN(t1) */ max(dt) from t1 where prj_id = 'TEST004';
+----------+
| max(dt) |
+----------+
| 20220120 |
+----------+
1 row in set (0.02 sec)
# Incorrect resultset is produced
mysql> select /*+ SKIP_SCAN(t1) */ max(dt) from t1 where prj_id = 'TEST004';
+----------+
| max(dt) |
+----------+
| 20220101 |
+----------+
1 row in set (0.00 sec)
