Bug #117091 Using two mutually exclusive predicates as WHERE conditions returned the same result.

• Submitted: 1 Jan 15:26
• Modified: 2 Jan 7:36
• Reporter: wang jack
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.4.3, 8.0.40
• OS: Windows (windows 11)
• CPU Architecture: x86 (x86_64)

[1 Jan 15:26] wang jack

Description:
Here are two queries where the predicates in the WHERE conditions are mutually exclusive. 
I believe that at least one of the queries should return a result. 
However, in reality, neither query returned any result.

mysql> select TRUE from t0 where (CAST(t0.c0 AS FLOAT)) != (CAST(t0.c0 AS REAL));
Empty set (0.00 sec)

mysql> select TRUE from t0 where (CAST(t0.c0 AS FLOAT)) = (CAST(t0.c0 AS REAL));
Empty set (0.00 sec)

How to repeat:
DROP TABLE IF EXISTS t0;
CREATE TABLE t0(c0 VARCHAR(50));
INSERT INTO t0 VALUES(-1273449157);

select TRUE from t0 where (CAST(t0.c0 AS FLOAT)) != (CAST(t0.c0 AS REAL));
select TRUE from t0 where (CAST(t0.c0 AS FLOAT)) = (CAST(t0.c0 AS REAL));

[2 Jan 7:36] MySQL Verification Team

Hello wang jack,

Thank you for the report and test case.

regards,
Umesh