Foundations

Formulas

Fields computed from a record's other fields: typed when written, empty where an input is, evaluated without re-reading.

Markdown

A computed field holds a value derived from the record's other fields — a line's total from its price and quantity, a due date from a release date, a label from a name and a status — written once as a formula and kept up to date whenever what it reads changes. People should never have to work out by hand what the record already knows.

Best practices

Compute what follows from the record; store what was decided. A total, an age, a count of items, a date so many days on: these are formulas. A price, a name, a choice someone made: these are entered. A computed field is shown like any other, but it is not edited — what it says is changed by changing what it reads.

Name fields, not places. A formula refers to fields by name — price * quantity — so it means the same in every record and survives reordering, sorting and filtering. A name in any script is written as it is: 単価 * 数量. A name with spaces or punctuation is written in backticks: `unit price` * quantity. A path through a list of records names that field of every item: sum(lines.amount).

Check a formula when it is written, not when it runs. Every name, every function and the kind of every value is known before the formula is used: a field that does not exist, name * 2, a missing parenthesis or a function given the wrong number of values is refused at once. The message says what is wrong in plain words, and the part of the formula it is about is marked, so it can be put right where it was typed. A formula that is accepted always gives a value of the kind it promised, or none.

Keep types apart, and say how they meet. Numbers are added and multiplied; a date and a number of days make a date, and two dates make a number of days; texts are joined with &. A number becomes text only when asked — "Total: " & text(total) — and then without the residue of binary arithmetic: text(0.1 + 0.2) is 0.3.

Let empty mean empty. A field that has not been filled in is empty, and so is what is computed from it: a total with no price has no value, rather than a value of zero that looks like an answer. A step that has no answer — a division by zero, a date that does not exist — is empty in the same way, never an error code or a number that is not one. Where a formula should carry on without a value, it says so: coalesce(discount, 0), isempty(price).

Where an empty value could not change the answer, give the answer. false and anything is false; true or anything is true. An if whose question is empty has no answer, but the branch it does not take may be empty.

Count what is there. Functions over many values — sum, average, min, max, count — skip what is empty: the average of 2, 4 and an unfilled value is 3. A sum of nothing is 0; the average, smallest or largest of nothing is empty.

Text is empty when it says nothing. An empty text joins as nothing and compares as "", and a computed text that comes to nothing is shown as empty.

Round as people write. round(1.005, 2) is 1.01, halves away from zero, as the number is written — not as binary floating point holds it.

Show that a value is computed, and from what. A computed field reads as computed — distinct from a field to type into — and its formula, with the fields it reads, is one step away. A field whose formula refers back to itself, directly or through others, is a cycle: it is shown as one, naming the fields in it, rather than computed.

Recompute what changed, and only that. When one field changes, only the formulas that read it — and those that read them — are computed again, in an order where each comes after what it reads. A formula that reads today's date changes with the day as well.

Conventions

WrittenMeans
+ - * / %arithmetic on numbers; % is the remainder with the divisor's sign
released + 30, released - duea date 30 days on; the days between two dates
&texts joined
= <> < <= > >=comparison of two values of one kind
and, or, notlogic on true-or-false values; not binds tighter than and, and than or
if(question, yes, no)one of two answers of one kind
round(x, digits), floor, ceil, absnumbers
sum, average, min, max, countover values and lists alike, skipping what is empty
len, upper, lower, trim, texttexts; len counts characters as people see them
today(), days(from, to), date(y, m, d), year, month, daydates
coalesce(a, b, …), isempty(x)the first value that is not empty; whether a value is empty

Function names and the words and, or, not, true and false may be written in any case; field names are exact.

Behaviour

Values and operators

Numbers are ASCII digits with an optional point, and texts are in quotation marks
expect(value("42")).toBe(42);
expect(value("0.5 + .25 + 5.")).toBe(5.75);
expect(value('"a \\"quoted\\" word \\\\"')).toBe('a "quoted" word \\');
expect(value("true")).toBe(true);
expect(value("FALSE")).toBe(false);
A number of any length is the nearest value there is to it, as JavaScript reads it
const digits = fc.string({ unit: fc.constantFrom(..."0123456789"), minLength: 1, maxLength: 40 });
fc.assert(
  fc.property(digits, fc.option(digits), (whole, fraction) => {
    const source = fraction === null ? whole : `${whole}.${fraction}`;
    return value(source) === Number(source);
  }),
);
expect(value("007")).toBe(7);
expect(value("9007199254740993")).toBe(9_007_199_254_740_992);
// Seventeen digits, where adding them up one at a time would round twice.
expect(value("90000012345694739")).toBe(Number("90000012345694739"));
expect(value("90000012345861038")).toBe(Number("90000012345861038"));
A point needs a digit beside it, and a number has one point at most
expect(refusal(".")).toMatchObject({ message: "“.” cannot be read here.", marked: "." });
expect(refusal("1 + .")).toMatchObject({ message: "“.” cannot be read here.", at: 4 });
// A second point begins another number, which wants an operator before it.
expect(refusal("1..2")).toMatchObject({ message: "An operator is missing before “.2”.", marked: ".2" });
expect(refusal("1.2.3")).toMatchObject({
  message: "An operator is missing before “.3”.",
  marked: ".3",
});
Multiplication, division and remainder bind tighter than addition and subtraction, and each applies left to right
const values = { price: 12.5, quantity: 4 };
expect(value("price * quantity", values)).toBe(50);
expect(value("2 + 3 * 4")).toBe(14);
expect(value("10 - 4 - 3")).toBe(3);
expect(value("24 / 4 / 2")).toBe(3);
expect(value("(2 + 3) * 4")).toBe(20);
expect(value("2 + 10 % 4")).toBe(4);
Any number of signs may precede a number, each minus turning it about
expect(value("-3")).toBe(-3);
expect(value("4 * -2")).toBe(-8);
expect(value("--3")).toBe(3);
expect(value("-+-+-3")).toBe(-3);
expect(value(`${"-".repeat(99_996)}3`)).toBe(3);
% is the remainder with the sign of the divisor, as spreadsheets have it
expect(value("7 % 3")).toBe(1);
expect(value("-7 % 3")).toBe(2);
expect(value("7 % -3")).toBe(-2);
expect(value("5.5 % 2")).toBe(1.5);
A date plus or minus a number is a date so many days on; a date less a date is the days between
const values = { released: "2026-10-01", due: "2026-12-25" };
expect(value("released + 30", values)).toBe("2026-10-31");
expect(value("30 + released", values)).toBe("2026-10-31");
expect(value("released - 1", values)).toBe("2026-09-30");
expect(value("due - released", values)).toBe(85);
expect(value("released - due", values)).toBe(-85);
expect(value("released + 365", { released: "2028-01-01" })).toBe("2028-12-31");
& joins texts
expect(value('name & " (" & status & ")"', { name: "Widget", status: "open" })).toBe("Widget (open)");
A comparison gives true or false, and compares values of one kind
expect(value("1 < 2")).toBe(true);
expect(value("2 <= 2")).toBe(true);
expect(value("3 > 2")).toBe(true);
expect(value("2 >= 3")).toBe(false);
expect(value("2 = 2")).toBe(true);
expect(value("2 <> 2")).toBe(false);
expect(value("released < due", { released: "2026-10-01", due: "2026-12-25" })).toBe(true);
expect(value('"apple" < "banana"')).toBe(true);
expect(value('"B" < "a"')).toBe(true);
expect(value("paid = shipped", { paid: true, shipped: true })).toBe(true);
expect(value("paid <> shipped", { paid: true, shipped: false })).toBe(true);
and, or and not combine true-or-false values; not binds tighter than and, and and than or
expect(value("true or false and false")).toBe(true);
expect(value("(true or false) and false")).toBe(false);
expect(value("not false and false")).toBe(false);
expect(value("not (false and false)")).toBe(true);
expect(value("not not true")).toBe(true);
expect(value("not 1 > 2")).toBe(true);
expect(value("1 < 2 and 2 < 3")).toBe(true);
Words of the language and function names are read in any case; field names are exact
expect(value("NOT False OR True")).toBe(true);
expect(value("ROUND(2.5) + Abs(-1)")).toBe(4);
expect(refusal("Price * 2").message).toBe("There is no field “Price”.");
Spaces, tabs, line breaks and the ideographic space separate tokens
expect(value(" 1\t+\n2\r\n* 3 ")).toBe(7);

Names

A field is named by its name, in any script
const types = { 単価: "number", 数量: "number", Größe_2: "number" } as const;
expect(value("単価 * 数量", { 単価: 300, 数量: 3 }, types)).toBe(900);
expect(value("Größe_2 + 1", { Größe_2: 1 }, types)).toBe(2);
A name that is not an identifier is written in backticks, with a backtick or a backslash in it escaped
const types = { "unit price": "number", "a`b\\c": "number", "2nd": "number" } as const;
expect(value("`unit price` * 2", { "unit price": 7 }, types)).toBe(14);
expect(value("`a\\`b\\\\c` + 1", { "a`b\\c": 1 }, types)).toBe(2);
expect(value("`2nd`", { "2nd": 5 }, types)).toBe(5);
A dotted path names a field of each item of a list of records, as a list
const types = { "order lines.unit price": "list-number" } as const;
expect(value("sum(lines.amount)", { "lines.amount": [1, 2, 3] })).toBe(6);
expect(value("sum(`order lines`.`unit price`)", { "order lines.unit price": [4, 5] }, types)).toBe(9);
A field called as a word of the language is written in backticks
const types = { and: "number", true: "boolean" } as const;
expect(value("`and` + 1", { and: 1 }, types)).toBe(2);
expect(value("`true`", { true: false }, types)).toBe(false);
expect(refusal("and + 1", types)).toEqual({
  message: "A field called “and” is written in backticks: `and`.",
  marked: "and",
  at: 0,
});
A name followed by parentheses calls a function; alone, it names a field
const types = { round: "number" } as const;
expect(value("round(round)", { round: 2.5 }, types)).toBe(3);

Functions

