Bug #113762 Unexpected results when using ASIN function with NULLIF

• Submitted: 25 Jan 2024 17:05
• Modified: 26 Jan 2024 7:04
• Reporter: Puneet Kaushik
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.36
• OS: Any
• CPU Architecture: Any

[25 Jan 2024 17:05] Puneet Kaushik

Description:

Consider the following test case. It is unexpected that the last two queries both had cardinality 1, since NULLIF(-1, ASIN(t0.c1)) cannot be true and null at the same time.

How to repeat:
CREATE DATABASE test;
USE test;
CREATE TABLE t0(c0 INT, c1 INTEGER, PRIMARY KEY(c0));
INSERT INTO t0 (c0) VALUES (0);
SELECT t0.c1 FROM t0; -- there is only 1 row in t0, expected

-- The two statements below both fetches the row
-- But the NULLIF predicate cannot be true/null at the same time

SELECT t0.c1 FROM t0 WHERE  NULLIF(-1, ASIN(t0.c1)); -- returned 1 row, NULL

SELECT t0.c1 FROM t0 WHERE  (NULLIF(-1, ASIN(t0.c1)) IS NULL); -- returned 1 row,

[25 Jan 2024 17:05] Puneet Kaushik

Issue is also observed in latest 5.7 also

[26 Jan 2024 7:04] MySQL Verification Team

Hello Puneet,

Thank you for the report and test case.

regards,
Umesh