5There is no doubt that SQL has made NULL pervasive among database products. However, as Chris Date makes clear through numerous counter-examples, NULL can have no place in the relational algebra. This document summarizes Chris Date's rationale for banishing NULL, provides a rationale for why it cannot appear in a mathematically-coherent relational algebra system, and describes some alternative designs.
9SQL intentionally differentiates NULL from all other values. NULL is not an integer or text or anything else (though it does carry type information). In fact, NULL is literally equal to nothing else.
12postgres=# select NULL = NULL;
19Whether two NULLs are equal is unknown (NULL). Of course, this leads to three-valued logic: a boolean query can true, false, or unknown (NULL).
23The purpose of a database is to model reality in some form, so what does the NULL represent in reality? Consider a nullable integer column; NULL could represent:
25* a unanswered question (How many children does he have? We forgot to ask.)
26* a refused answer (How many children does he have? He refused to divulge this.)
27* not applicable (How many children does the corporation have? Nonsensical.)
28* information to collect in the future (How many children does he have? Ask him tomorrow.)
29* the information can be found elsewhere (How many children does he have? Find the children's names with a join on some other table.)
30* and any other arbitrary meaning
32The problem is that NULL gives no indication which of the above meanings are intended. Indeed, NULL could indicate that one or even more than one of the meanings could hold. The schema's nullable values makes the intention completely ambiguous.
35## The Folly of Three-Valued Logic
37When dealing with NULLs, one inevitably must encounter the three-valued logic implied by the NULL non-value. This logic often comes as a surprise to newcomers as in the following examples:
39Setup: Create an example table with some NULLs.
41postgres=# create table x (a int, b text);
43postgres=# insert into x(a,b) values (1,'one'),(2,'two'),(NULL,'three') returning a,b;
51Example 1: Return all rows where a is not equal to one.
53postgres=# select * from x where a <> 1;
59Example 2: Return all rows where a is equal to itself.
61postgres=# select * from x where a=a;
68Example 3: Return all rows where b is from a list of NULL.
70postgres=# select * from x where b = any(NULL);
75Example 4: Return all rows where the (a,b) tuple are in the set of x's tuples.
77postgres=# select * from x where (a,b) = any(select * from x);
84Example 5: Return the natural join of x to itself.
86postgres=# select * from x natural join x as y;
93Example 6: Return rows in x where the same row appears in x.
95postgres=# select * from x where exists (SELECT * FROM x as y where (a,b) = (y.a,y.b));
104All of the above query examples conform to the SQL standard with regards to NULL handling.
107## Inconsistencies Leading to Errors
109If the difference between NULL handling in ```ANY``` versus ```EXISTS``` didn't surprise you, perhaps some NULL-in-aggregate-function usage will.
111Example 7: Aggregate a sum over a in x where one a value is NULL.
113postgres=# select sum(a) from x;
119Example 8: Aggregate a sum over two NULLs.
121postgres=# select sum(a) from (values (NULL::int),(NULL)) as y(a); --sum of only NULLs is NULL
127Example 9: Count the number of a values in x. Count the number of rows in x.
129postgres=# select count(a) from x;
134postgres=# select count(*) from x;
140Example 10: Aggregate the sum of an empty table.
142postgres=# create table z(a int);
144postgres=# select sum(a) from z; -- sum of empty table is NULL
150Example 11: NULL multipled by zero plus three.
152postgres=# select 0*NULL + 3;
159This logic follows into arrays:
161Example 12: Is NULL equal to any element in the array?
163postgres=# select NULL = ANY(array[1,2,3,NULL]);
170and row-valued expressions:
172Example 13: Is the (NULL,1) tuple equal to NULL? Is it not equal to NULL?
174postgres=# select (NULL,1) is null;
180postgres=# select (NULL,1) is not null;
187Are you certain that your application handles NULL correctly? Would you notice if some rows disappeared due to NULL?
189## Multiple NULL Nonsense
191SQL does not enforce the relational model constraint that rows (tuples) be unique in a table (relation). So what does the following table mean?
193Example 14: Create multiple NULLs in a result set.
195postgres=# select * from (values (34),(50),(NULL),(NULL)) as employee_age(val);
205What could this construct model in reality? In general, what does a row which is completely NULL mean?
209Since NULLs are not valued, they don't have a logical sort order. Luckily, SQL allows one to arbitrarily sort NULLs to come before all real values or after all real values. But... so what? Invariably, the receiver of a result set containing NULLs will require special handling for the NULLs.
213Suppose you are convinced NULLs in SQL are too convoluted to be used safely. Even if all columns in your schema are marked ```NOT NULL```, NULLs can still make an unwanted appearance. We have seen this with some aggregate functions on empty tables, but any usage of ```OUTER JOIN``` could generate NULLs, too.
215## Outer Join Ambiguity
217Example 15: A table with one valued-row and one completely NULL row. Construct NULL-aware left outer join.
219postgres=# create table z(a int,b text);
221postgres=# insert into z(a,b) values (2,'two'),(NULL,NULL) returning a,b;
227postgres=# select x.a as xa,x.b as xb,z.a as za,z.b as zb from x left join z on x.a is not distinct from z.a;
229----+-------+----+-----
236Does the double-NULL which appears twice reference the actual double-NULL row in ```z``` or is it a missing row which is actually missing in ```z```? There is no way to know from this query. In outer joins, NULLs are overloaded with at least two possible meanings.
241Inevitably, when one attacks the notion of NULL is SQL, database developers become defensive:
243* You can't have a database properly model reality without NULLs. Missing information is a fact-of-life.
244* Even if NULLs don't fit the mathematics, they are too valuable to dismiss.
245* Getting rid of NULLs is a purely academic concern. NULLs don't bother me.
247However, the onus should be on those holding these viewpoints to justify the existence of a construct which:
249* adds substantial complexity in the fundamental logic
250* adds ambiguity to query results
251* reduces confidence that queries are logically-sound
252* fails to adequately model the intention of missing information (more on this below)
254Undoubtedly, representing missing or unknown values *is* a useful construct, but unfortunately SQL's NULL fails to adequately represent this notion.
256If the SQL designers had wanted to be mathematically rigorous with regards to NULL, every query would return two tables (result sets): one where the query's predicate certainly holds, and a second where the predicate might hold if NULL is interpreted as unknown- a sort of logical superposition.
258## What was the goal of NULL?
260Clearly, the notion of NULL would not have been introduced if it served no valid purpose. NULL came from a time where all base database types could fit in CPU registers: integers, ASCII bytes (text), floats. Most programming languages includes these data types in some form, so to include them in SQL was a logical step.
262Then, someone must have realized that it can be useful to have a tuple/row record an employee's name, date-of-hire, and salary, even if his age, an otherwise required attribute/column, is unknown. This is certainly a fair need to fulfill, so why not put a placeholder in the place of age, indicating "fill this in later". Some programming languages include the notion of null references which literally reference nothing. (The first relational-model-based DBMS was [System R](http://www.cs.berkeley.edu/~brewer/cs262/SystemR.pdf) written in PL/I and included NULL). So, the inclusion of this concept in SQL seemed logical. However, in those same programming languages, attempting to dereference or use a NULL reference in calculations results in a fatal error, terminating the program. Obviously, we don't want to fail to answer queries with missing/unknown information, because it's not logically a fatal condition. Thus, the three-valued logic was born.
264Unfortunately, the consequences of including three-valued logic were not fully considered. There is no shame in this, as long as we learn from mistakes. Even Codd, the original IBM-based author of the seminal paper describing the relational model, tried to shoehorn in the concept of NULL.
266## What is the alternative to NULL?
268Chris Date devotes several chapters to exactly this question in his "[Database Explorations](http://bookstore.trafford.com/Products/SKU-000177853/Database-Explorations.aspx)". Some of his suggestions are not teneable, but are considered for the purpose of discussion. Date's options for representing missing data, summarized, are:
270* "The Decomposition Approach": normalize/decompose relations horizontally or vertically so that missing data is implied by missing tuples
271* "The Multirelational Approach": a structure which includes multiple relations with differing headers. This approach is mathematically untenable, but listed for dicussion.
272* "An Inheritance Approach": use vaguely object-oriented type hierarchies to build new types which may include a missing value. Note that "missing" is indeed a value in this model.
273* "An Approach Using Relation Valued Attributes": use single- or empty-tupled relation-values to represent present or missing data
275Date, however, fails to generalize the "inheritance approach" which leads to perhaps the easiest solution. [Algebraic data types](https://en.wikipedia.org/wiki/Algebraic_data_type) are essential in functional programming but make only rare appearances in other programming languages. The core of algebraic data types is that types themselves can be composed using either products or sums of types.
277Defining a new type can involve taking the product of two other types, as in:
279data BookType = Book BookTitle ISBN
281where ```BookTitle``` and ```ISBN``` are pre-existing types. Here, the new BookType can be considered a sort of new two-tuple holding two other types.
283Types can also be composed with a summation:
285data HairColorType = HairColor ColorName |
289In this case, ```ColorName``` is a text string describing the color. The values associated with a ```HairColorType``` could be any of the following (as examples):
291* ```HairColor "Brown"```
292* ```HairColor "Blonde"```
295Here, we witness that a type can be composed of multiple other types in a kind of "or" relationship. Indeed, this is precisely our intention when we wish to mark something as unknown! If a record's "age" attribute is unknown, the value of that record's field should model that intention. Furthermore, we can be explicit about why the value is missing:
298data AgeType = PreciseAge NaturalNumberType |
302 ApproximateAge NaturalNumberType NaturalNumberType
304and some examples values:
308* ```ApproximateAge 30 40``` approximately between 30-40 years old
310Already, we can see that this single data type can pack far more useful information than any combination of an integer and NULL.
312### Algebraic Data Types In SQL
314An algebraic data type may be emulated in SQL, albeit in a convoluted fashion and without type safety. Here is the above example translated using SQL enumerations:
317postgres=# create type age_type as enum ('precise_age', 'forgot_to_ask', 'refused_to_disclose', 'not_applicable', 'approximate_age');
320postgres=# create table hospital_patient(name text not null,
321 age age_type not null,
323 approximate_age int[2]);
324postgres=# insert into hospital_patient(name,age,precise_age,approximate_age) values ('Bob','approximate_age',NULL,array[30,40]);
326postgres=# select name,
327case when age = 'precise_age' then
328 'precisely ' || precise_age::text
329when age = 'approximate_age' then
330 'approximately ' || approximate_age[1] || '-' || approximate_age[2]
334from hospital_patient;
336------+---------------------
337 Bob | approximately 30-40
341But all we have really done is add even more NULLs. Retrieving a sensible value is convoluted and not strongly-typed. In addition, the table constraints (not shown) necessary to ensure that, for example, precise_age is NULL if age is "forgot_to_ask" make this scheme even more complicated to implement.
343### Algebraic Data Types In Project:M36
347Project:M36 supports management of new algebraic data types at runtime. These types are associated with transactions and can changed over time whereas primitive Atoms cannot be changed.
350TutorialD (master/main): data Age = PreciseAge Int | ForgotToAsk | RefusedToDisclose | NotApplicable | ApproximateAge Int Int
351TutorialD (master/main): :showexpr relation{tuple{name "Steve", age ApproximateAge 20 30}, tuple{name "Bob", age RefusedToDisclose}, tuple {name "Initech Corp.", age NotApplicable}}
352┌────────────────────┬───────────────┐
353│age::Age │name::Text │
354├────────────────────┼───────────────┤
355│ApproximateAge 20 30│"Steve" │
356│NotApplicable │"Initech Corp."│
357│RefusedToDisclose │"Bob" │
358└────────────────────┴───────────────┘
361Note that the two values which would normally be rendered as NULL in SQL are indeed proper values and can be distinguished. Three-valued logic makes no appearance. The type constructors can also have arguments:
364TutorialD (master/main): data Tree a = Node a (Tree a) (Tree a) | EmptyNode
365TutorialD (master/main): :showexpr relation{tuple{family "Stevenson", familytree Node "Jim" (Node "Cindy" EmptyNode EmptyNode) (Node "Mike" EmptyNode EmptyNode)}}
366┌────────────┬───────────────────────────────────────────────────────────────────────────────┐
367│family::Text│familytree::Tree (a::Text) │
368├────────────┼───────────────────────────────────────────────────────────────────────────────┤
369│"Stevenson" │Node "Jim" (Node "Cindy" EmptyNode EmptyNode) (Node "Mike" EmptyNode EmptyNode)│
370└────────────┴───────────────────────────────────────────────────────────────────────────────┘
373Algebraic data types in SQL are effectively impossible.
377Any Haskell data type can be turned into a value (called an "atom") in Project:M36. The following example illustrates:
380{-# LANGUAGE DeriveAnyClass,DeriveGeneric #-}
383import ProjectM36.Client
384import ProjectM36.Atom
386import ProjectM36.Relation
388import Control.DeepSeq
392import ProjectM36.Tuple
394data AgeType = PreciseAge Int |
398 ApproximateAge Int Int
399 deriving (Eq,Show,Read,Hashable,Binary,Typeable,NFData,Generic)
401instance Atomable AgeType
403failFastMaybe :: (Show a) => Maybe a -> IO ()
404failFastMaybe (Just err) = error (show err)
405failFastMaybe Nothing = return ()
407failFastEither :: (Show a) => Either a b -> IO (b)
408failFastEither (Left err) = error (show err)
409failFastEither (Right val) = return val
411ageAtomType :: AtomType
412ageAtomType = atomTypeForProxy (Proxy :: Proxy AgeType)
416 let bob_relation_attrs = attributesFromList [Attribute "name" stringAtomType,
417 Attribute "age" ageAtomType]
418 relvar_name = "hospital_patient"
419 age_value_in = ApproximateAge 30 40
420 bob_relation_err = mkRelationFromList
422 [[Atom ("Bob"::Text), Atom age_value_in]]
424 connerr <- connectProjectM36 (InProcessConnectionInfo NoPersistence)
425 conn <- failFastEither connerr
426 bob_relation <- failFastEither bob_relation_err
427 --create the relation variable
428 merr <- executeDatabaseContextExpr conn (Assign relvar_name (ExistingRelation bob_relation))
430 --query the relation variable
431 result_err <- executeRelationalExpr conn (RelationVariable relvar_name)
432 result <- failFastEither result_err
433 case singletonTuple result of
434 Nothing -> error "not a singleton relation!"
436 (Atom age_value_out) <- failFastEither $ atomForAttributeName "age" tuple
437 putStrLn (show age_value_in)
438 putStrLn (show age_value_out)
439 case cast age_value_out of
440 Nothing -> error "wrong datatype"
441 Just age_value_out' -> do
442 putStrLn (show (age_value_in == age_value_out'))
446The example demonstrates that the algebraic datatype ```AgeType``` value which goes into the database is an equal value when it is retrieved from the database.
448Since ```AgeType``` is a normal value, but one which can be created using multiple constructors (```PreciseAge x```, ```ForgotToAsk```, etc.), it can be manipulated using standard database functions without any special consideration for which values indicate "missing-ness".
452This document summarizes the inconsistencies and ambiguities when faced with dealing with SQL's NULL. As Date writes, "You can never trust the answers you get from a database with nulls. In my opinion, this state of affairs is a complete showstopper." ([Database In Depth](http://shop.oreilly.com/product/9780596100124.do) p. 55)
454Regardless, modeling the fact that some information may be missing is valuable, as long as the fact is unambiguous and does not violate the relational algebra. This can be accomplished by several means, but the easiest is to make use of algebraic data types. Unfortunately, SQL products do not support algebraic data types.
459Date, C.J. Darwen, Hugh. [Database Explorations: Essays on the Third Manifesto and Related Topics](http://bookstore.trafford.com/Products/SKU-000177853/Database-Explorations.aspx) chapters 23-29
461Date, C.J. [Database in Depth](http://bookstore.trafford.com/Products/SKU-000177853/Database-Explorations.aspx) pages 53-55
keysesc to close
- j k
- move down, up
- enter
- open
- esc
- back, or up a level
- 1 2 3 4
- readme, files, commits, refs
- /
- filter
- y
- copy the clone command
- c
- compare the chosen commit with another (or shift-click both)
- ?
- show or hide this list