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:
The SQL to create the test case is:
CREATE TABLE bad_constraints( bad_constraints_id NUMBER CONSTRAINT bad_constraints_pk PRIMARY KEY, bad_data NUMBER CONSTRAINT bad_constraints_bad_data_ck CHECK ( bad_data BETWEEN 100 AND 200 ) ); CREATE SEQUENCE bad_constraints_id_seq;
Let’s insert the test data (of which the second statement will be rejected):
INSERT INTO bad_constraints ( bad_constraints_id, bad_data ) VALUES( bad_constraints_id_seq.nextval, 100 ); INSERT INTO bad_constraints ( bad_constraints_id, bad_data ) VALUES( bad_constraints_id_seq.nextval, -1 ); INSERT INTO bad_constraints ( bad_constraints_id, bad_data ) VALUES( bad_constraints_id_seq.nextval, NULL ); 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.