Description:
Using a functional index based on a constant value and constructing a query with a WHERE clause that references the particular constant value might under-fetch rows.
How to repeat:
CREATE TABLE IF NOT EXISTS t0(c0 INT);
INSERT INTO t0(c0) VALUES(1);
CREATE INDEX i0 ON t0((-123));
CREATE INDEX i1 ON t0(c0 ASC);
(1) The table is as follows:
| c0 |
| ---- |
| 1 |
(2) In the following query, 0 rows are returned (unexpected):
SELECT
t0.c0 AS ref0
FROM t0
WHERE t0.c0 >= (((NOT (-123)) AND NULL));
| ref0 |
| ---- |
This is unexpected because the constant expression `(((NOT (-123)) AND NULL))` evaluates to 0, based on the documentation of the logical operator at https://dev.mysql.com/doc/refman/8.4/en/logical-operators.html#operator_and.
Thus, the first row of the table in (1) should be returned because its c0-column is 1.
The expected result should be:
| ref0 |
| ---- |
| 1 |
(3) When the index i0 or i1 is removed, 1 row is returned (expected):
DROP INDEX i0 ON t0; -- Or DROP INDEX i1 ON t0;
SELECT
t0.c0 AS ref0
FROM t0
WHERE t0.c0 >= (((NOT (-123)) AND NULL));
| ref0 |
| ---- |
| 1 |
According to the documentation (https://dev.mysql.com/doc/refman/8.4/en/mysql-indexes.html), indexes are used during query optimization to "find rows with specific column values quickly".
Thus, both the queries in (2) and (3) are semantically equivalent since the use of indexes should not change the result of the query execution. But, these queries produce different results in this case.
(4) Moreover, the constant expression `-123` in the functional index `i0` appears to have a dependence with the constant expression `-123` that is used in the WHERE clause of the query.
For example, the following query (where `-123` in the WHERE clause is replaced by `-246`) returns one row as expected:
CREATE TABLE IF NOT EXISTS t0(c0 INT);
INSERT INTO t0(c0) VALUES(1);
CREATE INDEX i0 ON t0((-123));
CREATE INDEX i1 ON t0(c0 ASC);
SELECT
t0.c0 AS ref0
FROM t0
WHERE t0.c0 >= (((NOT (-246)) AND NULL));
Likewise, the following query (where `-123` in the index `i0` is replaced by `-246`) returns one row as expected:
CREATE TABLE IF NOT EXISTS t0(c0 INT);
INSERT INTO t0(c0) VALUES(1);
CREATE INDEX i0 ON t0((-246));
CREATE INDEX i1 ON t0(c0 ASC);
SELECT
t0.c0 AS ref0
FROM t0
WHERE t0.c0 >= (((NOT (-123)) AND NULL));
(5) The query plan is as follows:
CREATE TABLE IF NOT EXISTS t0(c0 INT);
INSERT INTO t0(c0) VALUES(1);
CREATE INDEX i0 ON t0((-123));
CREATE INDEX i1 ON t0(c0 ASC);
EXPLAIN SELECT
t0.c0 AS ref0
FROM t0
WHERE t0.c0 >= (((NOT (-123)) AND NULL))
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| 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 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
In this case, based on the message, the documentation (https://dev.mysql.com/doc/refman/8.4/en/explain-output.html) implies that "For a query with a join, there was an empty table or a table with no rows satisfying a unique index condition.".
However, in this query, there is neither an empty table, nor a unique/primary key index condition used.
Furthermore, the documentation (https://dev.mysql.com/doc/refman/8.4/en/explain-output.html#jointype_const) mentioned that the `const` table is used when "you compare all parts of a PRIMARY KEY or UNIQUE index to constant values", which is not the case in this scenario.
Thus, this is an unexpected result.
Furthermore, in MySQL-compatible databases, such as TiDB (8.5), the same query produces the expected result.
| ref0 |
| ---- |
| 1 |
Description: Using a functional index based on a constant value and constructing a query with a WHERE clause that references the particular constant value might under-fetch rows. How to repeat: CREATE TABLE IF NOT EXISTS t0(c0 INT); INSERT INTO t0(c0) VALUES(1); CREATE INDEX i0 ON t0((-123)); CREATE INDEX i1 ON t0(c0 ASC); (1) The table is as follows: | c0 | | ---- | | 1 | (2) In the following query, 0 rows are returned (unexpected): SELECT t0.c0 AS ref0 FROM t0 WHERE t0.c0 >= (((NOT (-123)) AND NULL)); | ref0 | | ---- | This is unexpected because the constant expression `(((NOT (-123)) AND NULL))` evaluates to 0, based on the documentation of the logical operator at https://dev.mysql.com/doc/refman/8.4/en/logical-operators.html#operator_and. Thus, the first row of the table in (1) should be returned because its c0-column is 1. The expected result should be: | ref0 | | ---- | | 1 | (3) When the index i0 or i1 is removed, 1 row is returned (expected): DROP INDEX i0 ON t0; -- Or DROP INDEX i1 ON t0; SELECT t0.c0 AS ref0 FROM t0 WHERE t0.c0 >= (((NOT (-123)) AND NULL)); | ref0 | | ---- | | 1 | According to the documentation (https://dev.mysql.com/doc/refman/8.4/en/mysql-indexes.html), indexes are used during query optimization to "find rows with specific column values quickly". Thus, both the queries in (2) and (3) are semantically equivalent since the use of indexes should not change the result of the query execution. But, these queries produce different results in this case. (4) Moreover, the constant expression `-123` in the functional index `i0` appears to have a dependence with the constant expression `-123` that is used in the WHERE clause of the query. For example, the following query (where `-123` in the WHERE clause is replaced by `-246`) returns one row as expected: CREATE TABLE IF NOT EXISTS t0(c0 INT); INSERT INTO t0(c0) VALUES(1); CREATE INDEX i0 ON t0((-123)); CREATE INDEX i1 ON t0(c0 ASC); SELECT t0.c0 AS ref0 FROM t0 WHERE t0.c0 >= (((NOT (-246)) AND NULL)); Likewise, the following query (where `-123` in the index `i0` is replaced by `-246`) returns one row as expected: CREATE TABLE IF NOT EXISTS t0(c0 INT); INSERT INTO t0(c0) VALUES(1); CREATE INDEX i0 ON t0((-246)); CREATE INDEX i1 ON t0(c0 ASC); SELECT t0.c0 AS ref0 FROM t0 WHERE t0.c0 >= (((NOT (-123)) AND NULL)); (5) The query plan is as follows: CREATE TABLE IF NOT EXISTS t0(c0 INT); INSERT INTO t0(c0) VALUES(1); CREATE INDEX i0 ON t0((-123)); CREATE INDEX i1 ON t0(c0 ASC); EXPLAIN SELECT t0.c0 AS ref0 FROM t0 WHERE t0.c0 >= (((NOT (-123)) AND NULL)) +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+ | 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 | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+ In this case, based on the message, the documentation (https://dev.mysql.com/doc/refman/8.4/en/explain-output.html) implies that "For a query with a join, there was an empty table or a table with no rows satisfying a unique index condition.". However, in this query, there is neither an empty table, nor a unique/primary key index condition used. Furthermore, the documentation (https://dev.mysql.com/doc/refman/8.4/en/explain-output.html#jointype_const) mentioned that the `const` table is used when "you compare all parts of a PRIMARY KEY or UNIQUE index to constant values", which is not the case in this scenario. Thus, this is an unexpected result. Furthermore, in MySQL-compatible databases, such as TiDB (8.5), the same query produces the expected result. | ref0 | | ---- | | 1 |