MySQL Bugs: #118903: Incorrect results could be returned when using index condition pushdown

Bug #118903	Incorrect results could be returned when using index condition pushdown
Submitted:	28 Aug 2:20
Reporter:	hel le	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any
Description:
1. create table
CREATE TABLE `lpr_camera_status` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID', 
  `camera_id` bigint(20) NOT NULL, 
  `camera_key` varchar(200) NOT NULL, 
  `park_lot_id` bigint(20) NOT NULL, 
  `channel_id` bigint(20) NOT NULL, 
  `channel_type` varchar(64) NOT NULL, 
  `ip_address` varchar(50) DEFAULT NULL, 
  `camera_time` datetime DEFAULT NULL, 
  `is_online` bit(1) NOT NULL, 
  `offline_reason` varchar(64) DEFAULT NULL, 
  `version` int(10) NOT NULL, 
  `create_date_time` datetime NOT NULL, 
  `update_date_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
  `description` varchar(1000) DEFAULT NULL, 
  PRIMARY KEY (`id`), 
  KEY `idx_create_time` (`create_date_time`)
) ENGINE = InnoDB;

2. insert value in table

Use the attachment in the comments section to import the data into the table.

3. exec query statement
SELECT s.id, s.create_date_time,(SELECT s2.create_date_time from lpr_camera_status 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 lpr_camera_status s WHERE s.create_date_time BETWEEN '2024-11-18' and '2024-11-19'and s.camera_id = 100456;

set optimizer_switch='index_condition_pushdown=off';
mysql> explain SELECT s.id, s.create_date_time,(SELECT s2.create_date_time from lpr_camera_status 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 lpr_camera_status s WHERE s.create_date_time BETWEEN '2024-11-18' and '2024-11-19'and s.camera_id = 100456;
+----+--------------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+------------------------------------------------+
| id | select_type        | table | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                                          |
+----+--------------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+------------------------------------------------+
|  1 | PRIMARY            | s     | NULL       | ALL   | idx_create_time | NULL            | NULL    | NULL | 5271 |     5.46 | Using where                                    |
|  2 | DEPENDENT SUBQUERY | s2    | NULL       | range | idx_create_time | idx_create_time | 5       | NULL | 2877 |    10.00 | Range checked for each record (index map: 0x2) |
+----+--------------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+------------------------------------------------+
2 rows in set, 3 warnings (0.00 sec)

+-----------+---------------------+---------------------+
| id        | create_date_time    | last_time           |
+-----------+---------------------+---------------------+
| 329769788 | 2024-11-18 00:00:07 | NULL                |
| 329770121 | 2024-11-18 00:00:37 | 2024-11-18 00:00:07 |
| 329770461 | 2024-11-18 00:01:07 | 2024-11-18 00:00:07 |
| 329770796 | 2024-11-18 00:01:37 | 2024-11-18 00:00:07 |
| 329771130 | 2024-11-18 00:02:07 | 2024-11-18 00:00:07 |
| 329771464 | 2024-11-18 00:02:37 | 2024-11-18 00:00:07 |
| 329771799 | 2024-11-18 00:03:07 | 2024-11-18 00:00:07 |
| 329772136 | 2024-11-18 00:03:37 | 2024-11-18 00:00:07 |
| 329772474 | 2024-11-18 00:04:07 | 2024-11-18 00:00:07 |
| 329772807 | 2024-11-18 00:04:37 | 2024-11-18 00:00:07 |
| 329773139 | 2024-11-18 00:05:07 | 2024-11-18 00:00:07 |
| 329773480 | 2024-11-18 00:05:37 | 2024-11-18 00:00:07 |
| 329773811 | 2024-11-18 00:06:07 | 2024-11-18 00:00:07 |

set optimizer_switch='index_condition_pushdown=on';
mysql> explain SELECT s.id, s.create_date_time,(SELECT s2.create_date_time from lpr_camera_status 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 lpr_camera_status s WHERE s.create_date_time BETWEEN '2024-11-18' and '2024-11-19'and s.camera_id = 100456;
+----+--------------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------------------------------------------------------+
| id | select_type        | table | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                                                                 |
+----+--------------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------------------------------------------------------+
|  1 | PRIMARY            | s     | NULL       | ALL   | idx_create_time | NULL            | NULL    | NULL | 5271 |     5.46 | Using where                                                           |
|  2 | DEPENDENT SUBQUERY | s2    | NULL       | range | idx_create_time | idx_create_time | 5       | NULL | 2877 |    10.00 | Using index condition; Range checked for each record (index map: 0x2) |
+----+--------------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------------------------------------------------------+
2 rows in set, 3 warnings (0.00 sec)

mysql>  SELECT s.id, s.create_date_time,(SELECT s2.create_date_time from lpr_camera_status 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 lpr_camera_status 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           |
+-----------+---------------------+---------------------+
| 329769788 | 2024-11-18 00:00:07 | 2024-11-15 03:41:59 |
| 329770121 | 2024-11-18 00:00:37 | 2024-11-15 03:41:59 |
| 329770461 | 2024-11-18 00:01:07 | 2024-11-15 03:41:59 |
| 329770796 | 2024-11-18 00:01:37 | 2024-11-15 03:41:59 |
| 329771130 | 2024-11-18 00:02:07 | 2024-11-15 03:41:59 |
| 329771464 | 2024-11-18 00:02:37 | 2024-11-15 03:41:59 |
| 329771799 | 2024-11-18 00:03:07 | 2024-11-15 03:41:59 |
| 329772136 | 2024-11-18 00:03:37 | 2024-11-15 03:41:59 |
| 329772474 | 2024-11-18 00:04:07 | 2024-11-15 03:41:59 |
| 329772807 | 2024-11-18 00:04:37 | 2024-11-15 03:41:59 |
| 329773139 | 2024-11-18 00:05:07 | 2024-11-15 03:41:59 |
| 329773480 | 2024-11-18 00:05:37 | 2024-11-15 03:41:59 |
| 329773811 | 2024-11-18 00:06:07 | 2024-11-15 03:41:59 |
| 329774142 | 2024-11-18 00:06:37 | 2024-11-15 03:41:59 |
| 329774482 | 2024-11-18 00:07:07 | 2024-11-15 03:41:59 |
| 329774821 | 2024-11-18 00:07:37 | 2024-11-15 03:41:59 |
| 329775149 | 2024-11-18 00:08:08 | 2024-11-15 03:41:59 |

incorrect results.

How to repeat:
1. create table
CREATE TABLE `lpr_camera_status` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID', 
  `camera_id` bigint(20) NOT NULL, 
  `camera_key` varchar(200) NOT NULL, 
  `park_lot_id` bigint(20) NOT NULL, 
  `channel_id` bigint(20) NOT NULL, 
  `channel_type` varchar(64) NOT NULL, 
  `ip_address` varchar(50) DEFAULT NULL, 
  `camera_time` datetime DEFAULT NULL, 
  `is_online` bit(1) NOT NULL, 
  `offline_reason` varchar(64) DEFAULT NULL, 
  `version` int(10) NOT NULL, 
  `create_date_time` datetime NOT NULL, 
  `update_date_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
  `description` varchar(1000) DEFAULT NULL, 
  PRIMARY KEY (`id`), 
  KEY `idx_create_time` (`create_date_time`)
) ENGINE = InnoDB;

