Bug #116862 After deleting the index, the same query produces inconsistent results compared to before.
Submitted: 4 Dec 2024 5:11 Modified: 4 Dec 2024 6:28
Reporter: wang jack Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:8.4.1, 8.0.40, 8.4.3, 9.1.0 OS:Red Hat (Linux 3.10.0-1160.102.1.el7.x86_64)
Assigned to: CPU Architecture:x86 (x86_64)

[4 Dec 2024 5:11] wang jack
Description:
I create a index on t0 using blow SQL
CREATE INDEX i0 ON t0(c0 ASC) VISIBLE;

And I execute the query:
mysql> SELECT ALL t0.c0 AS ref0 FROM t0 WHERE (NOT ((DEGREES(EXP(100))) < (t0.c0)));
Empty set (0.01 sec)

Then delete the index using blow SQL
ALTER TABLE t0 DROP INDEX i0;

And execute the query again
mysql> SELECT ALL t0.c0 AS ref0 FROM t0 WHERE (NOT ((DEGREES(EXP(100))) < (t0.c0)));
+----------+
| ref0     |
+----------+
| 0.973252 |
+----------+
1 row in set (0.00 sec)

Meanwhile, MySQL did not issue any warning messages.

How to repeat:
Execute blow SQLs:
drop table if exists t0;
CREATE TABLE IF NOT EXISTS t0 (c0 FLOAT);
CREATE INDEX i0 ON t0(c0 ASC) VISIBLE;
INSERT INTO t0(c0) VALUES
    (0.9732518063863245),
    (NULL);

SELECT (NOT ((DEGREES(EXP(100))) < (t0.c0))) from t0;
-- query: Unable to retrieve data
SELECT ALL t0.c0 AS ref0 FROM t0 WHERE (NOT ((DEGREES(EXP(100))) < (t0.c0)));

-- Delete the index
ALTER TABLE t0 DROP INDEX i0;
-- same query: retrieve 1 row data
SELECT ALL t0.c0 AS ref0 FROM t0 WHERE (NOT ((DEGREES(EXP(100))) < (t0.c0)));
[4 Dec 2024 6:28] MySQL Verification Team
Hello wang jack,

Thank you for the report and test case.

regards,
Umesh