round rounds halves away from zero, to whole numbers or to the digits asked for, as the number is written
expect(value("round(2.5)")).toBe(3);
expect(value("round(-2.5)")).toBe(-3);
expect(value("round(2.4999)")).toBe(2);
expect(value("round(1.005, 2)")).toBe(1.01);
expect(value("round(2.675, 2)")).toBe(2.68);
expect(value("round(-1.005, 2)")).toBe(-1.01);
expect(value("round(1234.5, -2)")).toBe(1200);
expect(value("round(1250, -2)")).toBe(1300);
expect(value("round(0.1 + 0.2, 15)")).toBe(0.3);
expect(value("round(123456789012345678, 2)")).toBe(123_456_789_012_345_680);
round to digits that are not a whole number from −15 to 15 is empty
for (const digits of ["0.5", "16", "-16"]) expect(value(`round(1.5, ${digits})`)).toBeNull();
floor, ceil and abs take a number
expect(value("floor(-2.5)")).toBe(-3);
expect(value("ceil(-2.5)")).toBe(-2);
expect(value("abs(-2.5)")).toBe(2.5);
min and max take numbers or dates, any number of them, and lists of them
expect(value("min(3, 1, 2)")).toBe(1);
expect(value("max(3, 1, 2)")).toBe(3);
expect(value("max(lines.amount, 0)", { "lines.amount": [-4, -2] })).toBe(0);
expect(value("min(lines.due)", { "lines.due": ["2026-12-01", "2026-11-05"] })).toBe("2026-11-05");
expect(value("max(released, due)", { released: "2026-10-01", due: "2026-09-01" })).toBe("2026-10-01");
sum, average and count take any number of values and lists
const values = { "lines.amount": [1, 2, 3.5], tags: ["a", "b"], price: 4 };
expect(value("sum(lines.amount)", values)).toBe(6.5);
expect(value("sum(lines.amount, price, 1)", values)).toBe(11.5);
expect(value("average(lines.amount, 3.5)", values)).toBe(2.5);
expect(value("count(tags)", values)).toBe(2);
expect(value("count(tags, name, price)", { ...values, name: "x" })).toBe(4);
expect(value("count(lines.paid)", { "lines.paid": [true, false, true] })).toBe(3);
len counts the characters of a text as people perceive them
const types = { name: "text" } as const;
expect(value("len(name)", { name: "Widget" }, types)).toBe(6);
expect(value("len(name)", { name: "日本" }, types)).toBe(2);
expect(value("len(name)", { name: "é" }, types)).toBe(1);
expect(value("len(name)", { name: "👨‍👩‍👧" }, types)).toBe(1);
upper, lower and trim change a text
expect(value('upper("abc")')).toBe("ABC");
expect(value('lower("ÄBC")')).toBe("äbc");
expect(value('trim("  a b  ")')).toBe("a b");
today is the local date, unless fixed
const now = new Date();
const local = `${now.getFullYear()}-${String(now.getMonth() + 1).padStart(2, "0")}-${String(now.getDate()).padStart(2, "0")}`;
expect(value("today()")).toBe(local);
expect(withToday("2026-10-14", () => value("today()"))).toBe("2026-10-14");
expect(withToday("2026-10-14", () => withToday("2000-02-29", () => value("today()")))).toBe(
  "2000-02-29",
);
expect(value("today()")).toBe(local);
Fixing today at what is not an ISO date is an error, and a fixed today is put back whatever the function does
expect(() => withToday("2026-02-30", () => 0)).toThrow(RangeError);
expect(() => withToday("14/10/2026", () => 0)).toThrow(RangeError);
expect(() =>
  withToday("2001-01-01", () => {
    throw new Error("inside");
  }),
).toThrow("inside");
expect(value("today()")).not.toBe("2001-01-01");
days counts the days from one date to another
const values = { released: "2026-10-01" };
expect(withToday("2026-10-14", () => value("days(released, today())", values))).toBe(13);
expect(withToday("2026-10-14", () => value("days(today(), released)", values))).toBe(-13);
expect(value("days(released, released + 366)", { released: "2028-02-28" })).toBe(366);
year, month and day take a date apart, and date puts one together
const values = { released: "2024-02-29" };
expect(value("year(released)", values)).toBe(2024);
expect(value("month(released)", values)).toBe(2);
expect(value("day(released)", values)).toBe(29);
expect(value("date(2026, 10, 14)")).toBe("2026-10-14");
expect(value("date(1, 1, 1)")).toBe("0001-01-01");
date of a day that does not exist is empty, rather than rolled over
for (const args of [
  "2026, 2, 29",
  "2026, 13, 1",
  "2026, 0, 1",
  "2026, 1, 0",
  "0, 1, 1",
  "10000, 1, 1",
  "2026, 1.5, 1",
]) {
  expect(value(`date(${args})`)).toBeNull();
}
expect(value("date(2024, 2, 29)")).toBe("2024-02-29");
expect(value("date(2000, 2, 29)")).toBe("2000-02-29");
expect(value("date(1900, 2, 29)")).toBeNull();
if gives one of two answers of one kind
const values = { quantity: 4, total: 100 };
expect(value("if(quantity > 0, total / quantity, 0)", values)).toBe(25);
expect(value("if(quantity > 0, total / quantity, 0)", { ...values, quantity: 0 })).toBe(0);
expect(value('if(paid, "paid", "due")', { paid: false })).toBe("due");
coalesce is its first value that is not empty, and isempty asks whether a value is
expect(value("coalesce(price, discount, 0)", { discount: 0.1 })).toBe(0.1);
expect(value("coalesce(price, discount, 0)")).toBe(0);
expect(value('coalesce(name, "unnamed")')).toBe("unnamed");
expect(value("isempty(price)")).toBe(true);
expect(value("isempty(price)", { price: 0 })).toBe(false);
expect(value("isempty(name)", { name: "" })).toBe(true);
text writes a number to fifteen significant digits, a date as ISO, and true or false as words
expect(value('"Total: " & text(0.1 + 0.2)')).toBe("Total: 0.3");
expect(value("text(-0)")).toBe("0");
expect(value("text(released)", { released: "2026-10-14" })).toBe("2026-10-14");
expect(value("text(paid)", { paid: true })).toBe("true");
expect(value("text(name)", { name: "x" })).toBe("x");

Empty

Arithmetic, comparison and functions of an empty number, date or true-or-false value are empty
for (const formula of [
  "price * quantity",
  "-price",
  "price > 0",
  "round(price, 2)",
  "abs(price)",
  "released + 1",
  "days(released, due)",
  "year(released)",
  "date(2026, price, 1)",
  "text(price) & text(released)",
]) {
  const result = value(formula, { quantity: 2, due: "2026-10-01" });
  expect(result === null || result === "", formula).toBe(true);
}
A step that has no finite answer leaves the whole empty, whatever the steps after it would make of it
expect(value("1 / 0")).toBeNull();
expect(value("0 / 0")).toBeNull();
expect(value("1 / (1 / 0)")).toBeNull();
expect(value("5 - 2 / (3 / 0)")).toBeNull();
expect(value("1 / -(1 / 0)")).toBeNull();
expect(value(`1 / (${"9".repeat(200)} * ${"9".repeat(200)})`)).toBeNull();
expect(value(`1 / (${"9".repeat(308)} + ${"9".repeat(308)})`)).toBeNull();
expect(value(`1 / (-${"9".repeat(308)} - ${"9".repeat(308)})`)).toBeNull();
expect(value("5 % 0")).toBeNull();
expect(value(`${"9".repeat(200)} * ${"9".repeat(200)} * 0`)).toBeNull();
expect(value("sum(lines.amount)", { "lines.amount": [Number.MAX_VALUE, Number.MAX_VALUE] })).toBeNull();
A date before year 1 or after 9999, or a fractional number of days on, is empty
expect(value("date(9999, 12, 31) + 1")).toBeNull();
expect(value("date(1, 1, 1) - 1")).toBeNull();
expect(value("date(2026, 1, 1) + 1.5")).toBeNull();
expect(value("date(9999, 12, 31) - date(1, 1, 1)")).toBe(3_652_058);
and and or are not empty where the other side decides them
expect(value("paid and false")).toBe(false);
expect(value("false and paid")).toBe(false);
expect(value("paid or true")).toBe(true);
expect(value("paid and true")).toBeNull();
expect(value("paid or false")).toBeNull();
expect(value("not paid")).toBeNull();
if with an empty question is empty; the answer not given may be empty
expect(value("if(paid, 1, 2)")).toBeNull();
expect(value('if(paid, "a", "b")')).toBeNull();
expect(value("if(true, 1, price)")).toBe(1);
expect(value("if(quantity > 0, total / quantity, 0)", { quantity: 0, total: 5 })).toBe(0);
Text has no empty but the empty text: an empty text field joins and compares as "", and a result of "" is empty
expect(value('name & "!"')).toBe("!");
expect(value('name = ""')).toBe(true);
expect(value("len(name)")).toBe(0);
expect(value("upper(name)")).toBeNull();
expect(value('trim("   ")')).toBeNull();
expect(value('""')).toBeNull();
sum, average, min, max and count skip what is empty
const values = { "lines.amount": [1, null, 2], tags: ["a", "", null, "b"] };
expect(value("sum(lines.amount, price)", values)).toBe(3);
expect(value("average(lines.amount)", values)).toBe(1.5);
expect(value("min(lines.amount)", values)).toBe(1);
expect(value("max(lines.amount, price)", values)).toBe(2);
expect(value("count(lines.amount, price)", values)).toBe(2);
expect(value("count(tags)", values)).toBe(2);
expect(value("count(name, price, paid)", { paid: false })).toBe(1);
sum of nothing is 0; average, min and max of nothing are empty; count of nothing is 0
for (const values of [{}, { "lines.amount": [] }, { "lines.amount": [null, null] }]) {
  expect(value("sum(lines.amount)", values)).toBe(0);
  expect(value("average(lines.amount)", values)).toBeNull();
  expect(value("min(lines.amount)", values)).toBeNull();
  expect(value("max(lines.amount)", values)).toBeNull();
  expect(value("count(lines.amount)", values)).toBe(0);
}
A value of the wrong kind from the record is empty
for (const price of ["12", Number.NaN, Number.POSITIVE_INFINITY, true, {}, [1]]) {
  expect(value("price + 1", { price })).toBeNull();
}
for (const released of ["2026-02-30", "2026-1-01", "20261001", 20_261_001, "0000-01-01"]) {
  expect(value("released + 1", { released })).toBeNull();
}
expect(value("not paid", { paid: 1 })).toBeNull();
expect(value('name & "!"', { name: 42 })).toBe("!");
expect(value("sum(lines.amount)", { "lines.amount": "1,2" })).toBe(0);
expect(value("sum(lines.amount)", { "lines.amount": [1, "2", Number.NaN, 4] })).toBe(5);
expect(value("count(lines.due)", { "lines.due": ["2026-10-01", "soon", null] })).toBe(1);
A result is never NaN and never negative zero
for (const formula of [
  "-0",
  "0 * -1",
  "0 / -5",
  "-(0)",
  "-0.0",
  "0 - 0",
  "round(-0.4)",
  "ceil(-0.5)",
  "-7 % 7",
  "sum(-0)",
]) {
  expect(Object.is(value(formula), 0), formula).toBe(true);
}

Errors

