MySQL Bugs: #116862: After deleting the index, the same query produces inconsistent results compared to before.

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)

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)));

Hello wang jack,

Thank you for the report and test case.

regards,
Umesh