Expressions

An expression is anything that answers with a value: a literal, a field, a comparison, a call, an arithmetic sum, a choice between two other expressions. They compose, and they compose in the same way everywhere — in a projection, in a WHERE, in a LET, in a RETURN.

RETURN IF 1 < 2 THEN 'yes' ELSE 'no' END;

'yes'

Choosing

IF … THEN … ELSE … END is an expression, not a statement. It answers with a value, so it goes wherever a value goes — including the middle of a projection:

The table the projections below read. The third record has no age at all, which is what the later sections on this page turn on:

DEFINE TABLE people (name string, age int);
CREATE people = { name: 'ada', age: 17 };
CREATE people = { name: 'grace', age: 45 };
CREATE people = { name: 'nobody' };

SELECT
  name,
  IF age >= 18 THEN 'adult' ELSE 'minor' END AS band
FROM people;

1: { band: 'minor', name: 'ada' }
2: { band: 'adult', name: 'grace' }
3: { band: 'minor', name: 'nobody' }
(3 record(s), via scan)

The ELSE may be left off, and then the unmatched case answers with NONE:

RETURN IF 1 > 2 THEN 'a' END;

NONE

More than two cases chain with ELSE IF, and the chain closes with a single END:

RETURN IF 1 > 2 THEN 'a' ELSE IF 1 > 0 THEN 'b' ELSE 'c' END;

'b'

That is one END for the whole chain, not one per IF. There is no ELSEIF spelled as one word — it is refused, and the refusal says so.

A condition is a boolean, and nothing is quietly true

RETURN IF 'x' THEN 'a' ELSE 'b' END;

Refused — ConditionNotBoolean.

error: a condition must be a boolean, not string (at 10..13)

A non-empty string is not true here, a non-zero number is not true here, and 1 AND 2 is refused for the same reason. Languages that accept those have to teach a table of which values are falsy, and the table is always surprising in at least one row — an empty array, '0', -1, a zero-valued date.

The exception is deliberate and is not truthiness:

RETURN IF NONE THEN 'a' ELSE 'b' END;   -- 'b'
RETURN IF NULL THEN 'a' ELSE 'b' END;   -- 'b'

Absence takes the ELSE. A field that is missing, or present and empty, is a condition the store can decide — it is not true — whereas a string in the position of a condition is a mistake about what the expression is for. One is answered, the other is refused.

ELSE is everything else, including what could not be decided

This is the sharpest edge on the page, and it follows from the rule above rather than from anything about IF.

RETURN NONE >= 18;   -- false
RETURN NONE < 18;    -- false

Both false. An ordered comparison against an absent value does not answer true on either side, because there is no value there to be on a side of. So:

SELECT name, IF age >= 18 THEN 'adult' ELSE 'minor' END AS band FROM people;

banded the record with no age at all as 'minor'. Nothing failed; the answer is simply wrong about somebody whose age nobody recorded. ELSE caught a case it was never meant to describe.

Say the third case when there is one:

SELECT
  name,
  IF age = NONE THEN 'unknown' ELSE IF age >= 18 THEN 'adult' ELSE 'minor' END AS band
FROM people;

1: { band: 'minor', name: 'ada' }
2: { band: 'adult', name: 'grace' }
3: { band: 'unknown', name: 'nobody' }

Reach for that whenever the field is one a record is allowed to be missing. Two branches over three states is the shape of the bug.

Falling back with ??

?? answers with its left side unless that side is absent:

RETURN NONE ?? 'fallback';   -- 'fallback'
RETURN NULL ?? 'fallback';   -- 'fallback'
RETURN 0    ?? 'fallback';   -- 0

0 survives, and so do false and ''. ?? asks whether there is a value, never whether the value is interesting — which is exactly why it is safe to write over a column of counts.

It chains, left to right, until something is there:

RETURN NONE ?? NULL ?? 'last';   -- 'last'

A default supplied on the way out of a read is often the whole fix:

SELECT name, age ?? 0 AS age FROM people;

Arithmetic

+ - * / are numeric, and they say so rather than guessing:

RETURN 'a' + 'b';

Refused — NotArithmetic.

error: `+` needs numbers, not string and string (at 7..16)

+ never joins two strings. The operator that adds and the operator that concatenates look identical in most languages and differ in exactly the case where a number arrived as text — 1 + '1' giving '11' is a bug that ships. Joining text is a function, string::concat('a', 'b'), and it is spelled differently because it is a different thing.

Division answers exactly rather than truncating:

RETURN 3 / 2;

dec 1.50

* / + group the way school arithmetic groups, and parentheses override:

RETURN 1 + 2 * 3;     -- 7
RETURN (1 + 2) * 3;   -- 9

Comparisons do not chain

RETURN 5 > 3 = true;

Refused — UnexpectedToken.

error: expected `AND`, `OR` or the end of the condition at 15..19, found `TRUE`

a < b < c reads like mathematics and means something else in every language that allows it — usually (a < b) < c, comparing a boolean to a number. It is refused here instead. Write the AND.

AND, OR and NOT take booleans on both sides, AND binds tighter than OR, and both are covered with the rest of filtering in Conditions and reads.

Membership

RETURN 'a' IN ['a', 'b'];        -- true
RETURN ['a', 'b'] CONTAINS 'a';  -- true

The same question from either end. Use whichever reads better where you are standing.

The two absences stay apart

RETURN NONE = NULL;   -- false

NONE is no such field; NULL is the field is there and holds nothing. They compare unequal, they are tested separately, and ?? happens to treat both as absent because a fallback is asking one question about both. Nothing else in the language merges them.