Exact decimals

BoxDB’s patched SQLite engine supports DECIMAL values in arithmetic, comparison, and ordering. DECIMAL is the only exact decimal type.

CREATE TABLE prices(amount DECIMAL(20,2));
INSERT INTO prices VALUES('12.50');
SELECT amount + CAST('0.25' AS DECIMAL) FROM prices; -- decimal 12.75
SELECT amount FROM prices ORDER BY amount;          -- numeric order
SELECT decimal_div('1','3',2,'half_even');           -- decimal 0.33

Use decimal text, typed Rust parameters, or CAST('...' AS DECIMAL) for exact input. Ordinary SQL 0.1 and JavaScript numbers are binary floating point. CAST(0.1 AS DECIMAL) explicitly converts the actual binary value, producing 0.1000000000000000055511151231257827021181583404541015625.

Arithmetic and equality

+, -, *, unary signs, and % operate on finite arbitrary-precision decimals. INTEGER operands promote exactly. REAL arithmetic requires explicit conversion with CAST(... AS DECIMAL) or conversion of the decimal to REAL. No implicit float result is used to resolve decimal overflow.

/ returns an exact terminating decimal. Division by zero and nonterminating results such as CAST('1' AS DECIMAL)/3 produce errors. Use decimal_div(left,right,scale,mode) to round division once to an explicit scale, and decimal_round(value,scale,mode) to round an existing value. Supported modes are half_even, half_up, half_down, up, down, ceiling, and floor. These functions preserve requested trailing zeros and propagate NULL operands.

Decimal equality ignores display scale: 2, decimal 2.0, and decimal 2.00 are equal. Comparisons with REAL use its actual binary value, so decimal 0.1 is less than REAL 0.1. Decimal values participate in the numeric ordering before TEXT and ordinary BLOB values, with SQLite’s existing NULL placement. The same comparison drives predicates, indexes, ORDER BY, grouping, set operations, uniqueness, and foreign keys.

DECIMAL(p,s) checks precision and scale on assignment; excess fractional digits are rejected rather than rounded. Intermediate expressions are not restricted to the destination column’s scale. Use explicit rounding before assignment. Public declarations allow precision up to 1000. General decimal arithmetic is limited to 4096 normalized coefficient digits and an absolute scale of 4096, with bounded input text, enforced by the decimal implementation.

SUM promotes decimal inputs and preserves exact integer values, including integer overflow before decimal promotion. AVG uses exact division and therefore rejects nonterminating averages. MIN and MAX preserve the selected value. TOTAL deliberately retains SQLite’s approximate REAL contract. Window aggregates use the same rules. For fractional RANGE bounds, supply an exact decimal offset, for example RANGE BETWEEN CAST('0.5' AS DECIMAL) PRECEDING AND CURRENT ROW; an ordinary 0.5 offset is REAL and cannot mix implicitly with decimal arithmetic.

abs preserves decimal type. Built-in round uses half-up rounding for decimal input; decimal_round provides explicit modes. Bitwise operations retain integer semantics; use explicit INTEGER casts when integer-only SQL contexts such as LIMIT require them. Decimal values in typed arrays use strings to preserve digits. SQL JSON processing functions are disabled.

Storage and clients

The binary database keeps the standard SQLite page and record format. Decimals are ordinary BLOB records containing the one-byte tag d followed by UTF-8 decimal text. The database-wide codec version is stored in __boxdb_codec and readable snapshot headers. The tag survives stored columns, calculated values, temporary records, materialized CTEs, and sorting. No extra disk serial type or in-memory extension subtype is needed. SQLite’s record format defines BLOB serial types independently of their payload.

Standard file encoding does not promise the same SQL behavior in unmodified SQLite. Such clients see BLOB values and do not implement decimal affinity, numeric index ordering, operators, or the BoxDB constraint functions. Use the BoxDB engine for these databases. Every ordinary BLOB has a b tag followed by the original bytes, so payloads beginning with d or any other native tag remain BYTEA. Ordinary BLOB literals, casts, bind parameters, and function results are tagged at their API boundary. BoxDB uses a separate private wire-cell transport for native values and readable imports.

Rust exposes Type::Decimal and Value::Decimal(String). Calculated results are recognized from their runtime envelopes, not only column-origin metadata. Typed arrays serialize decimal elements as strings. Rust/CLI typed JSON retains the Decimal variant; TypeScript returns decimal values as strings. Bind TypeScript decimal parameters as strings and cast them when an expression requires a native decimal value.

The C API retains the five upstream fundamental type codes: decimal storage reports SQLITE_BLOB, while SQL typeof reports decimal. Decimal text views and explicit INTEGER/REAL conversions are handled by the patched value accessors. Standard C column and scalar-function accessors expose logical payloads. sqlite3_boxdb_column_blob and its size accessors expose complete storage cells; the vendored Rust FFI routes column BLOB reads through these accessors for typed decoding. Incremental BYTEA reads and writes address payload bytes, preserving the stored tag. Generic extensions that gate inputs on sqlite3_value_numeric_type still see BLOB; cast to the representation their API expects. The public decimal SQL functions are decimal_round and decimal_div, both returning native decimals.

Engine architecture and verification

Cargo depends directly on boxdb-sqlite-sys, the vendored SQLite 3.53.2 bundle. Checked source transformations apply at build time to the pinned amalgamation; every patch anchor must match exactly once. C helpers recognize values and connect VDBE operations to Rust decimal arithmetic. Aggregate helpers keep exact accumulators. The shared native type registry also drives UUID and temporal affinities. The original record encoder continues to serialize BLOB payloads. Readable YAML version 3 records the codec version once and uses typed mappings, such as amount: {decimal: "12.50"}, preserving requested display scale.

Comparison patches cover in-memory values and serialized-record paths, including optimized index comparison. SQLite’s sorter consumes those record comparators. This matters because numeric ORDER BY and index searches must agree even when results spill to temporary files. BoxDB adds precision checks during schema translation and registers explicit rounding functions on its connections in src/decimal.rs. Decimal affinity is retained in comparison planning and index keys; Bloom-filter hashes agree with equal INTEGER and REAL values. Comparisons coerce temporary values, preserving the original operands.

SQL integration tests exercise the Database seam: operators, comparison, indexed and unindexed plans, constraints, aggregates, materialization, transports, and snapshots. The same suite checks public Rust types and serialization. TypeScript tests check native transport without JavaScript Number conversion. These public seams are the acceptance boundary; private flags and patch strings are not the contract.