Bug #119822 MySQL and TiDB return different results

• Submitted: 31 Jan 17:35
• Modified: 31 Jan 22:04
• Reporter: Chunling Qin
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.45
• OS: Any
• CPU Architecture: Any

[31 Jan 17:35] Chunling Qin

Description:
Result is not correct on mysql:

mysql>  SELECT t0.c0 AS ref0 FROM t0 WHERE (IFNULL(0.07589082342313802, NULL)) IN (t0.c0) ;
+------+
| ref0 |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mysql> 
mysql> select version();
+--------------+
| version()    |
+--------------+
| 8.0.45-debug |
+--------------+
1 row in set (0.00 sec)

Result from tidb:

mysql>  SELECT t0.c0 AS ref0 FROM t0 WHERE (IFNULL(0.07589082342313802, NULL)) IN (t0.c0) ;
Empty set (0.00 sec)

How to repeat:
drop table t0;
CREATE TABLE IF NOT EXISTS t0(c0 MEDIUMINT  COLUMN_FORMAT DYNAMIC COMMENT 'asdf'  STORAGE MEMORY) ;
CREATE INDEX i36 ON t0(c0 ASC);
REPLACE DELAYED INTO t0(c0) VALUES(0.16515509618238133), (NULL), (NULL);
 SELECT ALL t0.c0 AS ref0 FROM t0;
 SELECT t0.c0 AS ref0 FROM t0 WHERE (IFNULL(0.07589082342313802, NULL)) IN (t0.c0) ;

[31 Jan 22:04] Roy Lyseng

Thank you for the bug report.
Verified as described.