Bug #118475 When the IF function is invoked to replace the specified column name, the returned results are inconsistent.

• Submitted: 18 Jun 7:58
• Modified: 18 Jun 8:37
• Reporter: Alice Alice
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.41, 8.4.5
• OS: Any
• CPU Architecture: Any

[18 Jun 7:58] Alice Alice

Description:
When the IF function is invoked to replace the specified column name, the returned results are inconsistent.

mysql> SELECT ALL t1.c0 AS ref1, t1.c1 AS ref2, t0.c2 AS ref3 FROM t1, t0 WHERE (NOT ((IF(0.02324252233242134, t1.c1, t1.c0)) NOT IN (t0.c2)));
Empty set (0.00 sec)

mysql> SELECT ALL t1.c0 AS ref1, t1.c1 AS ref2, t0.c2 AS ref3,(NOT ((IF(0.02324252233242134, t1.c1, t1.c0)) NOT IN (t0.c2))) FROM t1, t0 where NOT (t1.c1 NOT IN (t0.c2));
+------+----------+----------+----------------------------------------------------------------+
| ref1 | ref2     | ref3     | (NOT ((IF(0.02324252233242134, t1.c1, t1.c0)) NOT IN (t0.c2))) |
+------+----------+----------+----------------------------------------------------------------+
| NULL | 0.156351 | 0.156351 |                                                              1 |
+------+----------+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

How to repeat:
CREATE TABLE `t1` (
  `c0` varchar(500) DEFAULT NULL,
  `c1` float DEFAULT NULL,
  UNIQUE KEY `i0` (`c1`,`c0`(3)),
  KEY `i1` (`c0`)
) ENGINE=Dstore DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci PACK_KEYS=0 STATS_AUTO_RECALC=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC COMPRESSION='NONE'; 

CREATE TABLE `t0` (
  `c2` float /*!50606 STORAGE DISK */ DEFAULT NULL,
  UNIQUE KEY `c2` (`c2`)
) ENGINE=Dstore DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci PACK_KEYS=0 STATS_AUTO_RECALC=1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=REDUNDANT COMPRESSION='LZ4'; 

INSERT INTO t1 (c0, c1) VALUES (NULL, 0.15635101107120514);

INSERT INTO t0 (c2) VALUES (0.15635101107120514);

mysql> SELECT ALL t1.c0 AS ref1, t1.c1 AS ref2, t0.c2 AS ref3 FROM t1, t0 WHERE (NOT ((IF(0.02324252233242134, t1.c1, t1.c0)) NOT IN (t0.c2)));
Empty set (0.00 sec)

mysql> SELECT ALL t1.c0 AS ref1, t1.c1 AS ref2, t0.c2 AS ref3,(NOT ((IF(0.02324252233242134, t1.c1, t1.c0)) NOT IN (t0.c2))) FROM t1, t0 where NOT (t1.c1 NOT IN (t0.c2));
+------+----------+----------+----------------------------------------------------------------+
| ref1 | ref2     | ref3     | (NOT ((IF(0.02324252233242134, t1.c1, t1.c0)) NOT IN (t0.c2))) |
+------+----------+----------+----------------------------------------------------------------+
| NULL | 0.156351 | 0.156351 |                                                              1 |
+------+----------+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

[18 Jun 8:37] MySQL Verification Team

Hello Alice Alice,

Thank you for the report and feedback.

regards,
Umesh