# Handling Three-Value Logic

I have just finished reading Applied Mathematics for Database Professionals By Lex de Haan and Toon Koppelaars. I was struck by the discussion about nulls in Appendix D, especially about the inconsistent handling of NULLs between constraints and predicates in SQL (see p.344)

Let’s set a test case of where predicate checking allows the row to be inserted and predicate checking hides the row in a query. I am using the Oracle XE database.

The predicate in question is:

$100 \leq \mathrm{BAD\_DATA} \leq 200$

The SQL to create the test case is:

CREATE TABLE bad_constraints(
NUMBER
PRIMARY KEY,
NUMBER
CHECK (
)
);


Let’s insert the test data (of which the second statement will be rejected):

INSERT INTO bad_constraints ( bad_constraints_id, bad_data )
COMMIT;


The third statememt is accepted even though the predicate does not evaluate to TRUE.

Now to see all rows that satisfy the predicate:

SELECT bad_constraints_id, bad_data FROM bad_constraints
WHERE bad_data BETWEEN 100 AND 200;


The result is:

BAD_CONSTRAINTS_ID     BAD_DATA
---------------------- ----------------------
1                      100

1 rows selected


So we only see only one (1) row that satisfies the predicate even though there are two (2) rows that passed the predicate checking during insertion.