Aster Web Data / How it works

Comparison and arithmetic rules

Where your database runs an operation its own comparison, collation and arithmetic apply, and where Aster computes a result its written rules apply.

If your customers column is case-insensitive in the database, a $filter=name eq 'acme' through Aster Web Data finds ACME, as a query in your own SQL client would. Aster translates each OData operator to the database operator with the same meaning, so wherever the database runs an operation, the answer is the database's. Aster's own written rules apply only where Aster computes a result itself: across two databases, and in the browser.

Where your database runs the operation

The database decides:

Comparisons and collation. A comparison, sort or like on a text field uses the column's own collation. $metadata publishes it as AsterData.Collation, and Aster checks it against the column at start-up and reports a column that disagrees. See Model your data.
Arithmetic. Decimal scale and rounding, double overflow, and integer overflow follow the database.
Division by zero. The database's result applies, whether that is an error or a null.
Case mapping. tolower and toupper follow the database's mapping.
Stored types. Dates, decimals and doubles are held as the database holds them. Aster reads a value back exactly or refuses it with an error, and never rounds it silently.

Null placement in a sort is the one exception: Aster states it on every sort key, first in ascending order and last in descending, as OData orders them.

The table compares a few cases. Each cell is the database's own behaviour, except where it says the engine: those operations DuckDB and SQLite leave to Aster.

Case	PostgreSQL	MariaDB	DuckDB	SQLite
`divby` by zero	An error	Null	The engine: `INF` or `NaN`	The engine: `INF` or `NaN`
`1.5 div 2` on decimals	`0.75000000000000000000`	`0.75000000000000`	`0.75` (the engine)	`0.7`, truncated at the scale
`tolower`, `toupper`	Under the collation's provider	Under the collation	The engine	ASCII only
`INF` and `NaN` in a `double`	Stored	Not stored	Stored	`INF` stored, `NaN` writes refused
Stored decimal	`NUMERIC`, unbounded	`DECIMAL(p,s)`, default `(65,30)`	`DECIMAL(p,s)`, `p` up to 38	Scaled `INTEGER`, precision up to 18
Stored date-time	`TIMESTAMPTZ`, microseconds	`DATETIME(6)`, UTC, years 1 to 9999	`TIMESTAMPTZ`, microseconds	Fixed-width ISO 8601 UTC text, six fractional digits

Each database's page lists where its behaviour departs from OData: PostgreSQL, MariaDB, DuckDB and SQLite. TigerBeetle has a fixed catalog and no nulls or doubles.

Nulls

OData compares with nulls in two values, true or false, and Aster writes each comparison so the database gives that answer. $filter=region eq null finds the rows with no region. A row whose field is null matches no eq, lt or gt against a value, and matches ne: region ne 'EU' keeps the rows with no region, and so does not(region eq 'EU').

Where Aster computes the result

Aster applies its own rules where no database computes the answer:

Across two databases. For $orderby=customer/name where the customers are on another database, Aster performs the join and the sort. check warns about a navigation between two databases over text keys whose collation is not binary, because keys the collation calls equal do not relate under Aster's rules.
In the browser. In the browser has no database, so every result follows Aster's rules.
In operations a database leaves to Aster, such as those marked "the engine" in the table above, and over a script's virtual sets.

The rules:

Text. Comparison and sort use code point order, which is case-sensitive and has no trailing-space padding. Case-insensitive comparison uses the Unicode lowercase mapping, not full case folding, so ß does not equal ss.
Overflow. An int64 operation that overflows fails the request with 400 and EvaluationFailed. The value does not wrap or saturate.
Division by zero. An error for every numeric type, except divby on floating-point types, which keeps the OData rule and returns INF, -INF or NaN.
Doubles. NaN equals nothing, itself included, and every ordering comparison with it is false. A sort places NaN after every other number.
Decimals. A decimal holds up to 28 significant digits, with a scale from 0 to 28, and a result that needs more is rounded. Equality ignores scale, so 5 equals 5.0. A write with more decimal places than the declared scale is refused, and a value a database returns with more digits than Aster can hold is an error when it is read.
Dates. A date-time keeps the offset it was written with and compares by instant, so 08:00+01:00 equals 07:00Z. now() is the instant the request started, in UTC, and every call within one request returns it.

See where each part ran

The explain header shows where each operation of a request ran, and so which rules gave its answer. A release can move work between a database and Aster; the release notes list each such change for each database, and a change in placement ships only in a minor or major version, never in a patch.

From request to SQL shows how a request is split, and What runs on your database shows the SQL it becomes.