Consider the below issue that was identified in Oracle Database 23ai with version 23.7.0.25.01.
Steps to reproduce
CREATE TABLE t0(c1 INT, c2 INT);
INSERT INTO t0(c1) VALUES (1);
SELECT * FROM t0;
-- 1 NULL
SELECT (2 NOT BETWEEN t0.c2 AND 3) IS NULL FROM t0;
-- TRUE
SELECT * FROM t0 WHERE (2 NOT BETWEEN t0.c2 AND 3) IS NULL;
-- Expected: 1 NULL
-- Actual: no rows selected
The third query is expected to return one row, since the expression 2 NOT BETWEEN t0.c2 AND 3 should be evaluated to NULL, and (2 NOT BETWEEN t0.c2 AND 3) IS NULL will be TRUE. So it is expected that the third query should return the row in the table; however, no rows returned.
Kindly inform me if it is an expected behavior in Oracle.
Environment
I use the Oracle Database through https://hub.docker.com/r/gvenzl/oracle-free