Bug #118200 The query result using index is different with not used index

• Submitted: 15 May 1:51
• Modified: 20 May 12:12
• Reporter: Alice Alice
• Status: Duplicate
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.41
• OS: Any
• CPU Architecture: Any

[15 May 1:51] Alice Alice

Description:
mysql> create table t1(a time, index(a))engine=innodb;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t1 values(NULL),('00:40:24'),('01:40:24'),('02:40:24'),('03:40:24'),('04:40:24'),('05:40:24'),('06:40:24'),('07:40:24'),('08:40:24'),('09:40:24'),('10:40:24'),('11:40:24'),('12:40:24'),('13:40:24'),('14:40:24'),('15:40:24'),('16:40:24'),('17:40:24'),('18:40:24'),('19:40:24'),('20:40:24'),('21:40:24'),('22:40:24'),('23:40:24'),('24:00:00');
Query OK, 26 rows affected (0.01 sec)
Records: 26  Duplicates: 0  Warnings: 0

mysql> analyze table t1;
+------------+---------+----------+----------+
| Table      | Op      | Msg_type | Msg_text |
+------------+---------+----------+----------+
| wp_test.t1 | analyze | status   | OK       |
+------------+---------+----------+----------+
1 row in set (0.01 sec)

mysql> SELECT count(*) FROM t1 WHERE a NOT BETWEEN '09:09:59' AND NULL;
+----------+
| count(*) |
+----------+
|        9 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT count(*) FROM t1 ignore index(a) WHERE a NOT BETWEEN '09:09:59' AND NULL;
+----------+
| count(*) |
+----------+
|       25 |
+----------+
1 row in set (0.00 sec)

How to repeat:
create table t1(a time, index(a))engine=innodb;
insert into t1 values(NULL),('00:40:24'),('01:40:24'),('02:40:24'),('03:40:24'),('04:40:24'),('05:40:24'),('06:40:24'),('07:40:24'),('08:40:24'),('09:40:24'),('10:40:24'),('11:40:24'),('12:40:24'),('13:40:24'),('14:40:24'),('15:40:24'),('16:40:24'),('17:40:24'),('18:40:24'),('19:40:24'),('20:40:24'),('21:40:24'),('22:40:24'),('23:40:24'),('24:00:00');
analyze table t1;
SELECT count(*) FROM t1 WHERE a NOT BETWEEN '09:09:59' AND NULL;
SELECT count(*) FROM t1 ignore index(a) WHERE a NOT BETWEEN '09:09:59' AND NULL;

[20 May 12:12] MySQL Verification Team

Hello Alice,

Thank you for the bug report.
Imho this is duplicate of Bug #118092, please see Bug #118092.

Regards,
Ashwini Patil