Several orders at once

A search over text and a nearest-neighbour read each answer with an order. An application that wants both — the words a person typed and what they meant — wants one order made of the two. FUSE is that order.

DEFINE NAMESPACE lib;
USE NAMESPACE lib;
DEFINE DATABASE notes;
USE DATABASE notes;
DEFINE ANALYZER plain FILTERS lowercase;
DEFINE TABLE docs SCHEMALESS;
DEFINE FIELD body ON docs TYPE string ANALYZER plain;
DEFINE INDEX by_body ON docs FIELDS body SEARCH;
CREATE docs:1 = { n: 1, body: 'lock contention and a lock', e: [1, 0], at: geometry { type: 'Point', coordinates: [0, 0] } };
CREATE docs:2 = { n: 2, body: 'a lock', e: [0, 1], at: geometry { type: 'Point', coordinates: [10, 10] } };
CREATE docs:3 = { n: 3, body: 'nothing to see', e: [0.9, 0.1], at: geometry { type: 'Point', coordinates: [0, 1] } };

SELECT n FROM docs ORDER BY FUSE (
  search::score(body, 'lock contention') DESC,
  vector::cosine(e, [1, 0]),
  geo::distance(at, geometry { type: 'Point', coordinates: [0, 0] })
);

1, 3, 2. Record 1 is first in all three orders. Record 3 has no word of the query but is second on meaning and on place; record 2 is second on words and last on the other two. Two orders out of three decide it.

Why places, and never values

Each branch is an order on its own and means exactly what the same words mean after ORDER BY alone, including its direction. The fused order then asks, of each record, where it came in each branch — not what value it came with.

That is the point. A relevance score runs from zero to whatever this table's rarest word is worth; a cosine distance runs from zero to two; a distance on the ground runs to twenty thousand kilometres. Added together, whichever has the larger range on this data decides the order, and a weight written beside it decides nothing. A place in an order is on one scale whatever produced it.

A record in the first DEPTH places of a branch earns weight / (60 + place) from it; the fused order is the sum, highest first, and equal sums are ordered by identity. WEIGHT defaults to 1 and must be above zero, and DEPTH defaults to 100. The 60 is a constant of this implementation, as the full-text scorer's own constants are.

SELECT n FROM docs ORDER BY FUSE (
  search::score(body, 'lock contention') DESC WEIGHT 3,
  vector::cosine(e, [1, 0])
);

1, 2, 3 — three times the weight on the words lets them outvote meaning.

Three things to know before relying on it

• A record no branch placed within its depth is not in the answer. Nothing ranked it, so there is nothing to order it by:

  SELECT n FROM docs ORDER BY FUSE (search::score(body, 'contention') DESC, vector::cosine(e, [1, 0])) DEPTH 1;
  

  One record. A LIMIT larger than what the branches placed answers with fewer.

• The WHERE applies to every branch. Each branch ranks the records that passed it, so a filter never removes a record from one side and not the other.

• A fused read is exact. Every branch is ranked over every record that passed the WHERE, which is also what it costs: a fused read reads the whole of what it filters, and no index-served order serves a branch of one. APPROXIMATE is a permission, so a fused read that writes it still answers exactly, and its plan says exact: true.

Where each branch placed a record

SELECT n, search::ranks() AS ranks FROM docs ORDER BY FUSE (
  search::score(body, 'lock contention') DESC,
  vector::cosine(e, [1, 0])
) LIMIT 1;

{ n: 1, ranks: [1, 1] } — one place per branch, in the order the branches are written, and none where a branch did not place the record within its depth. It answers only in the projection of a fused read:

SELECT search::ranks() AS ranks FROM docs;

NotFused. A place belongs to the fusion, not to the record. For the same reason a fused read projects after it orders: its branches read the record as it is stored, so a branch naming a projected alias reads nothing — name the field or the expression itself.

A name and a place

"Cafés called blue near me" is two orders — words and distance — and needs no new syntax; see Geometry.

What is refused

A fused order is the whole order. One branch, a weight of zero, DEPTH 0, another key beside FUSE (…), a GROUP BY and an AFTER cursor are each refused while the statement is read:

SELECT n FROM docs ORDER BY FUSE (vector::cosine(e, [1, 0]));

UnexpectedToken — one order is not a fusion.