A formula that cannot be read is refused, with a message and the part of it to mark
for (const [formula, message, marked] of [
  ["", "A formula needs something to compute.", ""],
  ["   ", "A formula needs something to compute.", "   "],
  ["pricee + 1", "There is no field “pricee”.", "pricee"],
  ["lines.amont", "There is no field “lines.amont”.", "lines.amont"],
  ["rnd(price)", "There is no function “rnd”.", "rnd"],
  ["name * 2", "“*” multiplies numbers; this is text.", "name"],
  ["2 * (name)", "“*” multiplies numbers; this is text.", "(name)"],
  ["price + released * 2", "“*” multiplies numbers; this is a date.", "released"],
  ["price - released", "“-” subtracts numbers, or from a date; this is a date.", "released"],
  ["-released", "A sign goes before a number; this is a date.", "released"],
  ["name & price", "“&” joins texts — text(…) writes anything else as one; this is a number.", "price"],
  ["price < name", "“<” compares values of one kind: this is text, the other a number.", "name"],
  [
    "paid < shipped",
    "“<” orders numbers, dates and texts; true or false compare only with = and <>.",
    "<",
  ],
  ["price and paid", "“and” joins values that are true or false; this is a number.", "price"],
  ["not price", "“not” turns about a value that is true or false; this is a number.", "price"],
  ["if(price, 1, 2)", "if asks a question that is true or false; this is a number.", "price"],
  [
    "if(paid, 1, name)",
    "Both answers of if are of one kind: this is text, the other a number.",
    "name",
  ],
  ["round(price, 2, 3)", "round takes one or two values; this gives three.", "round(price, 2, 3)"],
  ["today(1)", "today takes no values; this gives one.", "today(1)"],
  ["days(released)", "days takes two values; this gives one.", "days(released)"],
  ["sum()", "sum takes at least one value; this gives none.", "sum()"],
  [
    "min(price, released)",
    "min compares numbers or dates, all of one kind: this is a date, the first a number.",
    "released",
  ],
  ["sum(tags)", "sum adds numbers; this is a list of texts.", "tags"],
  [
    "lines.amount",
    "A formula gives a single value, which sum, average, min, max or count make of a list; this is a list of numbers.",
    "lines.amount",
  ],
  ["lines.amount * 2", "“*” multiplies numbers; this is a list of numbers.", "lines.amount"],
  ["if(paid, tags, tags)", "if gives a single value; this is a list of texts.", "tags"],
  ["(price + 1", "This parenthesis is never closed.", "("],
  ["(price + 1 2", "An operator or a closing parenthesis is missing before “2”.", "2"],
  ["price + 1)", "This parenthesis closes nothing.", ")"],
  ["round(price", "This call of round is never closed.", "round("],
  ["round(price 2)", "An operator or a comma is missing before “2”.", "2"],
  ["price quantity", "An operator is missing before “quantity”.", "quantity"],
  ["price, 1", "A comma separates what is given to a function, and this is outside one.", ","],
  ["price *", "Something to compute is missing after “*”.", "*"],
  ["price * )", "Something to compute is missing after “*”.", "*"],
  ["()", "Something to compute is missing after “(”.", "("],
  ["round(, 2)", "Something to compute is missing after “(”.", "("],
  ["* 2", "Something to compute is missing before “*”.", "*"],
  ["price * not paid", "Put “not” and what it turns about in parentheses here.", "not"],
  ["price # 2", "“#” cannot be read here.", "#"],
  ["price 😀", "“😀” cannot be read here.", "😀"],
  ['"open', "This text is never closed with a quotation mark.", '"open'],
  ['"a\\n"', "Within a text, a backslash comes only before a quotation mark or a backslash.", "\\n"],
  ["`unit price", "This name is never closed with a backtick.", "`unit price"],
  ["`a\\b`", "Within backticks, a backslash comes only before a backtick or a backslash.", "\\b"],
  ["``", "A name in backticks cannot be empty.", "``"],
  ["lines.", "A field's name is missing after this point.", "."],
  [`${"9".repeat(400)}`, "This number is too large to hold.", "9".repeat(400)],
] as const) {
  const refused = refusal(formula);
  expect({ formula, message: refused.message, marked: refused.marked }).toEqual({
    formula,
    message,
    marked,
  });
}
A field that refers to itself compiles: a cycle is for the caller to find
const compiled = compileFormula("total + 1", scopeOf(TYPES));
expect(compiled.ok && compiled.references).toEqual(["total"]);
Parentheses and calls nest sixty-four deep; a formula nested deeper is refused at the opening too many
expect(value(`${"(".repeat(64)}1 + 1${")".repeat(64)}`)).toBe(2);
expect(value(`${"abs(".repeat(64)}-1${")".repeat(64)}`)).toBe(1);
expect(refusal(`${"(".repeat(65)}1${")".repeat(65)}`)).toEqual({
  message: "Formulas nest at most 64 deep.",
  marked: "(",
  at: 64,
});
expect(refusal(`${"abs(".repeat(65)}1${")".repeat(65)}`)).toMatchObject({ marked: "abs", at: 256 });
// Depth is how far inside, not how many: these are side by side.
expect(value(`${"(1) + ".repeat(1000)}1`)).toBe(1001);
Ninety-nine thousand open parentheses, signs or nots are refused or read, never an exhausted stack
expect(refusal("(".repeat(99_999)).message).toBe("Formulas nest at most 64 deep.");
expect(refusal("round(".repeat(16_000)).message).toBe("Formulas nest at most 64 deep.");
expect(value(`${"-".repeat(99_999)}1`)).toBe(-1);
expect(value(`${"not ".repeat(24_999)}true`)).toBe(false);
// And the next formula is read as if nothing had happened.
expect(value("(1 + 2) * 3")).toBe(9);
A formula is at most 100,000 characters; the characters past that are marked
expect(value(`${"1+".repeat(49_999)}1`)).toBe(50_000);
expect(refusal(`${"1+".repeat(50_000)}1`)).toEqual({
  message: "A formula is at most 100,000 characters long.",
  marked: "1",
  at: 100_000,
});

Compiled once, evaluated often

A compiled formula states its type and every path it reads, once each, in the order first mentioned
const compiled = compileFormula(
  "if(paid, sum(lines.amount) * price, price) + quantity",
  scopeOf(TYPES),
);
expect(compiled).toMatchObject({
  ok: true,
  type: "number",
  references: ["paid", "lines.amount", "price", "quantity"],
});
expect(compileFormula("released + 1", scopeOf(TYPES))).toMatchObject({ type: "date" });
expect(compileFormula("name & status", scopeOf(TYPES))).toMatchObject({ type: "text" });
expect(compileFormula("paid or shipped", scopeOf(TYPES))).toMatchObject({ type: "boolean" });
expect(compileFormula("1 + 1", scopeOf(TYPES))).toMatchObject({ references: [] });
A formula that reads today says so, as its value changes with the day
expect(compileFormula("days(released, today())", scopeOf(TYPES))).toMatchObject({ volatile: true });
expect(compileFormula("days(released, due)", scopeOf(TYPES))).toMatchObject({ volatile: false });
Each mention of a field is asked for once per evaluation, as the formula reaches it
const compiled = compileFormula(
  "price + price * quantity + if(quantity > 0, price, total)",
  scopeOf(TYPES),
);
if (!compiled.ok) throw new Error(compiled.message);
const asked: string[] = [];
const get = (path: string) => {
  asked.push(path);
  return 2;
};
expect(compiled.evaluate(get)).toBe(8);
expect(asked).toEqual(["price", "price", "quantity", "quantity", "price", "total"]);
asked.length = 0;
compiled.evaluate(get);
expect(asked).toHaveLength(6);
Evaluating never reads the formula's text again
const text = "round(price * quantity * (1 - discount), 2) + sum(lines.amount)";
let examined = 0;
const source = new Proxy(new String(text), {
  get(_, key) {
    if (key === "length") return text.length;
    if (key === "charCodeAt") {
      return (at: number) => {
        examined += 1;
        return text.charCodeAt(at);
      };
    }
    if (key === "slice") {
      return (start: number, end = text.length) => {
        examined += end - start;
        return text.slice(start, end);
      };
    }
    throw new Error(`a way of reading the text that is not counted: ${String(key)}`);
  },
}) as unknown as string;
const compiled = compileFormula(source, scopeOf(TYPES));
if (!compiled.ok) throw new Error(compiled.message);
const read = examined;
const values: Record<string, unknown> = { price: 2, quantity: 3, discount: 0.5, "lines.amount": [1] };
for (let i = 0; i < 100; i += 1) expect(compiled.evaluate((path) => values[path])).toBe(4);
expect(examined).toBe(read);
A long formula is compiled in one pass: each character is looked at a fixed number of times
/** A text that counts the characters looked at in it, however they are asked for. */
function countedText(text: string) {
  let examined = 0;
  const source = new Proxy(new String(text), {
    get(_, key) {
      if (key === "length") return text.length;
      if (key === "charCodeAt") {
        return (at: number) => {
          examined += 1;
          return text.charCodeAt(at);
        };
      }
      if (key === "slice") {
        return (start: number, end = text.length) => {
          examined += end - start;
          return text.slice(start, end);
        };
      }
      throw new Error(`a way of reading the text that is not counted: ${String(key)}`);
    },
  }) as unknown as string;
  return { source, examined: () => examined };
}
const types = { ...TYPES, 単価: "number", "unit price": "number" } as const;
// How many times a character is looked at, on average, for formulas of different makes.
for (const [text, times] of [
  [`${"1+".repeat(49_000)}1`, 2],
  [`${"round(price * quantity, 2) + ".repeat(3000)}0`, 1.85],
  [`${'if(len(name & "は") > 2, 単価, `unit price`) - '.repeat(2000)}0`, 1.75],
  [`${"sum(lines.amount, 1.25, -total) % ".repeat(2500)}7`, 1.85],
] as const) {
  const { source, examined } = countedText(text);
  const compiled = compileFormula(source, scopeOf(types));
  expect(compiled.ok).toBe(true);
  expect(examined()).toBeLessThanOrEqual(times * text.length + 8);
}
Folding a list reads each of its items once
const amounts = counted(Array.from({ length: 100_000 }, (_, i) => (i % 10 === 0 ? null : i)));
const values: Record<string, unknown> = { "lines.amount": amounts.array };
for (const formula of [
  "sum(lines.amount)",
  "average(lines.amount)",
  "max(lines.amount)",
  "count(lines.amount)",
]) {
  amounts.reset();
  expect(value(formula, values)).not.toBeNull();
  expect(amounts.accesses()).toBe(100_000);
}
A formula may be evaluated from within the get of another evaluation, of itself or another
// A folder's size is its own plus its children's, each computed by the same formula.
interface Folder {
  readonly own: number;
  readonly children: readonly Folder[];
}
const types = { own: "number", "children.size": "list-number" } as const;
const size = compileFormula("own + sum(children.size) * 1", scopeOf(types));
if (!size.ok) throw new Error(size.message);
const evaluate = (folder: Folder): unknown =>
  size.evaluate((path) => (path === "own" ? folder.own : folder.children.map(evaluate)));
const expected = (folder: Folder): number =>
  folder.own + folder.children.reduce((total, child) => total + expected(child), 0);
// Deep enough that the shared stacks must grow while frames below are in use.
let deep: Folder = { own: 1, children: [] };
for (let i = 0; i < 300; i += 1) deep = { own: i, children: [deep, { own: 2, children: [] }] };
const folder = { own: 5, children: [deep, { own: 3, children: [{ own: 4, children: [] }] }] };
expect(evaluate(folder)).toBe(expected(folder));
expect(value("1 + 2 * 3")).toBe(7);

Agreement with a model

