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;
SELECT (2 NOT BETWEEN t0.c2 AND 3) IS NULL FROM t0;
SELECT * FROM t0 WHERE (2 NOT BETWEEN t0.c2 AND 3) IS NULL;
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