Bug #118830 After a constant value is replaced by a LEAST function returnning the same value, the sql returns different result;

• Submitted: 14 Aug 2:26
• Modified: 19 Aug 9:09
• Reporter: Alice Alice
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.41, 9.4.0
• OS: Any
• CPU Architecture: Any

[14 Aug 2:26] Alice Alice

Description:
After a constant value is replaced by a LEAST function returnning the same value, the sql returns different result;

How to repeat:
CREATE TABLE `test` (
  `c0` bigint /*!50606 STORAGE DISK */ DEFAULT NULL COMMENT 'asdf',
  KEY `i0` (`c0`)
);

insert into test values(1);

mysql> SELECT ALL test.c0 AS ref0 FROM test WHERE 0.7401335565101936 IN (test.c0);
Empty set (0.00 sec)

mysql> select LEAST(0.8044314491415255, 0.7401335565101936);
+-----------------------------------------------+
| LEAST(0.8044314491415255, 0.7401335565101936) |
+-----------------------------------------------+
|                            0.7401335565101936 |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT ALL test.c0 AS ref0 FROM test WHERE LEAST(0.8044314491415255, 0.7401335565101936) IN (test.c0);
+------+
| ref0 |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Suggested fix:
the result set shall be same for the 2 queries.

[19 Aug 9:09] MySQL Verification Team

Hi,

I will verify this bug so that optimizer team can doublecheck but I think this is not a bug. We are here handling comparation of float and bigint so we can expect unexpected things to happen.

mysql> CREATE TABLE `test` (
    ->   `c0` bigint /*!50606 STORAGE DISK */ DEFAULT NULL COMMENT 'asdf',
    ->   KEY `i0` (`c0`)
    -> );
Query OK, 0 rows affected (0.009 sec)

mysql> 
mysql> insert into test values(1);
Query OK, 1 row affected (0.002 sec)

mysql> SELECT ALL test.c0 AS ref0 FROM test WHERE 0.7401335565101936 IN (test.c0);
Empty set (0.000 sec)

mysql> select LEAST(0.8044314491415255, 0.7401335565101936);
+-----------------------------------------------+
| LEAST(0.8044314491415255, 0.7401335565101936) |
+-----------------------------------------------+
|                            0.7401335565101936 |
+-----------------------------------------------+
1 row in set (0.002 sec)

mysql> SELECT ALL test.c0 AS ref0 FROM test WHERE LEAST(0.8044314491415255, 0.7401335565101936) IN (test.c0);
+------+
| ref0 |
+------+
|    1 |
+------+
1 row in set (0.001 sec)

mysql> select * FROM test WHERE 0.7401335565101936 IN (test.c0);
Empty set (0.000 sec)

mysql> select * from test where  LEAST(0.8044314491415255, 0.7401335565101936) IN (test.c0);
+------+
| c0   |
+------+
|    1 |
+------+
1 row in set (0.001 sec)

mysql> select c0 from test;
+------+
| c0   |
+------+
|    1 |
+------+
1 row in set (0.000 sec)

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 9.4.0     |
+-----------+
1 row in set (0.000 sec)

mysql>