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(
  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.

Advertisements

2 thoughts on “Handling Three-Value Logic

    • Fred,

      Thanks for the reference. From my reading so far, it would appear that C.J.Date and others blame the SQL standard for this mess. If RDBMS vendors follow the standard, they can deflect blame for shortcomings back to the standards committee.

      Douglas

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s