Bug #102160 Simple SELECT query returns different results when an index exists or not
Submitted: 6 Jan 2021 8:31 Modified: 6 Jan 2021 8:57
Reporter: Hope Lee (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.22, 5.7.32 OS:CentOS
Assigned to: CPU Architecture:Any

[6 Jan 2021 8:31] Hope Lee
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)
[6 Jan 2021 8:57] MySQL Verification Team
Hello Lee,

Thank you for the report and test case.
Observed the reported issue with 5.7.32 and 8.0.22 builds.

regards,
Umesh