Agrees with a plain statement of its meaning on any arithmetic over fields that may be empty
const types = { x: "number", y: "number", z: "number" } as const;
fc.assert(
  fc.property(
    trees,
    fc.record({ x: field, y: field, z: field }),
    fc.array(fc.constantFrom("", " ", "  ", "\n")),
    (tree, fields, spaces) => {
      let next = 0;
      const space = () => spaces[next++ % Math.max(1, spaces.length)] ?? "";
      const text = print(tree, space);
      const compiled = compileFormula(text, scopeOf(types));
      if (!compiled.ok) return false;
      const actual = compiled.evaluate((path) => fields[path as "x"]);
      const expected = model(tree, fields);
      return Number.isNaN(expected) ? actual === null : Object.is(actual, expected + 0);
    },
  ),
  { numRuns: 2000 },
);
and, or and not agree with three-valued logic, with precedence as written
const types = { p: "boolean", q: "boolean", r: "boolean" } as const;
const truth = fc.constantFrom(true, false, null);
fc.assert(
  fc.property(logic, fc.record({ p: truth, q: truth, r: truth }), (tree, fields) => {
    const compiled = compileFormula(printLogic(tree), scopeOf(types));
    if (!compiled.ok) return false;
    return compiled.evaluate((path) => fields[path as "p"]) === kleene(tree, fields);
  }),
  { numRuns: 1000 },
);
A date so many days on agrees with the platform's calendar, across the years an ISO date can write
const types = { released: "date", days: "number" } as const;
const compiled = compileFormula("released + days", scopeOf(types));
if (!compiled.ok) throw new Error(compiled.message);
fc.assert(
  fc.property(
    fc.date({
      min: new Date("0001-01-01T00:00:00Z"),
      max: new Date("9999-12-31T00:00:00Z"),
      noInvalidDate: true,
    }),
    fc.integer({ min: -400_000, max: 400_000 }),
    (date, days) => {
      const iso = date.toISOString().slice(0, 10);
      const moved = new Date(Date.UTC(2000, 0, 1));
      moved.setUTCFullYear(date.getUTCFullYear(), date.getUTCMonth(), date.getUTCDate() + days);
      const year = moved.getUTCFullYear();
      const expected = year >= 1 && year <= 9999 ? moved.toISOString().slice(0, 10) : null;
      return compiled.evaluate((path) => (path === "released" ? iso : days)) === expected;
    },
  ),
  { numRuns: 2000 },
);

React

Install

npx shadcn@latest add https://ui.publictheta.com/r/formula.json
Source

src/algorithms/formula.ts

/**
 * A typed formula over a record's fields, so a field can be computed from
 * others: `price * quantity`, `round(total * (1 - discount), 2)`,
 * `released + 30`, `name & " (" & status & ")"`, `sum(lines.amount)`.
 *
 * A formula is compiled once against a scope — what each field name stands for —
 * and then evaluated as often as its fields change. Compiling checks every type,
 * so a formula that compiles always has a value of its stated type, or none.
 *
 * Grammar, loosest binding first:
 *
 *     formula := or
 *     or      := and ("or" and)*
 *     and     := not ("and" not)*
 *     not     := "not"* compare
 *     compare := concat (("=" | "<>" | "<" | "<=" | ">" | ">=") concat)*
 *     concat  := sum ("&" sum)*
 *     sum     := product (("+" | "-") product)*
 *     product := unary (("*" | "/" | "%") unary)*
 *     unary   := ("-" | "+")* primary
 *     primary := number | text | "true" | "false" | "(" or ")"
 *              | function "(" (or ("," or)*)? ")" | path
 *     path    := name ("." name)*        no space around the point
 *     name    := identifier | "`" … "`"
 *
 * - A number is ASCII digits with an optional `.` point: `12`, `0.5`, `.5`.
 * - A text is `"…"`; within it `\"` is a quotation mark and `\\` a backslash.
 * - An identifier is letters (any script), marks, digits and `_`, not starting
 *   with a digit, so `単価 * 数量` reads. Any other name is written in
 *   backticks, with `` \` `` and `\\` escaped: `` `unit price` ``.
 * - Field names are exact. Function names and the words `and`, `or`, `not`,
 *   `true` and `false` are read in any case; a field called one of those words
 *   is written in backticks.
 * - Spaces, tabs, line breaks, the no-break space and the ideographic space an
 *   input method types separate tokens.
 *
 * Types, checked when compiled: `+ - * / %` take numbers (`%` is the remainder
 * with the divisor's sign, as spreadsheets have it). A date plus or minus a
 * number is a date so many days on; a date less a date is the days between.
 * `&` joins texts. A comparison takes two values of one kind; true-or-false
 * values compare only with `=` and `<>`; texts compare by code unit. `and`,
 * `or` and `not` take true-or-false values. A list — a path through a list of
 * records, `lines.amount`, or a list field — is taken only by `sum`, `average`,
 * `min`, `max` and `count`; a formula's own value is never a list.
 *
 * Empty — `null` from the record, written "empty" here — propagates:
 *
 * - Arithmetic, comparison and every function of an empty number, date or
 *   true-or-false value is empty. So is a step that has no finite answer: a
 *   division by zero, a number too large to hold, a date before year 1 or after
 *   9999, a fractional number of days, a date that does not exist.
 * - `and` and `or` know their answer without an empty side where the other
 *   decides it: `false and empty` is false, `true or empty` true.
 * - `if` with an empty question is empty; the branch not taken may be empty.
 * - Text has no empty apart from `""`: an empty text field reads as `""`, so
 *   `&`, `len` and text comparisons never give empty, and a text result of `""`
 *   is given as `null`.
 * - `sum`, `average`, `min`, `max` and `count` skip empties, in lists and among
 *   their arguments alike: `sum` of nothing is 0; `average`, `min` and `max` of
 *   nothing are empty; `count` counts what is not empty. `coalesce` is its first
 *   argument that is not empty, and `isempty` asks.
 *
 * A formula is at most 100,000 characters, and parentheses and calls nest at
 * most 64 deep, so no text can exhaust the stack. Every other text compiles or
 * is refused with a message and the range — UTF-16 indices into the text — to
 * mark. A field that refers to itself, directly or not, is a cycle among
 * formulas, which is the caller's to find.
 *
 * Evaluation. A formula compiles to a small bytecode in an `Int32Array` run
 * over two stacks: a `Float64Array` holding numbers, dates (as days since
 * 1970-01-01) and true-or-false values (as 1 and 0), with NaN for empty, and an
 * array of texts. Closures would box every intermediate number and chase a
 * tree of functions; here a number is never boxed, nothing is allocated per
 * evaluation once the stacks are as deep as the deepest formula, and the program
 * is data like the other structures of this layer. The stacks are shared, and
 * each evaluation takes a frame above whatever is running, so a `get` that
 * evaluates another formula — or this one for another record — is safe. Each
 * mention of a field is one call of `get`, made when the formula reaches it.
 * Text results, and functions of text, allocate their strings.
 */

import { civilFromDay, dayFromCivil, daysInMonth, FIRST_DAY, LAST_DAY } from "./civil-days.ts";
export type FormulaType =
  | "number"
  | "text"
  | "boolean"
  | "date"
  | "list-number"
  | "list-text"
  | "list-boolean"
  | "list-date";

/**
 * What a name in a formula stands for: a field's type, or undefined for no
 * such field. Dotted paths (`lines.amount`) name a field of each item of a list
 * of records, typed as a list.
 */
export type FormulaScope = (path: string) => FormulaType | undefined;

export type Compiled =
  | {
      readonly ok: true;
      /** The type of the formula's value; never a list. */
      readonly type: FormulaType;
      /** Every path the formula reads, once each, in the order first mentioned. */
      readonly references: readonly string[];
      /** Whether it reads `today()`, and so changes with the day as well as with its references. */
      readonly volatile: boolean;
      /**
       * The value for the current values of its references, or `null` for none.
       * A number, an ISO date (`YYYY-MM-DD`), a text or a boolean, as `type` says.
       */
      readonly evaluate: (get: (path: string) => unknown) => unknown;
    }
  | { readonly ok: false; readonly message: string; readonly at: number; readonly length: number };

const MAX_LENGTH = 100_000;
const MAX_DEPTH = 64;

// ── Dates ────────────────────────────────────────────────────────────────────
// Days since 1970-01-01 (algorithms/civil-days.ts), so a date is a number on the stack.

// What `civil` found; read straight after it.
const found = [0, 0, 0] as [number, number, number];
let civilYear = 0;
let civilMonth = 0;
let civilDay = 0;

function civil(days: number): void {
  civilFromDay(days, found);
  [civilYear, civilMonth, civilDay] = found;
}

/** The day a year, month and day name, or NaN when they name none. */
function dayOfParts(year: number, month: number, day: number): number {
  if (
    !(year >= 1 && year <= 9999 && month >= 1 && month <= 12 && day >= 1) ||
    year !== Math.floor(year) ||
    month !== Math.floor(month) ||
    day !== Math.floor(day) ||
    day > daysInMonth(year, month)
  ) {
    return Number.NaN;
  }
  return dayFromCivil(year, month, day);
}

function digitsAt(text: string, at: number, count: number): number {
  let value = 0;
  for (let i = at; i < at + count; i += 1) {
    const digit = text.charCodeAt(i) - 48;
    if (!(digit >= 0 && digit <= 9)) return Number.NaN;
    value = value * 10 + digit;
  }
  return value;
}

/** The day an ISO date (`YYYY-MM-DD`) names, or NaN for anything else. */
function dayOfISO(text: string): number {
  if (text.length !== 10 || text.charCodeAt(4) !== 45 || text.charCodeAt(7) !== 45) return Number.NaN;
  return dayOfParts(digitsAt(text, 0, 4), digitsAt(text, 5, 2), digitsAt(text, 8, 2));
}

function isoOf(days: number): string {
  civil(days);
  const month = civilMonth < 10 ? `0${civilMonth}` : `${civilMonth}`;
  const day = civilDay < 10 ? `0${civilDay}` : `${civilDay}`;
  return `${String(civilYear).padStart(4, "0")}-${month}-${day}`;
}

/** A day, or NaN when it is not a whole day an ISO date can write. */
function inRange(days: number): number {
  return days === Math.floor(days) && days >= FIRST_DAY && days <= LAST_DAY ? days : Number.NaN;
}

// ── Today ────────────────────────────────────────────────────────────────────

// NaN: read the clock.
let fixedToday = Number.NaN;
let clockDay = 0;
// The clock's day is kept until the local midnight that ends it.
let clockDayEnds = Number.NEGATIVE_INFINITY;

function today(): number {
  if (fixedToday === fixedToday) return fixedToday;
  if (Date.now() >= clockDayEnds) {
    const now = new Date();
    clockDay = dayFromCivil(now.getFullYear(), now.getMonth() + 1, now.getDate());
    clockDayEnds = new Date(now.getFullYear(), now.getMonth(), now.getDate() + 1).getTime();
  }
  return clockDay;
}

/**
 * Runs `fn` with `today()` fixed at the ISO date `iso`, and returns what it
 * returns. Outside it, `today()` is the local date of the clock.
 */
export function withToday<T>(iso: string, fn: () => T): T {
  const day = dayOfISO(iso);
  if (day !== day) throw new RangeError(`Today must be an ISO date, YYYY-MM-DD, not “${iso}”.`);
  const before = fixedToday;
  fixedToday = day;
  try {
    return fn();
  } finally {
    fixedToday = before;
  }
}

// ── Values ───────────────────────────────────────────────────────────────────

/** Only a finite number less itself is zero. */
const finite = (value: number): number => (value - value === 0 ? value : Number.NaN);

