Bug #116873 When comparing SMALLINT and FLOAT, the type conversion logic is incorrect.
Submitted: 4 Dec 2024 14:29 Modified: 4 Dec 2024 14:53
Reporter: wang jack Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[4 Dec 2024 14:29] wang jack
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
[4 Dec 2024 14:53] MySQL Verification Team
Hi Mr. jack,

Thank you for your bug report.

Actually, without index both queries return 0 results. It is only when you create index that you get those weird and wrong results.

What is even stranger, query never uses an index on a small number of rows. That makes this bug even harder to understand. 

Also, this behaviour is present in 8.0 and all higher and supported versions.

Verified for 8.0 and higher versions.