2. insert value in table

Use the attachment in the comments section to import the data into the table.

3. exec query statement
SELECT s.id, s.create_date_time,(SELECT s2.create_date_time from lpr_camera_status 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 lpr_camera_status s WHERE s.create_date_time BETWEEN '2024-11-18' and '2024-11-19'and s.camera_id = 100456;

set optimizer_switch='index_condition_pushdown=off';
mysql> explain SELECT s.id, s.create_date_time,(SELECT s2.create_date_time from lpr_camera_status 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 lpr_camera_status s WHERE s.create_date_time BETWEEN '2024-11-18' and '2024-11-19'and s.camera_id = 100456;
+----+--------------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+------------------------------------------------+
| id | select_type        | table | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                                          |
+----+--------------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+------------------------------------------------+
|  1 | PRIMARY            | s     | NULL       | ALL   | idx_create_time | NULL            | NULL    | NULL | 5271 |     5.46 | Using where                                    |
|  2 | DEPENDENT SUBQUERY | s2    | NULL       | range | idx_create_time | idx_create_time | 5       | NULL | 2877 |    10.00 | Range checked for each record (index map: 0x2) |
+----+--------------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+------------------------------------------------+
2 rows in set, 3 warnings (0.00 sec)

+-----------+---------------------+---------------------+
| id        | create_date_time    | last_time           |
+-----------+---------------------+---------------------+
| 329769788 | 2024-11-18 00:00:07 | NULL                |
| 329770121 | 2024-11-18 00:00:37 | 2024-11-18 00:00:07 |
| 329770461 | 2024-11-18 00:01:07 | 2024-11-18 00:00:07 |
| 329770796 | 2024-11-18 00:01:37 | 2024-11-18 00:00:07 |
| 329771130 | 2024-11-18 00:02:07 | 2024-11-18 00:00:07 |
| 329771464 | 2024-11-18 00:02:37 | 2024-11-18 00:00:07 |
| 329771799 | 2024-11-18 00:03:07 | 2024-11-18 00:00:07 |
| 329772136 | 2024-11-18 00:03:37 | 2024-11-18 00:00:07 |
| 329772474 | 2024-11-18 00:04:07 | 2024-11-18 00:00:07 |
| 329772807 | 2024-11-18 00:04:37 | 2024-11-18 00:00:07 |
| 329773139 | 2024-11-18 00:05:07 | 2024-11-18 00:00:07 |
| 329773480 | 2024-11-18 00:05:37 | 2024-11-18 00:00:07 |
| 329773811 | 2024-11-18 00:06:07 | 2024-11-18 00:00:07 |

set optimizer_switch='index_condition_pushdown=on';
mysql> explain SELECT s.id, s.create_date_time,(SELECT s2.create_date_time from lpr_camera_status 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 lpr_camera_status s WHERE s.create_date_time BETWEEN '2024-11-18' and '2024-11-19'and s.camera_id = 100456;
+----+--------------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------------------------------------------------------+
| id | select_type        | table | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                                                                 |
+----+--------------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------------------------------------------------------+
|  1 | PRIMARY            | s     | NULL       | ALL   | idx_create_time | NULL            | NULL    | NULL | 5271 |     5.46 | Using where                                                           |
|  2 | DEPENDENT SUBQUERY | s2    | NULL       | range | idx_create_time | idx_create_time | 5       | NULL | 2877 |    10.00 | Using index condition; Range checked for each record (index map: 0x2) |
+----+--------------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------------------------------------------------------+
2 rows in set, 3 warnings (0.00 sec)

mysql>  SELECT s.id, s.create_date_time,(SELECT s2.create_date_time from lpr_camera_status 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 lpr_camera_status 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           |
+-----------+---------------------+---------------------+
| 329769788 | 2024-11-18 00:00:07 | 2024-11-15 03:41:59 |
| 329770121 | 2024-11-18 00:00:37 | 2024-11-15 03:41:59 |
| 329770461 | 2024-11-18 00:01:07 | 2024-11-15 03:41:59 |
| 329770796 | 2024-11-18 00:01:37 | 2024-11-15 03:41:59 |
| 329771130 | 2024-11-18 00:02:07 | 2024-11-15 03:41:59 |
| 329771464 | 2024-11-18 00:02:37 | 2024-11-15 03:41:59 |
| 329771799 | 2024-11-18 00:03:07 | 2024-11-15 03:41:59 |
| 329772136 | 2024-11-18 00:03:37 | 2024-11-15 03:41:59 |
| 329772474 | 2024-11-18 00:04:07 | 2024-11-15 03:41:59 |
| 329772807 | 2024-11-18 00:04:37 | 2024-11-15 03:41:59 |
| 329773139 | 2024-11-18 00:05:07 | 2024-11-15 03:41:59 |
| 329773480 | 2024-11-18 00:05:37 | 2024-11-15 03:41:59 |
| 329773811 | 2024-11-18 00:06:07 | 2024-11-15 03:41:59 |
| 329774142 | 2024-11-18 00:06:37 | 2024-11-15 03:41:59 |
| 329774482 | 2024-11-18 00:07:07 | 2024-11-15 03:41:59 |
| 329774821 | 2024-11-18 00:07:37 | 2024-11-15 03:41:59 |
| 329775149 | 2024-11-18 00:08:08 | 2024-11-15 03:41:59 |

incorrect results.