const numberOf = (value: unknown): number => (typeof value === "number" ? finite(value) : Number.NaN);
const dateOf = (value: unknown): number => (typeof value === "string" ? dayOfISO(value) : Number.NaN);
const booleanOf = (value: unknown): number => (value === true ? 1 : value === false ? 0 : Number.NaN);
const textOf = (value: unknown): string => (typeof value === "string" ? value : "");

const POWERS = new Float64Array(16);
for (let i = 0, power = 1; i < 16; i += 1, power *= 10) POWERS[i] = power;

/**
 * Rounded to `digits` decimal places (negative: to tens, hundreds…), halves away
 * from zero, as the number is written: 1.005 is held a hair below itself, and a
 * product within a few units of its last place below a half is that half.
 */
function round(value: number, digits: number): number {
  if (value !== value || !(digits >= -15 && digits <= 15) || digits !== Math.floor(digits)) return Number.NaN;
  const size = value < 0 ? -value : value;
  const unit = POWERS[digits < 0 ? -digits : digits]!;
  const scaled = digits < 0 ? size / unit : size * unit;
  // Past 2^52 a number has no fraction to round at this scale.
  if (!(scaled < 4_503_599_627_370_496)) return value;
  let whole = Math.floor(scaled);
  if (scaled - whole >= 0.5 - scaled * 4 * Number.EPSILON) whole += 1;
  const rounded = digits < 0 ? whole * unit : whole / unit;
  return finite(value < 0 ? -rounded : rounded);
}

const graphemes = new Intl.Segmenter(undefined, { granularity: "grapheme" });

function lengthOf(text: string): number {
  let count = 0;
  for (const _ of graphemes.segment(text)) count += 1;
  return count;
}

/** A number as text: as JavaScript writes it, to fifteen significant digits, so 0.1 + 0.2 is "0.3". */
const textOfNumber = (value: number): string =>
  value === value ? String(Number(value.toPrecision(15))) : "";

// ── Bytecode ─────────────────────────────────────────────────────────────────
// An instruction is an opcode followed by its operands. Each comment says what
// it takes from the stacks and leaves: N the number stack, T the text stack.

const Op = {
  Number: 0, // k            → N: constant k
  Text: 1, // k              → T: string k
  LoadNumber: 2, // p        → N: field p
  LoadDate: 3, // p          → N
  LoadBoolean: 4, // p       → N
  LoadText: 5, // p          → T
  LoadList: 6, // p item agg   never run: a list is only an aggregate's argument, and becomes:
  AggregateList: 7, // p item agg   folds every item of field p into the accumulator
  Today: 8, //               → N
  Add: 9, // N N → N
  Subtract: 10,
  Multiply: 11,
  Divide: 12,
  Remainder: 13,
  Negate: 14, // N → N
  DateAdd: 15, // N N → N: a date and days, either way round
  DateSubtract: 16, // N N → N: a date less days
  DaysBetween: 17, // N N → N: the first date less the second
  DaysTo: 18, // N N → N: the second date less the first
  Equal: 19, // N N → N
  NotEqual: 20,
  Less: 21,
  LessEqual: 22,
  Greater: 23,
  GreaterEqual: 24,
  TextEqual: 25, // T T → N
  TextNotEqual: 26,
  TextLess: 27,
  TextLessEqual: 28,
  TextGreater: 29,
  TextGreaterEqual: 30,
  Concat: 31, // T T → T
  And: 32, // N N → N
  Or: 33,
  Not: 34, // N → N
  Select: 35, // N N N → N: question, yes, no
  SelectText: 36, // N, T T → T
  Round: 37, // N N → N
  Floor: 38, // N → N
  Ceil: 39,
  Abs: 40,
  Length: 41, // T → N
  Upper: 42, // T → T
  Lower: 43,
  Trim: 44,
  Year: 45, // N → N
  Month: 46,
  Day: 47,
  MakeDate: 48, // N N N → N
  Coalesce: 49, // N N → N
  CoalesceText: 50, // T T → T
  NumberText: 51, // N → T
  DateText: 52,
  BooleanText: 53,
  IsEmpty: 54, // N → N
  IsEmptyText: 55, // T → N
  PresentText: 56, // T → N: 1, or empty for ""
  Begin: 57, // agg → N N: an accumulator and a count
  Step: 58, // agg   N N N → N N
  End: 59, // agg    N N → N
} as const;

const Aggregate = {
  Sum: 0,
  Average: 1,
  Min: 2,
  Max: 3,
  Count: 4,
} as const;

const Item = {
  Number: 0,
  Date: 1,
  Boolean: 2,
  Text: 3,
} as const;

interface Program {
  readonly code: Int32Array;
  readonly constants: Float64Array;
  readonly strings: readonly string[];
  readonly paths: readonly string[];
  readonly type: FormulaType;
  readonly numberDepth: number;
  readonly textDepth: number;
}

// The stacks every evaluation shares. An evaluation's frame starts at the tops
// it finds and ends at its program's depth beyond them; one started from within
// `get` builds above it.
let numbers = new Float64Array(256);
const texts: string[] = [];
let numberTop = 0;
let textTop = 0;

function fold(stack: Float64Array, top: number, kind: number, value: number): void {
  if (value !== value) return;
  stack[top]! += 1;
  const held = stack[top - 1]!;
  if (kind === Aggregate.Sum || kind === Aggregate.Average) stack[top - 1] = held + value;
  else if (kind === Aggregate.Min) stack[top - 1] = value < held ? value : held;
  else if (kind === Aggregate.Max) stack[top - 1] = value > held ? value : held;
}

function itemOf(kind: number, item: unknown): number {
  if (kind === Item.Number) return numberOf(item);
  if (kind === Item.Date) return dateOf(item);
  if (kind === Item.Boolean) return booleanOf(item);
  return typeof item === "string" && item !== "" ? 1 : Number.NaN;
}

const compare = (left: number, right: number, answer: boolean): number =>
  left !== left || right !== right ? Number.NaN : answer ? 1 : 0;

function run(program: Program, get: (path: string) => unknown): unknown {
  const { code, constants, strings, paths } = program;
  const numberBase = numberTop;
  const textBase = textTop;
  if (numberBase + program.numberDepth > numbers.length) {
    // Frames below keep the array they began with, so nothing need be copied.
    numbers = new Float64Array(Math.max(numbers.length * 2, numberBase + program.numberDepth));
  }
  while (texts.length < textBase + program.textDepth) texts.push("");
  numberTop = numberBase + program.numberDepth;
  textTop = textBase + program.textDepth;
  const stack = numbers;
  let n = numberBase - 1;
  let t = textBase - 1;
  try {
    for (let pc = 0; pc < code.length;) {
      const op = code[pc]!;
      switch (op) {
        case Op.Number:
          stack[++n] = constants[code[pc + 1]!]!;
          pc += 2;
          break;
        case Op.Text:
          texts[++t] = strings[code[pc + 1]!]!;
          pc += 2;
          break;
        case Op.LoadNumber:
          stack[++n] = numberOf(get(paths[code[pc + 1]!]!));
          pc += 2;
          break;
        case Op.LoadDate:
          stack[++n] = dateOf(get(paths[code[pc + 1]!]!));
          pc += 2;
          break;
        case Op.LoadBoolean:
          stack[++n] = booleanOf(get(paths[code[pc + 1]!]!));
          pc += 2;
          break;
        case Op.LoadText:
          texts[++t] = textOf(get(paths[code[pc + 1]!]!));
          pc += 2;
          break;
        case Op.AggregateList: {
          const list = get(paths[code[pc + 1]!]!);
          if (Array.isArray(list)) {
            const item = code[pc + 2]!;
            const kind = code[pc + 3]!;
            for (let i = 0; i < list.length; i += 1) fold(stack, n, kind, itemOf(item, list[i]));
          }
          pc += 4;
          break;
        }
        case Op.Today:
          stack[++n] = today();
          pc += 1;
          break;
        case Op.Negate:
          stack[n] = -stack[n]!;
          pc += 1;
          break;
        case Op.Not: {
          const value = stack[n]!;
          stack[n] = value === value ? 1 - value : value;
          pc += 1;
          break;
        }
        case Op.Floor:
          stack[n] = Math.floor(stack[n]!);
          pc += 1;
          break;
        case Op.Ceil:
          stack[n] = Math.ceil(stack[n]!);
          pc += 1;
          break;
        case Op.Abs:
          stack[n] = Math.abs(stack[n]!);
          pc += 1;
          break;
        case Op.Year:
        case Op.Month:
        case Op.Day: {
          const value = stack[n]!;
          if (value === value) {
            civil(value);
            stack[n] = op === Op.Year ? civilYear : op === Op.Month ? civilMonth : civilDay;
          }
          pc += 1;
          break;
        }
        case Op.IsEmpty: {
          const value = stack[n]!;
          stack[n] = value === value ? 0 : 1;
          pc += 1;
          break;
        }
        case Op.Length:
          stack[++n] = lengthOf(texts[t--]!);
          pc += 1;
          break;
        case Op.IsEmptyText:
          stack[++n] = texts[t--] === "" ? 1 : 0;
          pc += 1;
          break;
        case Op.PresentText:
          stack[++n] = texts[t--] === "" ? Number.NaN : 1;
          pc += 1;
          break;
        case Op.Upper:
          texts[t] = texts[t]!.toUpperCase();
          pc += 1;
          break;
        case Op.Lower:
          texts[t] = texts[t]!.toLowerCase();
          pc += 1;
          break;
        case Op.Trim:
          texts[t] = texts[t]!.trim();
          pc += 1;
          break;
        case Op.NumberText:
          texts[++t] = textOfNumber(stack[n--]! + 0);
          pc += 1;
          break;
        case Op.DateText: {
          const value = stack[n--]!;
          texts[++t] = value === value ? isoOf(value) : "";
          pc += 1;
          break;
        }
        case Op.BooleanText: {
          const value = stack[n--]!;
          texts[++t] = value === value ? (value === 1 ? "true" : "false") : "";
          pc += 1;
          break;
        }
        case Op.Concat:
          t -= 1;
          texts[t] = texts[t]! + texts[t + 1]!;
          pc += 1;
          break;
        case Op.CoalesceText:
          t -= 1;
          if (texts[t] === "") texts[t] = texts[t + 1]!;
          pc += 1;
          break;
        case Op.TextEqual:
        case Op.TextNotEqual:
        case Op.TextLess:
        case Op.TextLessEqual:
        case Op.TextGreater:
        case Op.TextGreaterEqual: {
          const right = texts[t--]!;
          const left = texts[t--]!;
          const answer =
            op === Op.TextEqual
              ? left === right
              : op === Op.TextNotEqual
                ? left !== right
                : op === Op.TextLess
                  ? left < right
                  : op === Op.TextLessEqual
                    ? left <= right
                    : op === Op.TextGreater
                      ? left > right
                      : left >= right;
          stack[++n] = answer ? 1 : 0;
          pc += 1;
          break;
        }
        case Op.Select: {
          n -= 2;
          const question = stack[n]!;
          stack[n] = question === question ? (question === 1 ? stack[n + 1]! : stack[n + 2]!) : question;
          pc += 1;
          break;
        }
        case Op.SelectText: {
          const question = stack[n--]!;
          t -= 1;
          texts[t] = question === question ? (question === 1 ? texts[t]! : texts[t + 1]!) : "";
          pc += 1;
          break;
        }
        case Op.MakeDate:
          n -= 2;
          stack[n] = dayOfParts(stack[n]!, stack[n + 1]!, stack[n + 2]!);
          pc += 1;
          break;
        case Op.Begin:
          stack[++n] =
            code[pc + 1] === Aggregate.Min
              ? Number.POSITIVE_INFINITY
              : code[pc + 1] === Aggregate.Max
                ? Number.NEGATIVE_INFINITY
                : 0;
          stack[++n] = 0;
          pc += 2;
          break;
        case Op.Step:
          n -= 1;
          fold(stack, n, code[pc + 1]!, stack[n + 1]!);
          pc += 2;
          break;
        case Op.End: {
          const kind = code[pc + 1]!;
          n -= 1;
          const held = stack[n]!;
          const count = stack[n + 1]!;
          stack[n] =
            kind === Aggregate.Count
              ? count
              : kind === Aggregate.Sum
                ? finite(held)
                : count === 0
                  ? Number.NaN
                  : kind === Aggregate.Average
                    ? finite(held / count)
                    : held;
          pc += 2;
          break;
        }
        default: {
          // Every other operation takes two numbers and leaves one.
          n -= 1;
          const left = stack[n]!;
          const right = stack[n + 1]!;
          let value: number;
          switch (op) {
            case Op.Add:
              value = finite(left + right);
              break;
            case Op.Subtract:
              value = finite(left - right);
              break;
            case Op.Multiply:
              value = finite(left * right);
              break;
            case Op.Divide:
              value = finite(left / right);
              break;
            case Op.Remainder:
              value = finite(left - right * Math.floor(left / right));
              break;
            case Op.DateAdd:
              value = inRange(left + right);
              break;
            case Op.DateSubtract:
              value = inRange(left - right);
              break;
            case Op.DaysBetween:
              value = left - right;
              break;
            case Op.DaysTo:
              value = right - left;
              break;
            case Op.Equal:
              value = compare(left, right, left === right);
              break;
            case Op.NotEqual:
              value = compare(left, right, left !== right);
              break;
            case Op.Less:
              value = compare(left, right, left < right);
              break;
            case Op.LessEqual:
              value = compare(left, right, left <= right);
              break;
            case Op.Greater:
              value = compare(left, right, left > right);
              break;
            case Op.GreaterEqual:
              value = compare(left, right, left >= right);
              break;
            case Op.And:
              value = left === 0 || right === 0 ? 0 : left !== left || right !== right ? Number.NaN : 1;
              break;
            case Op.Or:
              value = left === 1 || right === 1 ? 1 : left !== left || right !== right ? Number.NaN : 0;
              break;
            case Op.Round:
              value = round(left, right);
              break;
            case Op.Coalesce:
              value = left === left ? left : right;
              break;
            default:
              value = Number.NaN;
          }
          stack[n] = value;
          pc += 1;
        }
      }
    }
    switch (program.type) {
      case "text": {
        const text = texts[textBase]!;
        return text === "" ? null : text;
      }
      case "date": {
        const value = stack[numberBase]!;
        return value === value ? isoOf(value) : null;
      }
      case "boolean": {
        const value = stack[numberBase]!;
        return value === value ? value === 1 : null;
      }
      default: {
        const value = stack[numberBase]!;
        // Adding zero makes a negative zero positive, and changes nothing else.
        return value === value ? value + 0 : null;
      }
    }
  } finally {
    // The texts are not kept alive for the sake of the next evaluation.
    for (let i = textBase; i < textTop; i += 1) texts[i] = "";
    numberTop = numberBase;
    textTop = textBase;
  }
}

