NULL and Three-Valued Logic

Why this stage exists

Until now every column of every row had a value. A row inserted without its email got an empty string, and one without its age got 0. That is a lie: nobody said the email was empty, or that the person was zero years old. The value is missing, and a database needs a way to say so. SQL's way is NULL.

NULL is not a value like the others. It means unknown, and that one word explains every rule on this page. Start with the query that surprises everyone:

>select id from t where c = NULL;
id
(0 rows)
[EXEC] rows_examined=5

Rows 3 and 4 have NULL in c, and still nothing comes back. Ask the question in words: is this unknown value equal to an unknown value? The honest answer is not yes, and not no. It is unknown. WHERE keeps a row only when its condition is true, so it keeps none. To find the NULLs, ask a question that does have a yes-or-no answer: where c is null.

Three truth values

A comparison now has three possible answers: true, false and unknown. Any comparison with a NULL on either side is unknown. NOT, AND and OR extend to the third value by one rule: the answer is known when it would be the same whatever the unknown turned out to be.

unknown AND false is false: whatever the unknown is, the AND fails. unknown OR true is true for the same reason. This is why where not (c = 1 and id = 99) keeps the NULL rows: for them the inside is unknown AND false, which is false, and NOT makes it true.

Short-circuiting still works, with a smaller window. AND can skip its right side only when the left is false, and OR only when it is true. An unknown left side does not decide anything, so the right side has to be evaluated.

Arithmetic follows the same idea. c + 1 with an unknown c is unknown, so it is NULL. So is c * 0: it is tempting to say "anything times zero is zero", but SQL keeps the rule simple, and NULL in gives NULL out. A NULL operand also means there is nothing to divide: NULL / 0 is NULL, not a division by zero.

The literal NULL fits any type. name = NULL, id = NULL and NULL + 1 pass the type check and evaluate to unknown or NULL. A real type error elsewhere in the WHERE is still 00403. A NULL is never a key bound either: id = NULL stays in the Filter over a SeqScan, since no key equals it.

IS NULL and IS NOT NULL are parsed at the level of the comparisons of the previous stage, one per level: below + and -, which bind tighter, and above NOT. So c + 1 is null asks about c + 1, and not c is null is NOT (c IS NULL). They are not a suffix stuck to the nearest operand: read that way, c + 1 is null would add a truth value to c.

Storing nothing

Since varlen-rows, every row has carried a null bitmap: one bit per column, all zero so far. Now a NULL column sets its bit and writes nothing, not even a length. Here is (1, NULL, 30) in a table t3 (id int, name varchar(20), age int):

The bitmap is why the earlier format reserved it: an empty string is a value with a length of 0, and NULL is no value at all. They have to be told apart in the bytes, and they are.

A column an INSERT leaves out is now NULL. Earlier stages filled it with 0 or an empty string, because there was nothing else to put there; now there is. The first column is the key, and a key that is unknown cannot be found or ordered, so it can never be NULL.

NOT NULL

Some columns must always have a value. create table p (id int, name varchar(20) not null, age int); makes name one of them. Giving it NULL, or leaving it out of an INSERT, is 01503, and so is an UPDATE that sets it to NULL. The check runs after the type checks and before the row is written, so a refused row changes nothing. catalog list shows the constraint in the table's column list: name VARCHAR(20) NOT NULL.

Output contract for this stage

These are the exact lines the tests check. The full definition is on the output contract page, sections 3 and 41.

Layout: lists only the columns that are stored, so a NULL column has no entry. deserialize is 00503 for a row whose bitmap marks the first column, or a column past column_count. IS NULL and IS NOT NULL print with capitals, after their operand, which is in parentheses unless it is a column or a literal.

What you should see when done

$droid --db people.db
Welcome to droid!
>create table p (id int, name varchar(20) not null, age int);
Create table statement executed successfully
>insert into p (id, name, age) values (1, 'ada', 36);
Insert statement executed successfully (1 rows)
>insert into p (id, name) values (2, 'bob');
Insert statement executed successfully (1 rows)
>insert into p (id, age) values (3, 40);
[ERROR:01503] Column name cannot be NULL
>select * from p;
id | name | age
1 | ada | 36
2 | bob | NULL
(2 rows)
[EXEC] rows_examined=2
>select name from p where age > 30;
name
ada
(1 rows)
[EXEC] rows_examined=2
>select name from p where not (age > 30);
name
(0 rows)
[EXEC] rows_examined=2
>select name from p where age is null;
name
bob
(1 rows)
[EXEC] rows_examined=2
>.mode csv
>select * from p;
id,name,age
1,ada,36
2,bob,
[EXEC] rows_examined=2
>.exit
exiting.. good bye!

Bob's age was left out, so it is NULL. He is not older than 30, and he is not not older than 30 either: both queries leave him out. Only is null finds him. The messages are yours to word; the tests check only the codes.

Common bugs in this stage

• NULL as false. Comparisons with NULL return false, so not (c = 1) keeps the NULL rows. Unknown must survive NOT.
• NULL equal to NULL. A comparison that looks at two "missing" markers and calls them equal makes NULL = NULL true. Any comparison with a NULL side is unknown.
• AND short-circuits on unknown. Only false decides an AND. unknown AND false must evaluate the right side to reach false.
• Reading a NULL column's bytes. The decoder reads a length that is not there and every column after it is garbage. Check the bit first.
• The bit order backwards. Column 0 is the least significant bit of the first bitmap byte, and column 8 starts the second byte.
• Writing a length of 0 for NULL. Then NULL and the empty string are the same bytes, and the row size is wrong by two.
• The default still 0. A column left out must be NULL now; a left-over default makes is null find nothing.
• NOT NULL checked after the write. The error is printed but the row is in the tree. Check before.
• CSV printing NULL. CSV mode prints an empty field; only table mode prints the word.

Language notes

The page above is the same for every language. These are the only language-specific hints.

• C and C++: a value struct with an is_null flag next to its type works; so does a separate TYPE_NULL. Truth values fit in a small enum of three. Set a bit with bitmap[i / 8] |= 1u << (i % 8).
• Rust: Option<Value> makes NULL impossible to forget, and an enum Truth { True, False, Unknown } keeps three-valued logic out of bool.
• Zig: an optional (?Value) for a value that may be NULL, and an enum for the three truth values; std.bit_set is there if you want it, though one shift and a mask is all a bitmap byte needs.