Types, expressions, and control flow

A Sumatra program contains two dialects. Inside a read block’s parentheses and inside write statements you are writing SQL — the connected engine’s own dialect (Firebolt, in this release), relayed verbatim. Between them, in the procedural middle, you are writing Sumatra’s compute dialect — whose value semantics are deliberately identical to the engine’s, down to rounding and overflow, so moving an expression between the two never changes the answer.

Types

Type	What it is	Notes
`INT`	32-bit signed integer	Overflow is a loud fault, never a wrap
`BIGINT`	64-bit signed integer
`NUMERIC(p,s)`	Exact decimal	Precision up to 38
`DOUBLE`	64-bit IEEE float	Bit-exact to the engine; no NaN or Infinity — an operation that would produce one faults instead
`REAL`	32-bit IEEE float
`TEXT`	UTF-8 string
`BOOLEAN`	three-valued with NULL
`DATE`	`0001-01-01` through `9999-12-31`
`TIMESTAMP`	microsecond resolution
`BYTEA`	binary	Carry-only — see below
`ARRAY`	engine array	Server-side carry only

Prefer the spellings DOUBLE and REAL. Firebolt’s own FLOAT keyword is a 64-bit alias, so writing FLOAT invites confusion about width; the corpus convention is DOUBLE for 64-bit and REAL for 32-bit.

Carry vs. compute. Every type can be carried — read from the engine and written back — as long as the value stays on the server side (a read column passed through INSERT ... SELECT FROM batch). Only the scalar types above the line participate in compute — in-memory expressions and variables. BYTEA and ARRAY cannot enter a row loop’s in-memory record, and arithmetic on a carry-only type is a compile error, not a silent coercion.

Literals

Integer literals are typed by magnitude: a value that fits in 32 bits is INT, otherwise BIGINT. Beyond 64 bits, write it in decimal form (9300000000000000000.0). Scientific notation is not accepted.
A literal with a decimal point is NUMERIC with the written scale (4.00 is NUMERIC scale 2).
Strings are single-quoted: 'shipped'.
Booleans are TRUE and FALSE.
Dates and timestamps: DATE '2026-07-01', TIMESTAMP '2026-07-01 12:30:00'.
A NULL that needs a type is written CAST(NULL AS T).

Variables and assignment

Assignment is :=; plain = is SQL’s comparison and SET operator, used only inside SQL statements.

running := GREATEST(running + rec.delta, 0);
rec.status := 'priority';

Reading a variable before it has ever been assigned is a compile error. Assignment converts the value to the declared type; converting a float to an integer rounds half-even (2.5 → 2, 3.5 → 4).

Operators

Category	Operators
Arithmetic	`+ - * /` and unary `-`
String	`\|\|` (concatenation)
Comparison	`= <> < <= > >=`, `BETWEEN`, `LIKE`, `IS NULL`, `IS [NOT] DISTINCT FROM`
Logic	`NOT AND OR` (operands must be BOOLEAN)
Cast	`CAST(x AS T)` and `x::T`
Conditional	`CASE` expressions — simple and searched

Precedence is conventional (OR < AND < NOT < comparison < additive < multiplicative); parenthesize anything you would parenthesize in SQL.

Inequality is <>. The != spelling is not accepted in this release.

A CASE expression selects a value and works anywhere a value does:

tax := rec.subtotal * CASE WHEN rec.country = 'US' THEN 0.00
                           WHEN rec.country = 'DE' THEN 0.19
                           ELSE 0.10 END;

Numeric semantics

Sumatra’s arithmetic is the bound engine’s arithmetic, bit for bit — today, Firebolt’s:

Integers are width-checked at every + - * and every cast: INT math stays INT and faults with OVERFLOW past 32 bits; mixing widths promotes (INT * BIGINT is BIGINT).
Decimals follow the engine’s strict typing: the result scale of + - * / is the widest operand scale, precision grows to fit and is capped at 38, and exceeding it is a loud overflow. Multiplication and division are computed in a wide intermediate and rounded half-up to that scale.
Floats are IEEE 754 with no fused-multiply-add surprises. Mixing DOUBLE with NUMERIC or integers promotes to DOUBLE. A result that would be NaN or Infinity faults instead.
Division by zero faults with ZERO_DIVIDE — never NULL, never Infinity.

One deliberate strictness: operands of CASE arms, GREATEST, and LEAST must share a type family. Mixing TEXT with a numeric or temporal operand is a compile error asking for an explicit CAST, where the engine alone would coerce at runtime and fault only on bad data. Casts that cross into or out of TEXT are not available in compute position — do text parsing and formatting in the read’s SQL, where the engine’s full conversion machinery applies.

NULL semantics

Sumatra uses SQL’s three-valued logic, unchanged:

NULL propagates through operators and functions: NULL + 1 is NULL, NULL || 'x' is NULL, comparisons with NULL are NULL.
Test with IS NULL, or compare null-safely with IS [NOT] DISTINCT FROM.
A condition that evaluates to NULL is not TRUE: an IF takes its ELSE path, and a capture filter skips the row.

