SQL Engine & Queries

LoomCache ships a Calcite-backed SQL engine for map queries. Queries target named maps (LoomMap values); each query reads from a single named map.

Here is the life of a query, from SQL string through parse, plan, and scan to the returned rows:

Life of a SQL Query

Client submits the query through LoomClient.sql(...).

SELECT key, name, age FROM users WHERE age > 30

SqlParserCalcite parse + validate

→

QueryPlanfilter · project · map scan

→

QueryEnginepredicate scan + caps

Named mapusers

u-01Ada · 36

u-02Ben · 24

u-03Coy · 41

u-04Dee · 29

u-05Eli · 33

u-06Fay · 19

u-07Gus · 52

u-08Hal · 27

WHERE age > 30

→

LoomSqlResultkey · name · age

Each query reads from a single named map.

Executing queries

Run SQL through LoomClient.sql(String). The client sends the query through the Java API without parsing the FROM clause locally; the server extracts the target map name and performs routing. The result is a LoomSqlResult:

// User must have an explicit supported serializer/encoding before production use.
// Routing uses the sentinel key _sql_auto; the server parses the FROM clause server-side.
LoomSqlResult result = client.sql("SELECT key, name, age FROM users WHERE age > 30");

for (List<Object> row : result.rows()) {
    Object key = row.get(result.getColumnIndex("key")); // positional, via column-name -> index
    ...
}
// Or address a cell directly on the result by (rowIndex, columnName):
Object firstAge = result.getValue(/* rowIndex */ 0, "age");
if (result.truncated()) {
    // the safety cap cut matching rows — see "Result limits" below
}

LoomSqlResult is a final class (not a Java record) holding columnNames, positional rows (List<List<Object>>), and a truncated flag. Use getColumnIndex(String) to resolve a column name to its positional index, and getValue(int rowIndex, String columnName) to address a cell directly. There is no per-row typed accessor object.

The engine consists of the following components:

• SqlParser — Calcite-backed SELECT/DDL parser producing a QueryPlan.
• QueryPlan — the parsed plan (projection columns, map name, Predicate, ORDER BY, LIMIT, optional aggregate).
• QueryEngine — executes the plan against the named map, enforcing the scan/row safety caps.
• QueryOptimizer — utility that extracts a partition key from a predicate for partition-pruned routing.
• IndexConfig / IndexType / AttributeExtractor — index declarations and reflective attribute extraction.

Supported syntax

• SELECT with projections (* or named columns) and AS aliases.
• FROM <mapName> — a single map (queues/sets/lists are not queryable today; multi-map / JOIN FROM clauses are rejected).
• WHERE <predicate> with:
  • =, <>, <, <=, >, >=
  • LIKE / ILIKE (SQL patterns), REGEX (RLIKE)
  • IS NULL / IS NOT NULL
  • IN, BETWEEN, NOT BETWEEN, BETWEEN SYMMETRIC (swaps bounds if low > high)
  • AND, OR, NOT
  • LIKE ESCAPE is explicitly not supported and throws at parse time.
• ORDER BY with optional NULLS FIRST / NULLS LAST per column, LIMIT.
• Aggregates over one map: COUNT, SUM, AVG, MIN, MAX (one aggregate projection per query).
• GROUP BY, HAVING, OFFSET, QUALIFY, and WINDOW are rejected/unsupported in this release, as are joins.
• DML and DDL statements are also parsed: INSERT, UPDATE, DELETE, CREATE MAPPING, CREATE VIEW, CREATE TYPE, EXPLAIN, and SHOW MAPPINGS / VIEWS / TYPES. These are available for embedded/testing use cases; consult the query-engine module for runtime support status of each statement type.

Indexes

CREATE INDEX is disabled in this release. CREATE INDEX <name> ON <mapping> (<cols>) TYPE SORTED|HASH|BITMAP is recognized by the parser, but runtime SQL indexes are disabled because index data is not maintained on every map mutation and snapshot restore. The server rejects CREATE INDEX, and LoomMap.addIndex(IndexConfig) will throw before sending a wire request. addIndexAsync(IndexConfig) wraps the same synchronous throw and surfaces it as a failed CompletableFuture — the exception is not thrown synchronously at the call site. The IndexType enum remains SORTED, HASH, BITMAP for the deferred metadata surface.

Attribute extraction is handled by AttributeExtractor — a reflective zero-arg accessor over the value POJO. Reflective extraction still depends on the serialization policy: the value type must be explicitly supported on every client and member before production use.

Result limits

Queries without an explicit LIMIT are capped at QueryLimits.MAX_RESULT_ROWS (10,000 rows) to protect heap usage. When that safety cap (or the 1,000,000-cell MAX_RESULT_CELLS budget) cuts matching rows, the server logs a WARN and LoomSqlResult.truncated() returns true.

Aggregate queries scan at most DEFAULT_MAX_AGGREGATE_SCAN_ENTRIES (1,000,000) entries. If that scan limit is reached the query is rejected with a QueryLimitsExceededException — an aggregate cannot return a partial result, so the caller must narrow the predicate rather than receive a truncated answer.

Limitations today

• Only named maps (LoomMap values) are queryable. Queues / sets / lists have no SQL surface.
• Joins, GROUP BY, HAVING, OFFSET, QUALIFY, and WINDOW are rejected/unsupported in this release; keep SQL map-scoped.
• No transactions — each SQL request runs as a single linearizable read.
• No cross-cluster federation; queries are scoped to the local cluster. Within a multi-group cluster, a query may fan out across Raft groups through scatter/gather over co-located groups.
• There is no server-side query-result cache; every SQL request re-scans the live map under the safety caps.
• All result paths are bounded: an explicit LIMIT, otherwise the MAX_RESULT_ROWS / MAX_RESULT_CELLS caps apply, and aggregates are bounded by DEFAULT_MAX_AGGREGATE_SCAN_ENTRIES.

See Data Structures for what is addressable from the SQL engine today.