Description:
When I compare using CAST AS DOUBLE and CAST AS FLOAT, the results are inconsistent with expectations.
I believe the first query below should return one row of results, but it is actually empty.
mysql> SELECT t0.c0 FROM t0 WHERE (NOT (CAST(t0.c0 AS DOUBLE) <= (CAST(t0.c0 AS FLOAT))));
Empty set (0.01 sec)
mysql> SELECT (NOT (CAST(t0.c0 AS DOUBLE)) <= (CAST(t0.c0 AS FLOAT))) FROM t0;
+---------------------------------------------------------+
| (NOT (CAST(t0.c0 AS DOUBLE)) <= (CAST(t0.c0 AS FLOAT))) |
+---------------------------------------------------------+
| 1 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
However, if I do not use CAST AS DOUBLE on the left side of the <= operator, the result is correct.
mysql> SELECT TRUE FROM t0 WHERE (NOT (t0.c0 <= (CAST(t0.c0 AS FLOAT))));
+------+
| TRUE |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
How to repeat:
DROP TABLE IF EXISTS t0;
CREATE TABLE IF NOT EXISTS t0(c0 DOUBLE) ;
INSERT INTO t0(c0) VALUES(0.48419657580314157);
SELECT (NOT (CAST(t0.c0 AS DOUBLE)) <= (CAST(t0.c0 AS FLOAT))) FROM t0; -- {1}
SELECT t0.c0 FROM t0 WHERE (NOT (CAST(t0.c0 AS DOUBLE) <= (CAST(t0.c0 AS FLOAT)))); -- empty
SELECT TRUE FROM t0 WHERE (NOT (t0.c0 <= (CAST(t0.c0 AS FLOAT)))); -- {1}
Description: When I compare using CAST AS DOUBLE and CAST AS FLOAT, the results are inconsistent with expectations. I believe the first query below should return one row of results, but it is actually empty. mysql> SELECT t0.c0 FROM t0 WHERE (NOT (CAST(t0.c0 AS DOUBLE) <= (CAST(t0.c0 AS FLOAT)))); Empty set (0.01 sec) mysql> SELECT (NOT (CAST(t0.c0 AS DOUBLE)) <= (CAST(t0.c0 AS FLOAT))) FROM t0; +---------------------------------------------------------+ | (NOT (CAST(t0.c0 AS DOUBLE)) <= (CAST(t0.c0 AS FLOAT))) | +---------------------------------------------------------+ | 1 | +---------------------------------------------------------+ 1 row in set (0.00 sec) However, if I do not use CAST AS DOUBLE on the left side of the <= operator, the result is correct. mysql> SELECT TRUE FROM t0 WHERE (NOT (t0.c0 <= (CAST(t0.c0 AS FLOAT)))); +------+ | TRUE | +------+ | 1 | +------+ 1 row in set (0.00 sec) How to repeat: DROP TABLE IF EXISTS t0; CREATE TABLE IF NOT EXISTS t0(c0 DOUBLE) ; INSERT INTO t0(c0) VALUES(0.48419657580314157); SELECT (NOT (CAST(t0.c0 AS DOUBLE)) <= (CAST(t0.c0 AS FLOAT))) FROM t0; -- {1} SELECT t0.c0 FROM t0 WHERE (NOT (CAST(t0.c0 AS DOUBLE) <= (CAST(t0.c0 AS FLOAT)))); -- empty SELECT TRUE FROM t0 WHERE (NOT (t0.c0 <= (CAST(t0.c0 AS FLOAT)))); -- {1}