TessariDBDocs
  • 0.34.2-betalatest
  • 0.34.1-beta
  • 0.34.0-beta
  • 0.33.4-beta
  • 0.33.3-beta
  • 0.33.2-beta
  • 0.33.1-beta
  • 0.33.0-beta
  • 0.32.0-beta

Conditions and reads

A filter is an expression that answers with a boolean — plus ordering, bounds, folds, grouping, and the plan behind them.

A filter is an expression that answers with a boolean. Everything below follows from that one sentence.

DEFINE COLLECTION people;
CREATE people:1 = { name: 'ada', age: 17, city: 'Paris', tags: ['urgent'] };
CREATE people = { name: 'grace', age: 45, city: 'Lyon', tags: ['old'] };
CREATE people = { name: 'alan', age: 'nineteen', city: 'Paris' };
CREATE people = { name: 'edsger', age: NULL, city: 'Lyon' };
CREATE people = { name: 'barbara', city: 'Paris' };

Five records, one field holding two different types, and two kinds of absence. The examples below are all against these.

Comparison

SELECT * FROM people WHERE city = 'Paris';
SELECT * FROM people WHERE city != 'Paris';

Three, then two. Equality is exact on the whole value, and inequality is its complement.

An ordered comparison follows the store's declared order across types:

SELECT * FROM people WHERE age > 18;

Two records — grace at 45 and alan at 'nineteen', because a string orders after every number.

Absence is not a small value

SELECT * FROM people WHERE age < 1000;

Two rows, not four. NONE and NULL are not treated as very small numbers, so they do not appear in ordered comparisons at all. That is deliberate: a missing age sorting below every real one is how "youngest customer" quietly becomes "the record nobody filled in".

They are found by naming them:

SELECT * FROM people WHERE age = NONE;    -- barbara: no such field
SELECT * FROM people WHERE age = NULL;    -- edsger: the field is there, empty

Two different absences, and the language keeps them apart.

Combining

SELECT * FROM people WHERE city = 'Paris' AND age = 17;

AND binds tighter than OR:

SELECT * FROM people WHERE city = 'Paris' AND age = 17 OR name = 'grace';

Two records. Parentheses say the other thing:

SELECT * FROM people WHERE city = 'Paris' AND (age = 17 OR name = 'grace');

One. NOT negates, with no third truth value:

SELECT * FROM people WHERE NOT (age = 17);

Four — everything that is not seventeen, including the records with no usable age at all.

Membership and patterns

From the collection's end, or the value's:

SELECT * FROM people WHERE tags CONTAINS 'urgent';
SELECT * FROM people WHERE 'urgent' IN tags;

With a document on the left, CONTAINS asks whether it holds a sub-document — every field asked for is there and contained, an array holds each element asked for in any order, anything else is equal:

DEFINE COLLECTION orders;
CREATE orders:1 = { doc: { status: 'paid', customer: { city: 'Paris' }, lines: [{ sku: 'a' }, { sku: 'b' }] } };
SELECT * FROM orders WHERE doc CONTAINS { customer: { city: 'Paris' } };
SELECT * FROM orders WHERE doc CONTAINS { status: 'paid', lines: [{ sku: 'b' }] };

A single value is not an array of one here either: { tags: 'a' } is not in { tags: ['a'] }. With an array on the left CONTAINS stays membership, which is exact. A containment index serves it.

A pattern covers the whole value:

SELECT * FROM people WHERE name LIKE 'ad%';

LIKE is a pattern over characters. For whole words, and for anything you want ranked, use full-text search.

What a condition may not be

SELECT * FROM people WHERE name;

Refused — ConditionNotBoolean. A truthiness rule is a rule everybody has to memorise, and every language that has one has a different one.

But an absence is not a type error there — it is a no:

SELECT * FROM people WHERE NONE;

No rows. This is what lets a call over a field some records lack narrow the answer instead of failing the read:

SELECT * FROM people WHERE array::len(tags) > 0;

Two rows. Three of the five have no tags, so the call answers with an absence for them. Refusing that would let one record missing one field fail the whole read.

Two comparisons in a row are refused where they are written:

SELECT * FROM people WHERE 1 < age < 100;

UnexpectedToken. It reads as a range and is not one.

Order and bounds

