Unexpected results when using ASIN function with NULLIF
General
Escalation
General
Escalation
Description
Environment
Docker image acquired from percona/percona-server:latest
sha256:b76c455d3db1ae297449753b1054547b7910b3fd2ed4fd8c761cc1a6e202095a
Activity
Show:
Details
Details
Assignee
Unassigned
UnassignedReporter
Albert ZhangFound by Automation
Yes
Upstream Bug URL
Needs QA
Yes
Affects versions
Priority
HighSmart Checklist
Open Smart Checklist
Smart Checklist
Open Smart Checklist
Created January 25, 2024 at 10:04 AM
Updated January 25, 2024 at 5:07 PM
Consider the following test case. It is unexpected that the last two queries both had cardinality 1, since
NULLIF(-1, ASIN(t0.c1))cannot betrueandnullat the same time.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, NULL