Description:
The simplest SELECT query returns different results when an index exists or not. This happens when comparing the field of integer type with a division expression.
How to repeat:
CREATE TABLE t1 (
a INT,
b INT,
c INT
);
INSERT INTO t1 VALUES (3, 2, 1), (4, 2, 1), (2, 2, 1);
root@localhost:hope_test 8.0.22-rds-dev-debug> SELECT * FROM t1 WHERE a = 7/2;
Empty set (0.00 sec)
root@localhost:hope_test 8.0.22-rds-dev-debug> EXPLAIN SELECT * FROM t1 WHERE a = 7/2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
The results are different when an index exists as follows:
CREATE INDEX i1 ON t1(a);
ANALYZE TABLE t1;
root@localhost:hope_test 8.0.22-rds-dev-debug> SELECT * FROM t1 WHERE a = 7/2;
+------+------+------+
| a | b | c |
+------+------+------+
| 4 | 2 | 1 |
+------+------+------+
1 row in set (0.00 sec)
root@localhost:hope_test 8.0.22-rds-dev-debug> EXPLAIN SELECT * FROM t1 WHERE a = 7/2;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ref | i1 | i1 | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)