// ── Compiling ────────────────────────────────────────────────────────────────

const Token = {
  End: 0,
  Number: 1,
  Text: 2,
  Name: 3,
  Open: 4,
  Close: 5,
  Comma: 6,
  Plus: 7,
  Minus: 8,
  Times: 9,
  Divide: 10,
  Remainder: 11,
  Ampersand: 12,
  Equal: 13,
  NotEqual: 14,
  Less: 15,
  LessEqual: 16,
  Greater: 17,
  GreaterEqual: 18,
} as const;

/** What part of a formula leaves on the stack, and where it was written. */
interface Operand {
  readonly type: FormulaType;
  readonly start: number;
  readonly end: number;
  /** Where its load was emitted, for a list: an aggregate turns it into a fold. */
  readonly list?: number;
}

class Refusal {
  readonly message: string;
  readonly at: number;
  readonly length: number;

  constructor(message: string, at: number, length: number) {
    this.message = message;
    this.at = at;
    this.length = length;
  }
}

const DESCRIBED: Readonly<Record<FormulaType, string>> = {
  number: "a number",
  text: "text",
  boolean: "true or false",
  date: "a date",
  "list-number": "a list of numbers",
  "list-text": "a list of texts",
  "list-boolean": "a list of true-or-false values",
  "list-date": "a list of dates",
};

const isList = (type: FormulaType): boolean => type.startsWith("list-");
const onNumberStack = (type: FormulaType): boolean => type !== "text";

const WORDS = ["none", "one", "two", "three"];
const counted = (count: number, noun: string): string =>
  count === 0 ? `no ${noun}s` : `${WORDS[count] ?? count} ${noun}${count === 1 ? "" : "s"}`;

const IDENTIFIER_START = /[\p{L}\p{Nl}]/u;
const IDENTIFIER_PART = /[\p{L}\p{Nl}\p{M}\p{Nd}]/u;

interface Definition {
  readonly min: number;
  readonly max: number;
  /** Before the arguments. */
  readonly begin?: (c: Compiler) => void;
  /** After each argument within `max`, as it is read. */
  readonly each?: (c: Compiler, argument: Operand, index: number, args: readonly Operand[]) => void;
  /** After the arguments, given that their number is right: the type it leaves. */
  readonly end: (c: Compiler, args: readonly Operand[]) => FormulaType;
}

class Compiler {
  // The token just read.
  kind: number = Token.End;
  start = -1;
  end = -1;
  value = 0;
  word = "";
  /** A single name, not in backticks: one that may be a word of the language or a function. */
  plain = false;
  /** The character after the name just read. */
  following = 0;
  // The token before it, to say what something is missing after.
  previousStart = -1;
  previousEnd = -1;
  position = 0;
  depth = 0;

  readonly code: number[] = [];
  readonly constants: number[] = [];
  readonly strings: string[] = [];
  readonly paths: string[] = [];
  readonly indices = new Map<string, number>();
  numberDepth = 0;
  textDepth = 0;
  maxNumberDepth = 0;
  maxTextDepth = 0;
  volatile = false;

  readonly source: string;
  readonly scope: FormulaScope;

  constructor(source: string, scope: FormulaScope) {
    this.source = source;
    this.scope = scope;
  }

  fail(message: string, at: number, length: number): never {
    throw new Refusal(message, at, length);
  }

  // ── Emitting

  /** Appends an instruction that changes the stacks' depths by `numberCount` and `textCount`. */
  emit(numberCount: number, textCount: number, ...words: number[]): void {
    for (const word of words) this.code.push(word);
    this.numberDepth += numberCount;
    this.textDepth += textCount;
    if (this.numberDepth > this.maxNumberDepth) this.maxNumberDepth = this.numberDepth;
    if (this.textDepth > this.maxTextDepth) this.maxTextDepth = this.textDepth;
  }

  // ── Reading tokens

  isSpace(code: number): boolean {
    return code === 32 || code === 9 || code === 10 || code === 13 || code === 0xa0 || code === 0x3000;
  }

  /**
   * The length of the identifier character `code` at `at` — 2 for a pair of
   * surrogates — or 0 if it is not one. Only a surrogate's partner is read.
   */
  identifierAt(code: number, at: number, first: boolean): number {
    if ((code >= 65 && code <= 90) || (code >= 97 && code <= 122) || code === 95) return 1;
    if (code >= 48 && code <= 57) return first ? 0 : 1;
    if (!(code >= 128)) return 0;
    let point = code;
    let length = 1;
    if (code >= 0xd800 && code <= 0xdbff) {
      const low = this.source.charCodeAt(at + 1);
      if (low >= 0xdc00 && low <= 0xdfff) {
        point = (code - 0xd800) * 0x400 + (low - 0xdc00) + 0x10000;
        length = 2;
      }
    }
    const character = String.fromCodePoint(point);
    return (first ? IDENTIFIER_START : IDENTIFIER_PART).test(character) ? length : 0;
  }

  /** A name in backticks from the backtick at `position`, unescaped. */
  quoted(): string {
    const { source } = this;
    const open = this.position;
    let at = open + 1;
    let name = "";
    let from = at;
    for (;;) {
      if (at >= source.length) this.fail("This name is never closed with a backtick.", open, at - open);
      const code = source.charCodeAt(at);
      if (code === 96) break;
      if (code === 92) {
        const next = source.charCodeAt(at + 1);
        if (next !== 96 && next !== 92) {
          this.fail("Within backticks, a backslash comes only before a backtick or a backslash.", at, 2);
        }
        name += source.slice(from, at);
        from = at + 1;
        at += 2;
      } else at += 1;
    }
    name += source.slice(from, at);
    if (name === "") this.fail("A name in backticks cannot be empty.", open, at + 1 - open);
    this.position = at + 1;
    this.following = source.charCodeAt(this.position);
    return name;
  }

  /**
   * One name of a path, whose first character `code` is at `position`: its
   * text, or undefined when there is none there. `following` is left as the
   * character after it.
   */
  segment(code: number): string | undefined {
    if (code === 96) return this.quoted();
    const { source } = this;
    const from = this.position;
    let length = this.identifierAt(code, from, true);
    if (length === 0) return undefined;
    let at = from;
    let next = code;
    while (length > 0) {
      at += length;
      next = source.charCodeAt(at);
      length = this.identifierAt(next, at, false);
    }
    this.position = at;
    this.following = next;
    return source.slice(from, at);
  }

