Bug #114384 Incorrect query result may caused by LOG

• Submitted: 17 Mar 2024 14:33
• Modified: 18 Mar 2024 6:52
• Reporter: Ye Shiyang
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.15,8.0.35, 8.0.36
• OS: Any
• CPU Architecture: Any

[17 Mar 2024 14:33] Ye Shiyang

Description:
Dear devps, Our fuzzer may found a bug.

Consider the following statements, qeury1 and query2 should reutrn the same reuslt. However, qeury1 return 0 while query2 return nothing.

-- query1
mysql> SELECT t1.c0 FROM t1 WHERE t1.c0 IN (LOG(0.6261534882548163));
+------+
| c0   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

--query2
mysql> SELECT c0 FROM (SELECT t1.c0, (t1.c0 IN (LOG(0.6261534882548163))) IS TRUE AS flag FROM t1) as t WHERE flag=1;
Empty set (0.00 sec)

How to repeat:
The bug can be reproduced from 8.0.15

CREATE TABLE IF NOT EXISTS t1(c0 SMALLINT) ;
INSERT INTO t1(c0) VALUES(0.49);
CREATE INDEX i0 ON t1(c0);

SELECT t1.c0 FROM t1 WHERE t1.c0 IN (LOG(0.6261534882548163));

SELECT c0 FROM (SELECT t1.c0, (t1.c0 IN (LOG(0.6261534882548163))) IS TRUE AS flag FROM t1) as t WHERE flag=1;

[18 Mar 2024 6:52] MySQL Verification Team

Hello Ye Shiyang,

Thank you for the report and feedback

regards,
Umesh