Description:
First example:
I believe the result of using the >= should be more than the result of using the =.
However, the reality is not the case.
mysql> SELECT ALL t0.c0 AS ref0 FROM t0 WHERE (t0.c0) >= (LOG10(20)); -- result: empty
Empty set (0.03 sec)
mysql> SELECT ALL t0.c0 AS ref0 FROM t0 WHERE (t0.c0) = (LOG10(20)); -- result: 1
+------+
| ref0 |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
Second Example:
According to my research in the MySQL manual, when comparing SMALLINT and floating-point numbers, both should be converted to floating-point numbers.
However, it appears that for the predicate (t0.c0) = (LOG10(20)), the value LOG10(20) is truncated to an integer for comparison.
mysql> select LOG10(20);
+--------------------+
| LOG10(20) |
+--------------------+
| 1.3010299956639813 |
+--------------------+
1 row in set (0.01 sec)
mysql>
mysql> SELECT ALL t0.c0 AS ref0 FROM t0 WHERE (1) = (LOG10(20));
Empty set (0.01 sec)
mysql> SELECT ALL t0.c0 AS ref0 FROM t0 WHERE (t0.c0) = (1.3010299956639813);
Empty set (0.01 sec)
mysql>
mysql> SELECT ALL t0.c0 AS ref0 FROM t0 WHERE (t0.c0) = (LOG10(20));
+------+
| ref0 |
+------+
| 1 |
+------+
1 row in set (0.03 sec)
How to repeat:
execute blow SQLs:
DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (
c0 SMALLINT,
INDEX i0 USING HASH (c0)
);
INSERT INTO t0(c0) VALUES(1);
SELECT t0.c0 FROM t0;
SELECT ALL t0.c0 AS ref0 FROM t0 WHERE (t0.c0) >= (LOG10(20)); -- result: empty
SELECT ALL t0.c0 AS ref0 FROM t0 WHERE (t0.c0) = (LOG10(20)); -- result: 1
select LOG10(20); -- result: 1.3010299956639813
SELECT ALL t0.c0 AS ref0 FROM t0 WHERE (1) = (LOG10(20)); -- result: empty
SELECT ALL t0.c0 AS ref0 FROM t0 WHERE (t0.c0) = (1.3010299956639813); -- result: empty
SELECT ALL t0.c0 AS ref0 FROM t0 WHERE (t0.c0) = (LOG10(20)); -- result: 1