  advance(): void {
    this.previousStart = this.start;
    this.previousEnd = this.end;
    const { source } = this;
    let at = this.position;
    // Each character is read once here, and once more by what reads its token.
    let code = source.charCodeAt(at);
    while (this.isSpace(code)) code = source.charCodeAt(++at);
    this.start = at;
    this.position = at;
    let kind: number;
    if (at >= source.length) kind = Token.End;
    else if (this.isDigit(code) || (code === 46 && this.isDigit(source.charCodeAt(at + 1)))) {
      let end = at;
      let next = code;
      while (this.isDigit(next)) next = source.charCodeAt(++end);
      if (next === 46) {
        next = source.charCodeAt(++end);
        while (this.isDigit(next)) next = source.charCodeAt(++end);
      }
      this.value = Number(source.slice(at, end));
      this.position = end;
      if (this.value - this.value !== 0) this.fail("This number is too large to hold.", at, end - at);
      kind = Token.Number;
    } else if (code === 34) {
      kind = Token.Text;
      this.word = this.text();
    } else if (code === 96 || this.identifierAt(code, at, true) > 0) {
      kind = Token.Name;
      let path = this.segment(code)!;
      this.plain = code !== 96;
      while (this.following === 46) {
        const dot = this.position;
        this.position += 1;
        const next = this.segment(source.charCodeAt(this.position));
        if (next === undefined) this.fail("A field's name is missing after this point.", dot, 1);
        path += `.${next}`;
        this.plain = false;
      }
      this.word = path;
    } else {
      let length = 1;
      switch (code) {
        case 40:
          kind = Token.Open;
          break;
        case 41:
          kind = Token.Close;
          break;
        case 44:
          kind = Token.Comma;
          break;
        case 43:
          kind = Token.Plus;
          break;
        case 45:
          kind = Token.Minus;
          break;
        case 42:
          kind = Token.Times;
          break;
        case 47:
          kind = Token.Divide;
          break;
        case 37:
          kind = Token.Remainder;
          break;
        case 38:
          kind = Token.Ampersand;
          break;
        case 61:
          kind = Token.Equal;
          break;
        case 60: {
          const next = source.charCodeAt(at + 1);
          kind = next === 62 ? Token.NotEqual : next === 61 ? Token.LessEqual : Token.Less;
          if (kind !== Token.Less) length = 2;
          break;
        }
        case 62:
          kind = source.charCodeAt(at + 1) === 61 ? Token.GreaterEqual : Token.Greater;
          if (kind !== Token.Greater) length = 2;
          break;
        default: {
          const next = source.charCodeAt(at + 1);
          const wide = code >= 0xd800 && code <= 0xdbff && next >= 0xdc00 && next <= 0xdfff ? 2 : 1;
          this.fail(`“${source.slice(at, at + wide)}” cannot be read here.`, at, wide);
        }
      }
      this.position = at + length;
    }
    this.kind = kind;
    this.end = this.position;
  }

  // A method, so that what a call has read since is not narrowed away.
  at(kind: number): boolean {
    return this.kind === kind;
  }

  isDigit(code: number): boolean {
    return code >= 48 && code <= 57;
  }

  /** A text in quotation marks from the one at `position`, unescaped. */
  text(): string {
    const { source } = this;
    const open = this.position;
    let at = open + 1;
    let text = "";
    let from = at;
    for (;;) {
      if (at >= source.length) this.fail("This text is never closed with a quotation mark.", open, at - open);
      const code = source.charCodeAt(at);
      if (code === 34) break;
      if (code === 92) {
        const next = source.charCodeAt(at + 1);
        if (next !== 34 && next !== 92) {
          this.fail("Within a text, a backslash comes only before a quotation mark or a backslash.", at, 2);
        }
        text += source.slice(from, at);
        from = at + 1;
        at += 2;
      } else at += 1;
    }
    text += source.slice(from, at);
    this.position = at + 1;
    return text;
  }

  /** Whether the token is the word `word`, in any case. */
  is(word: string): boolean {
    return this.kind === Token.Name && this.plain && this.word.toLowerCase() === word;
  }

  // ── Types

  need(operand: Operand, accepted: (type: FormulaType) => boolean, message: string): void {
    if (!accepted(operand.type)) {
      this.fail(
        `${message}; this is ${DESCRIBED[operand.type]}.`,
        operand.start,
        operand.end - operand.start,
      );
    }
  }

  scalar(operand: Operand, message: string): void {
    this.need(operand, (type) => !isList(type), message);
  }

  /** `right` must be of `left`'s type. */
  same(left: Operand, right: Operand, message: string): void {
    if (left.type !== right.type) {
      this.fail(
        `${message}: this is ${DESCRIBED[right.type]}, the other ${DESCRIBED[left.type]}.`,
        right.start,
        right.end - right.start,
      );
    }
  }

  // ── Grammar

  formula(): Operand {
    this.advance();
    if (this.kind === Token.End) this.fail("A formula needs something to compute.", 0, this.source.length);
    const result = this.or();
    if (this.kind !== Token.End) this.unexpected();
    this.scalar(
      result,
      "A formula gives a single value, which sum, average, min, max or count make of a list",
    );
    return result;
  }

  /** A token where an operator, or the end, was expected. */
  unexpected(): never {
    const { start, end } = this;
    if (this.kind === Token.Close) this.fail("This parenthesis closes nothing.", start, 1);
    if (this.kind === Token.Comma) {
      this.fail("A comma separates what is given to a function, and this is outside one.", start, 1);
    }
    this.fail(`An operator is missing before “${this.source.slice(start, end)}”.`, start, end - start);
  }

  binary(left: Operand, right: Operand, type: FormulaType): Operand {
    return { type, start: left.start, end: right.end };
  }

  or(): Operand {
    let left = this.and();
    while (this.is("or")) {
      this.advance();
      const right = this.and();
      this.need(left, (type) => type === "boolean", "“or” joins values that are true or false");
      this.need(right, (type) => type === "boolean", "“or” joins values that are true or false");
      this.emit(-1, 0, Op.Or);
      left = this.binary(left, right, "boolean");
    }
    return left;
  }

  and(): Operand {
    let left = this.not();
    while (this.is("and")) {
      this.advance();
      const right = this.not();
      this.need(left, (type) => type === "boolean", "“and” joins values that are true or false");
      this.need(right, (type) => type === "boolean", "“and” joins values that are true or false");
      this.emit(-1, 0, Op.And);
      left = this.binary(left, right, "boolean");
    }
    return left;
  }

  not(): Operand {
    const start = this.start;
    let count = 0;
    // Counted, not descended into: there may be any number of them.
    while (this.is("not")) {
      count += 1;
      this.advance();
    }
    const operand = this.compare();
    if (count === 0) return operand;
    this.need(operand, (type) => type === "boolean", "“not” turns about a value that is true or false");
    if (count % 2 === 1) this.emit(0, 0, Op.Not);
    return { type: "boolean", start, end: operand.end };
  }

  compare(): Operand {
    let left = this.concat();
    for (;;) {
      const kind = this.kind;
      if (kind < Token.Equal || kind > Token.GreaterEqual) return left;
      const operator = this.source.slice(this.start, this.end);
      const at = this.start;
      this.advance();
      const right = this.concat();
      this.scalar(left, `“${operator}” compares single values`);
      this.scalar(right, `“${operator}” compares single values`);
      this.same(left, right, `“${operator}” compares values of one kind`);
      const offset = kind - Token.Equal;
      if (left.type === "text") this.emit(1, -2, Op.TextEqual + offset);
      else {
        if (left.type === "boolean" && offset > 1) {
          this.fail(
            `“${operator}” orders numbers, dates and texts; true or false compare only with = and <>.`,
            at,
            operator.length,
          );
        }
        this.emit(-1, 0, Op.Equal + offset);
      }
      left = this.binary(left, right, "boolean");
    }
  }

  concat(): Operand {
    let left = this.sum();
    while (this.kind === Token.Ampersand) {
      this.advance();
      const right = this.sum();
      const message = "“&” joins texts — text(…) writes anything else as one";
      this.need(left, (type) => type === "text", message);
      this.need(right, (type) => type === "text", message);
      this.emit(0, -1, Op.Concat);
      left = this.binary(left, right, "text");
    }
    return left;
  }

  sum(): Operand {
    let left = this.product();
    while (this.kind === Token.Plus || this.kind === Token.Minus) {
      const plus = this.kind === Token.Plus;
      this.advance();
      const right = this.product();
      const message = plus ? "“+” adds numbers, or days to a date" : "“-” subtracts numbers, or from a date";
      this.need(left, (type) => type === "number" || type === "date", message);
      let result: FormulaType;
      if (left.type === "number") {
        this.need(right, (type) => type === "number" || (plus && type === "date"), message);
        result = right.type;
        this.emit(-1, 0, result === "date" ? Op.DateAdd : plus ? Op.Add : Op.Subtract);
      } else {
        this.need(right, (type) => type === "number" || (!plus && type === "date"), message);
        result = plus || right.type === "number" ? "date" : "number";
        this.emit(-1, 0, plus ? Op.DateAdd : right.type === "number" ? Op.DateSubtract : Op.DaysBetween);
      }
      left = this.binary(left, right, result);
    }
    return left;
  }

  product(): Operand {
    let left = this.unary();
    for (;;) {
      const kind = this.kind;
      if (kind !== Token.Times && kind !== Token.Divide && kind !== Token.Remainder) return left;
      this.advance();
      const right = this.unary();
      const message =
        kind === Token.Times
          ? "“*” multiplies numbers"
          : kind === Token.Divide
            ? "“/” divides numbers"
            : "“%” is the remainder of numbers";
      this.need(left, (type) => type === "number", message);
      this.need(right, (type) => type === "number", message);
      this.emit(-1, 0, kind === Token.Times ? Op.Multiply : kind === Token.Divide ? Op.Divide : Op.Remainder);
      left = this.binary(left, right, "number");
    }
  }

  unary(): Operand {
    const start = this.start;
    let signs = 0;
    let negative = false;
    // Counted, not descended into, as `not` is.
    while (this.kind === Token.Plus || this.kind === Token.Minus) {
      if (this.kind === Token.Minus) negative = !negative;
      signs += 1;
      this.advance();
    }
    const operand = this.primary();
    if (signs === 0) return operand;
    this.need(operand, (type) => type === "number", "A sign goes before a number");
    if (negative) this.emit(0, 0, Op.Negate);
    return { type: "number", start, end: operand.end };
  }

  /** Something to compute was expected at the token. */
  missing(): never {
    if (this.previousStart < 0) {
      this.fail(
        `Something to compute is missing before “${this.source.slice(this.start, this.end)}”.`,
        this.start,
        this.end - this.start,
      );
    }
    this.fail(
      `Something to compute is missing after “${this.source.slice(this.previousStart, this.previousEnd)}”.`,
      this.previousStart,
      this.previousEnd - this.previousStart,
    );
  }

  primary(): Operand {
    const { start, end } = this;
    switch (this.kind) {
      case Token.Number:
        this.emit(1, 0, Op.Number, this.constants.length);
        this.constants.push(this.value);
        this.advance();
        return { type: "number", start, end };
      case Token.Text:
        this.emit(0, 1, Op.Text, this.strings.length);
        this.strings.push(this.word);
        this.advance();
        return { type: "text", start, end };
      case Token.Open: {
        if (this.depth === MAX_DEPTH) this.fail(`Formulas nest at most ${MAX_DEPTH} deep.`, start, 1);
        this.depth += 1;
        this.advance();
        const inner = this.or();
        if (!this.at(Token.Close)) {
          if (this.at(Token.End)) this.fail("This parenthesis is never closed.", start, 1);
          this.fail(
            `An operator or a closing parenthesis is missing before “${this.source.slice(this.start, this.end)}”.`,
            this.start,
            this.end - this.start,
          );
        }
        this.depth -= 1;
        const close = this.end;
        this.advance();
        return { ...inner, start, end: close };
      }
      case Token.Name:
        return this.name();
      default:
        return this.missing();
    }
  }

