Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

None and Missing

SuperSQL has two types for “nothing here,” each with just one value:

  • none, a value meaning that there is no value, and
  • missing, 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 to none,
  • 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 missing so that data with absent fields can be written and read, but missing isn’t intended for use in queries.