MySQL Bugs: #118904: Incorrect results could be returned when using index condition pushdown and Range checked for each record

Bug #118904	Incorrect results could be returned when using index condition pushdown and Range checked for each record
Submitted:	28 Aug 3:20	Modified:	1 Sep 9:58
Reporter:	hel le	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0.41+	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
CREATE TABLE `t1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID', 
  `camera_id` bigint(20) NOT NULL,
  `create_date_time` datetime NOT NULL, 
  `update_date_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`), 
  KEY `idx_create_time` (`create_date_time`)
);
INSERT INTO `t1`(camera_id, create_date_time, update_date_time) VALUES (100456, '2024-11-15 23:46:45', '2024-11-15 23:46:45'),(100456, '2024-11-15 23:47:15', '2024-11-15 23:47:15'),(100456, '2024-11-15 23:47:45', '2024-11-15 23:47:45'),(100456, '2024-11-15 23:48:15', '2024-11-15 23:48:15'),(100456, '2024-11-15 23:48:45', '2024-11-15 23:48:45'),(100456, '2024-11-15 23:49:15', '2024-11-15 23:49:15'),(100456, '2024-11-15 23:49:45', '2024-11-15 23:49:45'),(100456, '2024-11-15 23:50:15', '2024-11-15 23:50:15'),(100456, '2024-11-15 23:50:45', '2024-11-15 23:50:45');
INSERT INTO `t1`(camera_id, create_date_time, update_date_time) VALUES (100456, '2024-11-18 00:39:39', '2024-11-18 00:39:39'),(100456, '2024-11-18 00:40:09', '2024-11-18 00:40:09'),(100456, '2024-11-18 00:40:39', '2024-11-18 00:40:39'),(100456, '2024-11-18 00:41:10', '2024-11-18 00:41:10'),(100456, '2024-11-18 00:41:40', '2024-11-18 00:41:40'),(100456, '2024-11-18 00:42:10', '2024-11-18 00:42:10'),(100456, '2024-11-18 00:42:40', '2024-11-18 00:42:40'),(100456, '2024-11-18 00:43:10', '2024-11-18 00:43:10'),(100456, '2024-11-18 00:43:40', '2024-11-18 00:43:40');
insert into t1(camera_id, create_date_time, update_date_time) select camera_id, create_date_time, update_date_time from t1;
insert into t1(camera_id, create_date_time, update_date_time) select camera_id, create_date_time, update_date_time from t1;
insert into t1(camera_id, create_date_time, update_date_time) select camera_id, create_date_time, update_date_time from t1;
insert into t1(camera_id, create_date_time, update_date_time) select camera_id, create_date_time, update_date_time from t1;

set optimizer_switch='index_condition_pushdown=on';
SELECT s.id, s.create_date_time,(SELECT s2.create_date_time from t1 s2 WHERE s2.create_date_time BETWEEN '2024-11-18' and '2024-11-19' and s2.camera_id = s.camera_id and s2.create_date_time < s.create_date_time order by s2.create_date_time desc LIMIT 1) last_time from t1 s WHERE s.create_date_time BETWEEN '2024-11-18' and '2024-11-19'and s.camera_id = 100456;

How to repeat:
CREATE TABLE `t1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID', 
  `camera_id` bigint(20) NOT NULL,
  `create_date_time` datetime NOT NULL, 
  `update_date_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`), 
  KEY `idx_create_time` (`create_date_time`)
);
INSERT INTO `t1`(camera_id, create_date_time, update_date_time) VALUES (100456, '2024-11-15 23:46:45', '2024-11-15 23:46:45'),(100456, '2024-11-15 23:47:15', '2024-11-15 23:47:15'),(100456, '2024-11-15 23:47:45', '2024-11-15 23:47:45'),(100456, '2024-11-15 23:48:15', '2024-11-15 23:48:15'),(100456, '2024-11-15 23:48:45', '2024-11-15 23:48:45'),(100456, '2024-11-15 23:49:15', '2024-11-15 23:49:15'),(100456, '2024-11-15 23:49:45', '2024-11-15 23:49:45'),(100456, '2024-11-15 23:50:15', '2024-11-15 23:50:15'),(100456, '2024-11-15 23:50:45', '2024-11-15 23:50:45');
INSERT INTO `t1`(camera_id, create_date_time, update_date_time) VALUES (100456, '2024-11-18 00:39:39', '2024-11-18 00:39:39'),(100456, '2024-11-18 00:40:09', '2024-11-18 00:40:09'),(100456, '2024-11-18 00:40:39', '2024-11-18 00:40:39'),(100456, '2024-11-18 00:41:10', '2024-11-18 00:41:10'),(100456, '2024-11-18 00:41:40', '2024-11-18 00:41:40'),(100456, '2024-11-18 00:42:10', '2024-11-18 00:42:10'),(100456, '2024-11-18 00:42:40', '2024-11-18 00:42:40'),(100456, '2024-11-18 00:43:10', '2024-11-18 00:43:10'),(100456, '2024-11-18 00:43:40', '2024-11-18 00:43:40');
insert into t1(camera_id, create_date_time, update_date_time) select camera_id, create_date_time, update_date_time from t1;
insert into t1(camera_id, create_date_time, update_date_time) select camera_id, create_date_time, update_date_time from t1;
insert into t1(camera_id, create_date_time, update_date_time) select camera_id, create_date_time, update_date_time from t1;
insert into t1(camera_id, create_date_time, update_date_time) select camera_id, create_date_time, update_date_time from t1;

set optimizer_switch='index_condition_pushdown=on';
SELECT s.id, s.create_date_time,(SELECT s2.create_date_time from t1 s2 WHERE s2.create_date_time BETWEEN '2024-11-18' and '2024-11-19' and s2.camera_id = s.camera_id and s2.create_date_time < s.create_date_time order by s2.create_date_time desc LIMIT 1) last_time from t1 s WHERE s.create_date_time BETWEEN '2024-11-18' and '2024-11-19'and s.camera_id = 100456;

mysql> SELECT s.id, s.create_date_time,(SELECT s2.create_date_time from t1 s2 WHERE s2.create_date_time BETWEEN '2024-11-18' and '2024-11-19' and s2.camera_id = s.camera_id and s2.create_date_time < s.create_date_time order by s2.create_date_time desc LIMIT 1) last_time from t1 s WHERE s.create_date_time BETWEEN '2024-11-18' and '2024-11-19'and s.camera_id = 100456;
+-----+---------------------+---------------------+
| id  | create_date_time    | last_time           |
+-----+---------------------+---------------------+
|  10 | 2024-11-18 00:39:39 | 2024-11-15 23:46:45 |
|  11 | 2024-11-18 00:40:09 | 2024-11-15 23:46:45 |
|  12 | 2024-11-18 00:40:39 | 2024-11-15 23:46:45 |
|  13 | 2024-11-18 00:41:10 | 2024-11-15 23:46:45 |

last_time should between 2024-11-18 and 2024-11-19.

set optimizer_switch='index_condition_pushdown=off';
SELECT s.id, s.create_date_time,(SELECT s2.create_date_time from t1 s2 WHERE s2.create_date_time BETWEEN '2024-11-18' and '2024-11-19' and s2.camera_id = s.camera_id and s2.create_date_time < s.create_date_time order by s2.create_date_time desc LIMIT 1) last_time from t1 s WHERE s.create_date_time BETWEEN '2024-11-18' and '2024-11-19'and s.camera_id = 100456;

this result is correct.

Thank you for the report.