# SQL forms

A **SQL form** is a PostgreSQL 16 expression that gives exactly the result
the runtime gives, for every argument. It lets the database do work that
would otherwise take a round trip through your app. It matters in three
places:

- **Computed backfills.** A backfill whose every operation has a SQL form
  runs as one `UPDATE` per batch. Any other runs through
  `jadpo migrate apply`, reading and writing each batch
  ([Computed backfills](computed-backfills.md)). Both are
  correct; the SQL form is only faster.
- **Aggregate queries.** An aggregate query
  ([Counts, sums and groups](counts-sums-and-groups.md))
  is always one statement. It writes its time buckets (`temporal.year`,
  `temporal.month` and `temporal.day` of a date) and its averages
  (`decimal.divide`) with their SQL forms.
- **Conditions.** A query's condition may compare an operation of a field
  that has a SQL form, with its other arguments written out, through an
  index on the form
  ([Operations in conditions](filters.md#operations-in-conditions)).
  Otherwise a query's conditions compute nothing, and a one-step update's
  `set:` still works a stored field out only with `+` and `-`
  ([Counters and other one-step updates](counters-and-other-one-step-updates.md)).

The rules every SQL form follows:

- **An operation that can fail has no SQL form,** since a statement can't
  map a failure to a row.
- **Some arguments have no form.** An operation's form is written from
  its arguments: each a SQL expression and how it is held (a built-in
  type's column, JSON, a range of a built-in type), money's two columns,
  or what a call writes out, such as a rounding mode, `decimal.round`'s
  `scale:` or `text.pad_start`'s `fill:`. An operation has no form for
  arguments the tables don't cover: a rounding mode that isn't written
  out, `text.from` with `form:` or of a time, or `hash.sha256` of a secret
  value.
- **Given values, never `NULL`.** Code tests a value that may be `none`
  before calling an operation on it, so a form never decides what `none`
  means. `range.intersection`, whose result may be `none`, gives `NULL`
  for it.
- **A whole number is always `bigint`.** A field with a narrow range is
  stored in a `smallint` or an `integer`
  ([Storage types](storage-types.md)), whose `abs` and `/`
  overflow at -32768 where an `Int`'s don't. So `integer`'s operations
  widen their arguments first, and `char_length` and
  `jsonb_array_length` are cast, so arithmetic on what they give
  overflows exactly where the runtime's does. A value tested against a
  range is cast to the range's end type for the same reason.
- **A decimal keeps the places the runtime keeps.** A `numeric` carries
  its places as a runtime `Decimal` does, so the forms read them with
  `scale()`. `decimal.round` keeps a value with at most `scale:` places
  as it is, where `round(x, s)` adds zeros (`1.5` to two places stays
  `1.5`). `decimal.divide` works the quotient out from the two mantissas
  with `div` and `mod`, exactly, as the runtime does, never with
  PostgreSQL's `/`, which rounds to the places it chooses.
- **A defect stops the statement.** Where the runtime stops with a
  defect, a whole number past 64 bits or a decimal past 38 digits, the
  form fails too: `bigint` overflows where an `Int` does, and
  `decimal.divide`'s mantissa passes through `numeric(38, 0)`, so a
  backfill stops at the row the runtime would.
- **`temporal.period_between` without `age`.** The form counts the
  months between the two months, one fewer when the end's day of the
  month hasn't reached the start's (one more going back), then steps once
  toward zero where adding them to the start, years first and then
  months, each clamped to a month's last day, would pass the end; the
  days are what is left. The runtime steps until it stops passing; once
  is always enough, since a clamped day is never past the start's. Year
  0 is PostgreSQL's 1 BC, whose `extract` gives -1, so the form counts
  from year 0 as the runtime does. It names its two dates once, in a
  subquery, and gives the three columns a `CalendarPeriod` is stored in.
- **Counts and positions clamped to `integer`.** `left`, `right`,
  `substr` and `lpad` take PostgreSQL's 32-bit `integer`; text is never
  longer than a gigabyte, so a larger count means what the largest does
  and is clamped rather than cast, which would fail.
- **Text in code points, whatever the collation.** Text is compared and
  ordered in code points, as queries sort it
  ([What can be sorted](order-and-pages.md#what-can-be-sorted)), with
  `COLLATE "C"` indexes ([Indexes](indexes.md)). So a SQL form
  never depends on the database's collation. That rules out `lower` and
  `upper`: PostgreSQL 16's follow the collation, and under `"C"` they
  change only ASCII letters. `ends_with` compares with `COLLATE "C"`;
  `strpos`, `starts_with`, `replace` and `btrim` match code points
  already. `trim`'s characters are Unicode's 25 `White_Space` characters,
  which Rust's `str::trim` removes.
- **No session setting changes a form.** Dates are added without a zone,
  and escapes are written `E'...'`, so a session's time zone, date style,
  interval style and `standard_conforming_strings` make no difference.
- **Lists, sets and maps.** A list or set is stored as a `jsonb` array
  and a map as a `jsonb` object ([Data](data.md)); only `count` has a
  SQL form.
- **Each form is tested against the runtime before anything uses it.**
  Each operation with a form is run in the runtime and as its form, on
  the same columns a backfill reads, and the two results must match,
  places and widths included. An input the runtime stops on must fail the
  statement. Every form in the tables below is tested over a hundred
  thousand inputs on live PostgreSQL.

A backfill's operators and conditionals have SQL forms too, tested
against the runtime the same way. In this table `a` and `b` are their
operands:

| Operator | Operands | SQL form |
|---|---|---|
| `and`, `or` | `Bool` | `CASE WHEN a THEN b ELSE FALSE END`, `CASE WHEN a THEN TRUE ELSE b END`, so they stop early as the runtime does |
| `not` | `Bool` | `NOT a` |
| `==`, `!=` | two of one kind: whole numbers and decimals, text and enums, `Bool`, `Uuid`, `Bytes`, dates, instants, durations | `a = b`, `a <> b`, text `COLLATE "C"`, whole numbers as `bigint` |
| `<`, `<=`, `>`, `>=` | numbers, dates, instants, durations | `a < b` |
| `+`, `-`, `*`, `/`, `%`, unary `-` | whole numbers | each as `bigint`, so it overflows where an `Int` does; `/` and `%` drop the remainder toward zero |
| `+`, `-` | decimals, or a decimal and a whole number | the sum at the larger scale, stopping where the runtime's 128-bit mantissas or 38 digits would |
| unary `-` | decimals | `-a` |
| `+`, `-` | durations | as `bigint` milliseconds |
| `+` | text | `a \|\| b` |
| `x == none`, `x != none` | a value that may be `none` | `x IS NULL`, `x IS NOT NULL` |
| `if` chains, `match` | a function's `if`s whose branches return; a `match` on a plain variant, `true`, `false`, a literal, `none` or `some` | `CASE`, which works out only the branch it takes |

A decimal's `*` and `/`, `in`, `matches`, and the operators on money,
quantities, percentages, periods and times moved by a duration have no
form yet.

In the tables below, `x` is the first argument and `a`, `b` the others.