SELECT * FROM people ORDER BY name;
SELECT * FROM people ORDER BY city, name DESC;
SELECT * FROM people ORDER BY name START 1 LIMIT 2;

A start past the end is a state, not a mistake — START 99 answers with no rows. A bound with no order still applies.

An index may serve a bounded order, and when it cannot cover every record the scan still answers:

DEFINE INDEX by_age ON people FIELDS age;
SELECT * FROM people ORDER BY age DESC LIMIT 5;

Five rows. barbara has no age and therefore no index entry, and descending she belongs after every record that does — so the answer is complete either way.

The words that shape a read are not reserved names:

DEFINE COLLECTION order;
CREATE order = { limit: 10, by: 'ada', start: 1 };
SELECT * FROM order ORDER BY by LIMIT 1;

Folds

A fold answers once for many records:

SELECT count(*) AS n FROM people;
SELECT count(*) AS records, count(age) AS ages FROM people;

Counting records and counting a value are different questions, and both are askable.

The folds pass over what holds nothing:

DEFINE COLLECTION scores;
CREATE scores = { n: 10 };
CREATE scores = { n: 20 };
CREATE scores = { n: NULL };
CREATE scores = { other: 'no n at all' };
SELECT sum(n) AS total, mean(n) AS average, count(n) AS held FROM scores;

sum is 30, mean is 15, held is 2. A mean that counted the empty rows would be a different number with the same name.

Folding something that is not a number fails rather than coercing:

SELECT sum(name) AS total FROM people;

NotSummable. And * means the records themselves, which only count can fold — sum(*) is StarIsOnlyForCount.

Grouping

SELECT city, count(*) AS n FROM people GROUP BY city;

Once per distinct key. A grouped read may answer only with its keys and its folds:

SELECT name, count(*) AS n FROM people GROUP BY city;

Refused — UngroupedProjection. There are three names in Paris and the row has one slot; picking one silently is the behaviour this refuses.

Composing over a fold

A fold is an expression, so it composes:

SELECT sum(n) * 2 AS doubled FROM scores;
SELECT sum(n) / count(*) AS per_score FROM scores;
SELECT city, count(*) * 10 AS scaled FROM people GROUP BY city;

Its value is constant within its group, which the evaluator makes literally true by substituting it before the rest of the expression runs.

Three things have no meaning and are refused:

SELECT mean(sum(n)) AS nonsense FROM scores;

FoldInsideAFold — the inner one leaves nothing to fold.

SELECT city FROM people WHERE count(*) > 1 GROUP BY city;

FoldInAFilter — a filter sees one record at a time, so this is a filter over groups, which WHERE is not.

SELECT city, count(*) AS n FROM people GROUP BY city ORDER BY count(*);

Also FoldInAFilter. Order by the fold's name instead, which shapes the groups as expected:

SELECT city, count(*) AS n FROM people GROUP BY city ORDER BY n DESC LIMIT 1;

A field called count is still a field:

DEFINE COLLECTION tallies;
CREATE tallies = { count: 7 };
SELECT * FROM tallies WHERE count = 7;

Folds that estimate

Two folds answer in bounded memory however many records they read. Each says so in a note on the answer:

DEFINE COLLECTION visits;
CREATE visits = { page: '/', visitor: 'a', ms: 12 };
CREATE visits = { page: '/', visitor: 'b', ms: 40 };
SELECT page, approx_distinct(visitor) AS users, approx_quantile(ms, 0.99) AS p99 FROM visits GROUP BY page;
  • approx_distinct counts distinct values exactly up to 1 024 of them, then estimates within a few per cent (a HyperLogLog of 16 384 registers).
  • approx_quantile(x, q) answers the value at rank q, from 0 to 1, within 1 % of the true one (a DDSketch).
  • The note is estimated, naming the fold, the method and the bound.
  • On a split table each leader folds its own shards and the merged answer is the one a whole node gives.

EXPLAIN

The plan a read would take, without taking it.

EXPLAIN SELECT * FROM people WHERE age > 1;
EXPLAIN SELECT * FROM people WHERE city = 'Paris';

The first explains as a scan, the second names the index. A decision nobody can look at is a decision nobody can debug.

Only a read has a plan to describe — EXPLAIN DELETE people:1 is UnexpectedToken.

Where the rest of it is

Conditions and reads — TessariDB