NULLs are restricted in exactly three places: ORDER KEY columns (a NULL key is a loud runtime error), keyed-collection keys (NULL never matches; a NULL-key probe raises NO_DATA_FOUND), and merge-key matching (a NULL key component never matches a target row).

Control flow

IF rec.balance < 0 THEN
  ...
ELSIF rec.flagged THEN
  ...
ELSE
  ...
END IF;

CASE rec.band              -- simple form: subject compared to each WHEN
  WHEN 3 THEN rec.grade := 'A';
  WHEN 2 THEN rec.grade := 'B';
  ELSE        rec.grade := 'C';
END CASE;

CASE                       -- searched form: first TRUE predicate wins
  WHEN rec.spend >= 100000 THEN rec.tier := 'platinum';
  ELSE                          rec.tier := 'standard';
END CASE;

WHILE i > 0 LOOP           -- bounded, in-memory
  s := s + i;
  i := i - 1;
END LOOP;

FOR i IN 1 .. rec.n LOOP   -- counted, ascending; i is a read-only INT
  ...
END LOOP;

CONTINUE WHEN pred; (or bare CONTINUE;) skips to the next iteration.
EXIT WHEN pred; (or bare EXIT;) leaves the innermost loop.
RETURN; ends the whole program cleanly (exit 0) from anywhere; writes persist only up to the last COMMIT that was reached.

Every loop closes with END LOOP; — batch reads, row loops, counted loops, and WHILE alike. Compute loops are in-memory constructs: a read block or a COMMIT inside one is a compile error by design.

Records

Declare a record type explicitly, or derive one from a table:

DECLARE
  TYPE audit_rec IS RECORD (
    order_id    BIGINT,
    event       TEXT,
    amount      NUMERIC(14,2),
    logged_at   TIMESTAMP
  );
  ev  audit_rec;
  row2 order_audit%ROWTYPE;    -- shape = the table's columns, from the live catalog
BEGIN
  ev.order_id  := rec.order_id;
  ev.event     := 'shipped';
  ev.amount    := rec.amount;
  ev.logged_at := CURRENT_TIMESTAMP;

  INSERT INTO order_audit VALUES ev;   -- record shape must match the target row

The rows of a read (rec in FOR rec IN batch) are records too — their fields are the SELECT’s columns, and they are assignable.

Collections

Sumatra’s collection is the keyed map: a whole table (or filtered subset) loaded into memory once and probed by key — the lookup-table pattern.

DECLARE
  TYPE rate_map IS TABLE OF rate_dim%ROWTYPE INDEX BY tier;
  rmap rate_map;
BEGIN
  SELECT tier, mult BULK COLLECT INTO rmap FROM rate_dim WHERE mult > 0;

  FOR batch IN (SELECT id, amount, tier, adjusted FROM txns)
      ORDER KEY (id) BATCH SIZE 20000
  LOOP
    FOR rec IN batch LOOP
      rec.adjusted := rec.amount * rmap(rec.tier).mult;
      UPDATE txns SET ROW = rec WHERE id = rec.id;
    END LOOP;
    COMMIT;
  END LOOP;
END;

INDEX BY names the key column(s) of the element type — composite keys are supported (INDEX BY wh, sku), and the probe key can be a runtime-computed value, the one correlation no JOIN can express.
A probe on a missing key (or a NULL key) raises NO_DATA_FOUND — handleable like any fault.
The map is a read-only lookup: there is no iteration over a collection, and no .COUNT-style methods. It is resident in memory, so load dimensions, not fact tables.
Key columns may be integer, decimal, text, date, or timestamp — not float.

For a single-row lookup without a map, SELECT ... INTO fetches scalars directly; zero rows raises NO_DATA_FOUND, and more than one row is a loud error:

SELECT mult INTO v_mult FROM rate_dim WHERE tier = 'gold';

Identifiers and names

Identifiers are ASCII: letters, digits, underscore, not starting with a digit. Case is not significant; the convention is UPPERCASE keywords and lowercase identifiers.
Table names are single bare identifiers — there is no schema.table qualification. A table or column whose name needs quoting in the engine can be reached by aliasing it inside the read’s SQL.
A few words are reserved and cannot name variables, types, or parameters: MERGE, FUNCTION, PROCEDURE, RETURN, BULK COLLECT, INDEX BY, COLLAPSE, SCAN, FOLD, MAP, FILTER.

Introduction

Getting Started

Writing Programs

Reference

Types, expressions, and control flow

Types

Literals

Variables and assignment

Operators

Numeric semantics

NULL semantics

Control flow

Records

Collections

Identifiers and names

​Types

​Literals

​Variables and assignment

​Operators

​Numeric semantics

​NULL semantics

​Control flow

​Records

​Collections

​Identifiers and names

Types

Literals

Variables and assignment

Operators

Numeric semantics

NULL semantics

Control flow

Records

Collections

Identifiers and names