Description:
Creation of an invisible index leads to rows from the result set of a query being under-fetched.
How to repeat:
CREATE TABLE t0(c0 INT UNIQUE);
CREATE INDEX i0 ON t0((-1)) VISIBLE;
INSERT INTO t0(c0) VALUES(0);
-- Creation of i1 leads to rows being under-fetched even though it is invisible
CREATE INDEX i1 ON t0((123)) INVISIBLE;
SELECT t0.c0 FROM t0 WHERE (NOT (NULL OR -1)) >= t0.c0;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
The actual result is:
| c0 |
| ---- |
The expected result should be:
| c0 |
| ---- |
| 0 |
(1) However, when removing the definition of the creation of the index i1, we get the expected result.
CREATE TABLE t0(c0 INT UNIQUE);
CREATE INDEX i0 ON t0((-1)) VISIBLE;
INSERT INTO t0(c0) VALUES(0);
SELECT t0.c0 FROM t0 WHERE (NOT (NULL OR -1)) >= t0.c0;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t0 | NULL | range | c0 | c0 | 5 | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
Noticeably, the removal of the invisible index i1 causes the query plan to differ.
(2) According to the documentation at https://dev.mysql.com/doc/refman/8.4/en/invisible-indexes.html, "MySQL supports invisible indexes; that is, indexes that are not used by the optimizer".
Therefore, it is unexpected that the use of an invisible index can affect the output of the query result.
(3) Moreover, it is mentioned in the documentation that "the use_invisible_indexes flag of the optimizer_switch system variable controls whether the optimizer uses invisible indexes for query execution plan construction".
Here, we explicitly ensured that the optimizer does not use invisible indexes, but the query still does not fetch any rows.
SELECT /*+ SET_VAR(optimizer_switch = 'use_invisible_indexes=off') */ t0.c0 FROM t0 WHERE (NOT (NULL OR -1)) >= t0.c0;
