Bug #113317 Unexpected Result by IF

• Submitted: 4 Dec 2023 5:29
• Modified: 4 Dec 2023 6:07
• Reporter: JINSHENG BA
• Status: Verified
• Category: MySQL Server: DML
• Severity: S1 (Critical)
• Version: 8.0.35, 8.1.0, 8.2.0
• OS: Any
• CPU Architecture: Any

[4 Dec 2023 5:29] JINSHENG BA

Description:
CREATE TABLE t0(c0 INT);
INSERT INTO t0(c0) VALUES(0.1);
CREATE INDEX i0 ON t0(c0);

SELECT NOT (t0.c0 != IF(NULL, NULL, 0.2)) FROM t0; -- {false}
SELECT t0.c0 FROM t0 WHERE NOT (t0.c0 != IF(NULL, NULL, 0.2)); -- {0}

NOT (t0.c0 != IF(NULL, NULL, 0.2)) is evaluated to false for the first query, but the second query still returns non-empty result.

How to repeat:
docker run -it -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root mysql:8.2.0

Then execute the above test case.

[4 Dec 2023 6:07] MySQL Verification Team

Hello Jinsheng Ba,

Thank you for the report and test case.
Verified as described.

regards,
Umesh

[14 Oct 2024 15:31] Omer Barnir

Also see contribution https://bugs.mysql.com/file.php?id=33713&bug_id=113302 submitted to bug#113302