Bug #110354 SELECT Query choose different index return different results

• Submitted: 14 Mar 2023 7:37
• Modified: 14 Mar 2023 9:48
• Reporter: Qingping Zhu
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.32, 5.7.41
• OS: CentOS
• CPU Architecture: Any

[14 Mar 2023 7:37] Qingping Zhu

Description:
When use 8.0.32 version, SELECT Query choose different index return different results.

mysql> SELECT * FROM tt FORCE INDEX(b_idx) WHERE b NOT BETWEEN '06:59:59.000000' AND NULL;
+---------------------+----------+
| a                   | b        |
+---------------------+----------+
| 0000-00-00 00:00:00 | 00:00:00 |
| 2020-01-01 10:00:00 | 01:59:59 |
| 2020-12-01 10:00:00 | 02:59:59 |
| 2020-11-01 10:00:00 | 03:59:59 |
| 2020-10-01 10:00:00 | 04:59:59 |
| 2020-09-01 10:00:00 | 05:59:59 |
+---------------------+----------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM tt FORCE INDEX(a_b_idx) WHERE b NOT BETWEEN '06:59:59.000000' AND NULL;
+---------------------+----------+
| a                   | b        |
+---------------------+----------+
| 0000-00-00 00:00:00 | 00:00:00 |
| 1970-01-01 08:00:01 | 24:59:59 |
| 2020-01-01 00:00:01 | 12:00:00 |
| 2020-01-01 10:00:00 | 01:59:59 |
| 2020-01-01 10:00:00 | 23:23:59 |
| 2020-02-03 10:00:00 | 10:59:59 |
| 2020-03-02 10:00:00 | 09:09:59 |
| 2020-04-04 10:00:00 | 20:59:59 |
| 2020-05-06 10:00:00 | 08:59:59 |
| 2020-07-01 10:00:00 | 07:59:59 |
| 2020-08-01 10:00:00 | 06:59:59 |
| 2020-09-01 10:00:00 | 05:59:59 |
| 2020-10-01 10:00:00 | 04:59:59 |
| 2020-11-01 10:00:00 | 03:59:59 |
| 2020-12-01 10:00:00 | 02:59:59 |
+---------------------+----------+
15 rows in set (0.00 sec)

How to repeat:
# prepare table and data
CREATE TABLE tt(a timestamp, b time, KEY b_idx(b), KEY a_b_idx(a,b));
INSERT INTO tt VALUES('2020-12-01 10:00:00', '02:59:59.000000');
INSERT INTO tt VALUES('2020-11-01 10:00:00', '03:59:59.000000');
INSERT INTO tt VALUES('2020-10-01 10:00:00', '04:59:59.000000');
INSERT INTO tt VALUES('2020-09-01 10:00:00', '05:59:59.000000');
INSERT INTO tt VALUES('2020-08-01 10:00:00', '06:59:59.000000');
INSERT INTO tt VALUES('2020-07-01 10:00:00', '07:59:59.000000');
INSERT INTO tt VALUES('2020-05-06 10:00:00', '08:59:59.000000');
INSERT INTO tt VALUES('2020-04-04 10:00:00', '20:59:59.000000');
INSERT INTO tt VALUES('2020-03-02 10:00:00', '09:09:59.000000');
INSERT INTO tt VALUES('2020-02-03 10:00:00', '10:59:59.000000');
INSERT INTO tt VALUES('2020-01-01 10:00:00', '23:23:59.000000');
INSERT INTO tt VALUES('2020-01-01 10:00:00', '01:59:59.000000');
INSERT INTO tt VALUES('2020-01-01 00:00:01', '12:00:00.000000');
INSERT INTO tt VALUES('2008-10-02 12:20:00', NULL);
INSERT INTO tt VALUES('1999-09-02 12:20:00', NULL);
INSERT INTO tt VALUES('1998-09-02 12:20:00', NULL);
INSERT INTO tt VALUES('1997-09-02 12:20:00', NULL);
INSERT INTO tt VALUES('1995-09-02 12:20:00', NULL);
INSERT INTO tt VALUES('1992-03-04 12:20:00', NULL);
INSERT INTO tt VALUES('1970-01-01 08:00:01', '24:59:59.000000');
INSERT INTO tt VALUES('0000-00-00 00:00:00', '00:00:00.000000');

# return 6 rows 
SELECT * FROM tt FORCE INDEX(b_idx) WHERE b NOT BETWEEN '06:59:59.000000' AND NULL;
# return 15 rows
SELECT * FROM tt FORCE INDEX(a_b_idx) WHERE b NOT BETWEEN '06:59:59.000000' AND NULL;

[14 Mar 2023 7:38] Qingping Zhu

reproduce this issue

Attachment: NOT_BETWEEN_WRONG_RESULTS.txt (text/plain), 3.72 KiB.

[14 Mar 2023 9:48] MySQL Verification Team

Hello Qingping Zhu,

Thank you for the report and test case.

regards,
Umesh