Skip to Main Content

Oracle Database Free

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Potential unexpected result when using BETWEEN operator with NULL values

Suyang ZhongMar 20 2025

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

Comments

Processing

Post Details

Added on Mar 20 2025
2 comments
72 views