  name(): Operand {
    const { start, end, word, plain } = this;
    const lower = plain ? word.toLowerCase() : "";
    if (lower === "true" || lower === "false") {
      this.emit(1, 0, Op.Number, this.constants.length);
      this.constants.push(lower === "true" ? 1 : 0);
      this.advance();
      return { type: "boolean", start, end };
    }
    if (lower === "and" || lower === "or" || lower === "not") {
      if (this.scope(word) !== undefined) {
        this.fail(`A field called “${word}” is written in backticks: \`${word}\`.`, start, end - start);
      }
      if (lower === "not")
        this.fail("Put “not” and what it turns about in parentheses here.", start, end - start);
      this.missing();
    }
    this.advance();
    if (plain && this.kind === Token.Open) return this.call(word, lower, start, end);
    const type = this.scope(word);
    if (type === undefined) this.fail(`There is no field “${word}”.`, start, end - start);
    let index = this.indices.get(word);
    if (index === undefined) {
      index = this.paths.push(word) - 1;
      this.indices.set(word, index);
    }
    if (isList(type)) {
      const at = this.code.length;
      this.emit(0, 0, Op.LoadList, index, ITEMS[type]!, 0);
      return { type, start, end, list: at };
    }
    if (type === "text") this.emit(0, 1, Op.LoadText, index);
    else
      this.emit(
        1,
        0,
        type === "date" ? Op.LoadDate : type === "boolean" ? Op.LoadBoolean : Op.LoadNumber,
        index,
      );
    return { type, start, end };
  }

  call(written: string, name: string, start: number, nameEnd: number): Operand {
    const definition = FUNCTIONS.get(name);
    if (definition === undefined) this.fail(`There is no function “${written}”.`, start, nameEnd - start);
    if (this.depth === MAX_DEPTH)
      this.fail(`Formulas nest at most ${MAX_DEPTH} deep.`, start, nameEnd - start);
    this.depth += 1;
    this.advance();
    definition.begin?.(this);
    const args: Operand[] = [];
    if (this.kind !== Token.Close) {
      for (;;) {
        const argument = this.or();
        args.push(argument);
        if (args.length <= definition.max) definition.each?.(this, argument, args.length - 1, args);
        if (this.kind !== Token.Comma) break;
        this.advance();
      }
    }
    if (this.kind !== Token.Close) {
      if (this.kind === Token.End)
        this.fail(`This call of ${name} is never closed.`, start, nameEnd + 1 - start);
      this.fail(
        `An operator or a comma is missing before “${this.source.slice(this.start, this.end)}”.`,
        this.start,
        this.end - this.start,
      );
    }
    this.depth -= 1;
    const end = this.end;
    if (args.length < definition.min || args.length > definition.max) {
      const { min, max } = definition;
      const takes =
        max === Infinity
          ? `at least ${counted(min, "value")}`
          : min === max
            ? counted(min, "value")
            : `${WORDS[min]} or ${counted(max, "value")}`;
      this.fail(
        `${name} takes ${takes}; this gives ${WORDS[args.length] ?? args.length}.`,
        start,
        end - start,
      );
    }
    const type = definition.end(this, args);
    this.advance();
    return { type, start, end };
  }
}

const ITEMS: Readonly<Record<string, number>> = {
  "list-number": Item.Number,
  "list-date": Item.Date,
  "list-boolean": Item.Boolean,
  "list-text": Item.Text,
};

const numeric = (type: FormulaType) => type === "number";
const textual = (type: FormulaType) => type === "text";
const dated = (type: FormulaType) => type === "date";

/** A function of one value of a type, as one operation. */
function unary(
  accepts: (type: FormulaType) => boolean,
  what: string,
  op: number,
  gives: FormulaType,
): Definition {
  const numberIn = accepts("text") ? 0 : 1;
  const numberOut = gives === "text" ? 0 : 1;
  return {
    min: 1,
    max: 1,
    each: (c, argument) => c.need(argument, accepts, what),
    end: (c) => {
      c.emit(numberOut - numberIn, 1 - numberIn - numberOut, op);
      return gives;
    },
  };
}

/** sum, average, min, max and count: any mix of values and lists, folded one by one. */
function aggregate(kind: number, accepts: (type: FormulaType) => boolean, what: string): Definition {
  return {
    min: 1,
    max: Infinity,
    begin: (c) => c.emit(2, 0, Op.Begin, kind),
    each: (c, argument, index, args) => {
      const first = args[0]!;
      c.need(argument, accepts, what);
      if (index > 0 && (kind === Aggregate.Min || kind === Aggregate.Max)) {
        // Numbers with numbers, dates with dates.
        const element = (type: FormulaType) => type.replace("list-", "");
        if (element(argument.type) !== element(first.type)) {
          c.fail(
            `${what}, all of one kind: this is ${DESCRIBED[argument.type]}, the first ${DESCRIBED[first.type]}.`,
            argument.start,
            argument.end - argument.start,
          );
        }
      }
      if (argument.list !== undefined) {
        c.code[argument.list] = Op.AggregateList;
        c.code[argument.list + 3] = kind;
      } else {
        if (argument.type === "text") c.emit(1, -1, Op.PresentText);
        c.emit(-1, 0, Op.Step, kind);
      }
    },
    end: (c, args) => {
      c.emit(-1, 0, Op.End, kind);
      if (kind === Aggregate.Min || kind === Aggregate.Max)
        return args[0]!.type.replace("list-", "") as FormulaType;
      return "number";
    },
  };
}

const FUNCTIONS: ReadonlyMap<string, Definition> = new Map<string, Definition>([
  [
    "if",
    {
      min: 3,
      max: 3,
      each: (c, argument, index, args) => {
        if (index === 0)
          c.need(argument, (type) => type === "boolean", "if asks a question that is true or false");
        else c.scalar(argument, "if gives a single value");
        if (index === 2) c.same(args[1]!, argument, "Both answers of if are of one kind");
      },
      end: (c, args) => {
        const yes = args[1]!;
        if (yes.type === "text") c.emit(-1, -1, Op.SelectText);
        else c.emit(-2, 0, Op.Select);
        return yes.type;
      },
    },
  ],
  [
    "round",
    {
      min: 1,
      max: 2,
      each: (c, argument, index) =>
        c.need(argument, numeric, index === 0 ? "round takes a number" : "round takes a number of digits"),
      end: (c, args) => {
        if (args.length === 1) {
          c.emit(1, 0, Op.Number, c.constants.length);
          c.constants.push(0);
        }
        c.emit(-1, 0, Op.Round);
        return "number";
      },
    },
  ],
  ["floor", unary(numeric, "floor takes a number", Op.Floor, "number")],
  ["ceil", unary(numeric, "ceil takes a number", Op.Ceil, "number")],
  ["abs", unary(numeric, "abs takes a number", Op.Abs, "number")],
  ["len", unary(textual, "len counts the characters of text", Op.Length, "number")],
  ["upper", unary(textual, "upper takes text", Op.Upper, "text")],
  ["lower", unary(textual, "lower takes text", Op.Lower, "text")],
  ["trim", unary(textual, "trim takes text", Op.Trim, "text")],
  ["year", unary(dated, "year takes a date", Op.Year, "number")],
  ["month", unary(dated, "month takes a date", Op.Month, "number")],
  ["day", unary(dated, "day takes a date", Op.Day, "number")],
  [
    "sum",
    aggregate(Aggregate.Sum, (type) => type === "number" || type === "list-number", "sum adds numbers"),
  ],
  [
    "average",
    aggregate(
      Aggregate.Average,
      (type) => type === "number" || type === "list-number",
      "average takes numbers",
    ),
  ],
  [
    "min",
    aggregate(
      Aggregate.Min,
      (type) => type === "number" || type === "date" || type === "list-number" || type === "list-date",
      "min compares numbers or dates",
    ),
  ],
  [
    "max",
    aggregate(
      Aggregate.Max,
      (type) => type === "number" || type === "date" || type === "list-number" || type === "list-date",
      "max compares numbers or dates",
    ),
  ],
  ["count", aggregate(Aggregate.Count, () => true, "count counts values")],
  [
    "today",
    {
      min: 0,
      max: 0,
      end: (c) => {
        c.volatile = true;
        c.emit(1, 0, Op.Today);
        return "date";
      },
    },
  ],
  [
    "days",
    {
      min: 2,
      max: 2,
      each: (c, argument) => c.need(argument, dated, "days counts the days from one date to another"),
      end: (c) => {
        c.emit(-1, 0, Op.DaysTo);
        return "number";
      },
    },
  ],
  [
    "date",
    {
      min: 3,
      max: 3,
      each: (c, argument) => c.need(argument, numeric, "date takes a year, a month and a day, as numbers"),
      end: (c) => {
        c.emit(-2, 0, Op.MakeDate);
        return "date";
      },
    },
  ],
  [
    "coalesce",
    {
      min: 1,
      max: Infinity,
      each: (c, argument, index, args) => {
        c.scalar(argument, "coalesce chooses among single values");
        if (index === 0) return;
        c.same(args[0]!, argument, "coalesce chooses among values of one kind");
        if (argument.type === "text") c.emit(0, -1, Op.CoalesceText);
        else c.emit(-1, 0, Op.Coalesce);
      },
      end: (_, args) => args[0]!.type,
    },
  ],
  [
    "text",
    {
      min: 1,
      max: 1,
      each: (c, argument) => c.scalar(argument, "text writes a single value"),
      end: (c, [argument]) => {
        const { type } = argument!;
        if (type === "number") c.emit(-1, 1, Op.NumberText);
        else if (type === "date") c.emit(-1, 1, Op.DateText);
        else if (type === "boolean") c.emit(-1, 1, Op.BooleanText);
        return "text";
      },
    },
  ],
  [
    "isempty",
    {
      min: 1,
      max: 1,
      each: (c, argument) => c.scalar(argument, "isempty asks of a single value"),
      end: (c, [argument]) => {
        if (onNumberStack(argument!.type)) c.emit(0, 0, Op.IsEmpty);
        else c.emit(1, -1, Op.IsEmptyText);
        return "boolean";
      },
    },
  ],
]);

/**
 * Compiles a formula against what its names stand for. On success, its type,
 * the paths it reads and a function that evaluates it; otherwise what is wrong
 * and where.
 */
export function compileFormula(text: string, scope: FormulaScope): Compiled {
  if (text.length > MAX_LENGTH) {
    return {
      ok: false,
      message: `A formula is at most ${MAX_LENGTH.toLocaleString("en")} characters long.`,
      at: MAX_LENGTH,
      length: text.length - MAX_LENGTH,
    };
  }
  const compiler = new Compiler(text, scope);
  let result: Operand;
  try {
    result = compiler.formula();
  } catch (error) {
    if (error instanceof Refusal) {
      return { ok: false, message: error.message, at: error.at, length: error.length };
    }
    throw error;
  }
  const program: Program = {
    code: Int32Array.from(compiler.code),
    constants: Float64Array.from(compiler.constants),
    strings: compiler.strings,
    paths: compiler.paths,
    type: result.type,
    numberDepth: compiler.maxNumberDepth,
    textDepth: compiler.maxTextDepth,
  };
  return {
    ok: true,
    type: result.type,
    references: Object.freeze(compiler.paths.slice()),
    volatile: compiler.volatile,
    evaluate: (get) => run(program, get),
  };
}