Bug #114217 Unexpected Results by Minus Sign
Submitted: 5 Mar 2024 4:48 Modified: 5 Mar 2024 6:59
Reporter: JINSHENG BA Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:8.2.0, 8.3.0, 8.0.36 OS:Any
Assigned to: CPU Architecture:Any

[5 Mar 2024 4:48] JINSHENG BA
Description:
CREATE TABLE t0(c0 FLOAT) ;
INSERT INTO t0(c0) VALUES(0.1);
CREATE INDEX i0 ON t0((CAST(-t0.c0 AS SIGNED)));

SELECT t0.c0 FROM t0; -- {0.1}
SELECT t0.c0 FROM t0 WHERE -t0.c0; -- {0.1}
SELECT t0.c0 FROM t0 WHERE NOT -t0.c0; -- {0.1}

The last two queries have mutually exclusive predicates for WHERE, but return the same results.

How to repeat:
docker run -it -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root mysql:8.2.0

Then execute the above test case.
[5 Mar 2024 6:59] MySQL Verification Team
Hello Jinsheng Ba,

Thank you for the report and test case.
Verified as described.

regards,
Umesh