None and Missing
SuperSQL has two types for “nothing here,” each with just one value:
none, a value meaning that there is no value, andmissing, which marks a field that’s absent from some of the values described by one type.
They answer different questions. A none says the answer is nothing,
while missing says the field isn’t there. You compute with none.
You don’t compute with missing: SuperSQL reports it so you can say
what you want, usually by converting it to none with
ok.
None
A plain none is the result of an expression that produces nothing, e.g.,
- ok of an error,
?.applied tonone,- a type assertion that fails,
- a match with no matching case and no
else, or - a pipe subquery that produces no values.
A none is handled quietly rather than reported:
- it’s dropped from the output of the values operator, so it’s never passed to a downstream operator,
- it disappears from a record expression, an array, a set, or a map,
- aggregate functions skip it,
??replaces it with another value, and?.passes it through a field access.
For example,
# spq
values {a:this, b:ok(10/this)}, ok(10/this) ?? "bad value"
# input
2
0
# expected output
{a:2,b:5}
5
{a:0}
"bad value"
The none of an option type, e.g.,
none::int64, isn’t a plain none. It’s a value of its option type, and
it does not disappear from record fields or from array/set/map values like
a plain none does.
Missing
A missing never comes from an expression. It appears only in data whose
different shapes are described by one type, e.g., the records produced by
the fuse operator:
# spq
fuse
# input
{a:1}
{b:"x"}
# expected output
{a:1::(int64|missing),b:missing::(string|missing)}
{a:missing::(int64|missing),b:"x"::(string|missing)}
Here, the type int64|missing says that a is absent from some of the
values, and each record holds missing for the field it doesn’t have.
Reading a field where it’s absent is an error, just as it is for a field that doesn’t exist at all:
# spq
fuse | values b
# input
{a:1}
{b:"x"}
# expected output
error("no such field b")
"x"::(string|missing)
When the types of a query’s input are known, a field that may be absent is reported before the query runs:
# spq
values {a:1},{b:2} | values a ?? 0
# input
# expected output
field may be missing (consider ok()) at line 1, column 29:
values {a:1},{b:2} | values a ?? 0
~
Note that ?? and ?. handle none, not missing. To handle a field
that may be absent, convert it to none with
ok, then drop the none or replace it with
??:
# spq
values {a:1},{b:2} | values {input:this, ok:a.ok(), with_default:a.ok() ?? 0}
# input
# expected output
{input:{a:1},ok:1,with_default:1}
{input:{b:2},with_default:0}
Why Two Types?
A none from ok() or a failed type assertion is something the query asked
for, so it’s safe to drop it or replace it without comment.
A field that may be absent is different. It usually means the data doesn’t
look the way the query expects, e.g., the field name is misspelled, or only
some of the input has the field. If an absent field quietly became none,
such mistakes would silently drop values from the output.
Keeping the two apart lets SuperSQL drop none quietly while still catching
every read of a field that may not be there. Writing ok() makes the choice
explicit: where the field is absent, treat it as nothing.
This resembles the distinction between null and missing in
SQL++, but in SuperSQL a field that isn’t
there is an error rather than a value (see Errors),
and missing only marks such fields in the types and values of data.
Note
The SUP format spells the missing value
missingso that data with absent fields can be written and read, butmissingisn’t intended for use in queries.