Bug #117128 Query results do not meet expectations when the predicate involves CAST AS FLOAT and >> operator

• Submitted: 8 Jan 5:42
• Modified: 8 Jan 7:37
• Reporter: wang jack
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.4.1, 8.0.40
• OS: Windows (windows 11)
• CPU Architecture: x86

[8 Jan 5:42] wang jack

Description:
From my understanding, the result of the first query below should be empty, but in fact, it returns one row of data.

mysql> SELECT TRUE FROM t0 WHERE ((CAST(0.7006029897167592 AS FLOAT))>>(t0.c2));
+------+
| TRUE |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select ((CAST(0.7006029897167592 AS FLOAT))>>(t0.c2)) from t0; --
+------------------------------------------------+
| ((CAST(0.7006029897167592 AS FLOAT))>>(t0.c2)) |
+------------------------------------------------+
|                                              0 |
+------------------------------------------------+
1 row in set (0.00 sec)

How to repeat:
DROP TABLE IF EXISTS t0;
CREATE TABLE t0( c2 DOUBLE);
INSERT INTO t0(c2) VALUES(0.3554452643359681);

select ((CAST(0.7006029897167592 AS FLOAT))>>(t0.c2)) from t0; -- {0}
SELECT TRUE FROM t0 WHERE ((CAST(0.7006029897167592 AS FLOAT))>>(t0.c2)); -- 1 row

[8 Jan 7:37] MySQL Verification Team

Hello wang jack,

Thank you for the report and test case.

regards,
Umesh

[13 Jan 9:23] Tor Didriksen

Posted by developer:
 
Bug#36481397 	CAST from DOUBLE to INTEGER sometimes returns 1, sometimes 2