4-valued logic (TRUE, FALSE, NULL, MISSING), UNKNOWN is not a data value
truth table:
A | B | A AND B | A OR B |
FALSE | FALSE | FALSE | FALSE |
TRUE | TRUE | TRUE | TRUE |
FALSE | TRUE | FALSE | TRUE |
NULL | FALSE | FALSE | NULL |
TRUE | NULL | NULL | TRUE |
NULL | NULL | NULL | NULL |
FALSE | MISSING | FALSE | MISSING |
TRUE | MISSING | MISSING | TRUE |
NULL | MISSING | MISSING | NULL |
MISSING | MISSING | MISSING | MISSING |
A | NOT A |
FALSE | TRUE |
TRUE | FALSE |
NULL | NULL |
MISSING | MISSING |
Comparing NULL and MISSING
Operator | Non-NULL value | NULL | MISSING |
IS NULL | FALSE | TRUE | MISSING |
IS NOT NULL | TRUE | FALSE | MISSING |
IS MISSING | FALSE | FALSE | TRUE |
IS NOT MISSING | TRUE | TRUE | FALSE |
IS UNKNOWN | FALSE | TRUE | TRUE |
IS NOT UNKNOWN | TRUE | FALSE | FALSE |
Arrays are a separate “storage” data type
MISSING gets converted to NULL on array construction
arrays have a length
indices from 0 to length -1
access to out-of-bounds indices result in MISSING
Ordering
ORDER BY | > | |
NULL > MISSING | TRUE | MISSING |
MISSING < 25 | TRUE | MISSING |
NULL > 25 | FALSE | NULL |
99 < ‘25’ | impl dependent total order | NULL |
“hello” < [] | impl dependent total order | NULL |
99 < [99] | impl dependent total order | NULL |
[] < {} | impl dependent total order | NULL |
explicit casting required to cross type hierarchies
only truth values have truth values (e.g., 1 is not true, “foo” is not true, …)
aggregation functions ignore MISSING and NULL