Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

Cypher Support

patinaDB implements a large, Neo4j-compatible subset of Cypher. The engine is validated against the openCypher Technology Compatibility Kit (TCK): the latest run passes ~3755 / 3868 scenarios (~97.1%). The TCK is treated as a prioritized gap report, not a strict regression gate — so treat this chapter as the authoritative statement of what works.

Reading

  • MATCH with node/edge patterns, optional labels, property maps, and all three directions (->, <-, - undirected).
  • OPTIONAL MATCH.
  • Variable-length paths — -[:T*1..3]->, [*0..n] (length-0 self-row), shortestPath.
  • WHERE — full boolean expressions: AND / OR / NOT / XOR, comparisons, IN, IS NULL, STARTS WITH / ENDS WITH / CONTAINS, regex =~, nested parenthesisation.
  • RETURN with DISTINCT, aliases (AS), expressions, ordering.
  • WITH chained query stages (projection, filtering, aggregation hand-off, ordering before projection).
  • UNWIND, UNION / UNION ALL.
  • ORDER BY / SKIP / LIMIT — single-key ordering uses an index fast-path; multi-key falls back to a post-sort.

Loading CSV

LOAD CSV WITH HEADERS FROM 'file:///data/people.csv' AS row
WITH toInteger(row.id) AS id, row.name AS name
CREATE (:Person {id: id, name: name})

LOAD CSV [WITH HEADERS] FROM '<url>' AS <var> [FIELDTERMINATOR '<c>'] streams rows from a CSV file into the query as a row source — like UNWIND, but from a file. It is read row by row (the file is never fully buffered).

  • WITH HEADERS → each row is a map keyed by the header, so row.columnName works. Without it, each row is a list of string cells: row[0], row[1], ….
  • Cells are strings (openCypher semantics). Convert explicitly with toInteger / toFloat / toBoolean. A coercion may sit directly inside a CREATE/MERGE pattern property — CREATE (… {p: toInteger(row.x)}) — as well as in a WITH; only a row[i] list-index expression still needs a WITH stage first.
  • FIELDTERMINATOR '<c>' overrides the delimiter (default ,).
  • Sources: file:///absolute/path, file://host/absolute/path (the host is ignored), and bare/relative filesystem paths. Quoted fields and embedded delimiters/newlines are handled. http(s):// URLs are not yet supported.

Large loads (memory): a plain LOAD CSV … CREATE buffers all its writes into one transaction (on the server, one Raft entry), so a very large load has an O(rows) memory cliff. Wrap it in CALL { … } IN TRANSACTIONS (below) to chunk it into many small commits — the online answer. For a large columnar file already on the server, POST /mgmt/upsert is faster still — see Bulk Upsert. A LOAD CSV … RETURN row read query streams end-to-end.

Server-side file reads (security): on the server, LOAD CSV FROM 'file://…' reads a server-side file, so it is deny-by-default: it reads only directories whitelisted with --allow-csv-dir, and any file-I/O query is raised to require the global Admin role. See Bulk Loading → Server file-I/O sandbox and Authentication & TLS.

Batched writes: CALL { … } IN TRANSACTIONS

LOAD CSV WITH HEADERS FROM 'file:///data/people.csv' AS row
CALL {
  WITH row
  CREATE (:Person {id: toInteger(row.id), name: row.name})
} IN TRANSACTIONS OF 1000 ROWS

CALL { <write-subquery> } IN TRANSACTIONS [OF <n> ROW[S]] [ON ERROR {CONTINUE|BREAK|FAIL}] chunks a large write into many small, independently committed transactions instead of one giant one. The outer stream (typically LOAD CSV … AS row or UNWIND $rows AS r) feeds rows to the inner subquery, which runs and commits every n rows as a separate transaction (default n = 1000). This bounds peak memory to one chunk — the fix for the LOAD CSV … CREATE RAM cliff — and, on a cluster, replicates as one small entry per chunk instead of one unbounded one.

  • Each chunk is its own commit ⇒ its own engram (so batched ingest is versioned and time-travellable, unlike the offline importer), and partial progress is durable: a crash between chunks recovers to a chunk boundary (all-or-nothing per chunk). A later chunk sees the writes committed by earlier chunks.
  • OF <n> ROW[S] sets the chunk size (omit for the 1000 default).
  • ON ERROR — FAIL (default) aborts the whole statement on a failing chunk; CONTINUE skips the failing chunk and commits the rest; BREAK stops after the failing chunk, keeping the chunks already committed. A skipped/failed chunk changes nothing (it is resolved against a throwaway copy of the graph, so a partial chunk is never left behind).
  • The CALL { … } IN TRANSACTIONS must be the final clause of the query.

Writing

  • CREATE, MERGE (match-or-create), SET (properties, map merge, and SET n:Label label mutation), REMOVE, DELETE / DETACH DELETE.
  • FOREACH.
  • Writes inside WITH stages.

Expressions & functions

  • Arithmetic, string, list, and map operators; list/map indexing and slicing.
  • Aggregation — count (incl. count(*)), sum, avg, min, max, collect, with GROUP BY semantics (mix of aggregated and grouping keys in RETURN), DISTINCT inside aggregates, and aggregate hoisting through nested function calls and arithmetic.
  • CASE (simple and generic).
  • List comprehensions and pattern comprehensions.
  • Quantifiers — any / all / none / single, including aggregate and count(*) sources.
  • Scalar functions — toInteger, toString, size, labels, type, nodes, relationships, range, and many more.
  • Temporal functions — date, time, localtime, datetime, localdatetime, duration, plus namespaced calls (date.truncate, duration.between, datetime.fromepoch) with ISO-8601 week numbering.

Value semantics

Numeric equivalence

patinaDB treats an integer and a mathematically-equal float as the same value for equivalence purposes, matching Neo4j. The fold is exact: a float counts as its integer twin only when it is finite, has a zero fractional part, and round-trips bit-for-bit ((f as i64) as f64 == f) — so a large integer that is not exactly float-representable never falsely merges, and two genuinely different integers never collide.

Because of this, DISTINCT, an implicit GROUP BY key, and a distinct UNION all merge numeric twins:

UNWIND [1, 1.0] AS x RETURN DISTINCT x
-- 1 row (previously 2)

UNWIND [1, 1.0, 2] AS x RETURN x, count(*)
-- 1 → 2, 2 → 1     (1 and 1.0 fall into one group)

The surviving value keeps its original type — first occurrence wins (as in Neo4j), so the result shows whichever of 1 / 1.0 appeared first, not a normalized form. UNION ALL is unaffected (it never de-duplicates). The same equivalence drives numeric-twin-aware UNIQUE constraints.

Cross-type ORDER BY

When a single ORDER BY column holds values of different types, patinaDB follows Neo4j’s documented precedence, lowest to highest:

Map < Node < Relationship < List < Path < Temporal
    < String < Boolean < Number < Point < Polygon < NaN < Null

Every temporal type (Date, Time, LocalTime, DateTime, LocalDateTime, Duration) now sorts between Path and String — i.e. before strings and numbers (previously they sorted after numbers). min() and max() follow the same order. This applies only to a mixed-type column; a column of a single type is ordered normally and is unaffected. (Point and Polygon are patinaDB spatial extensions beyond Neo4j’s list, shown here for completeness.)

Arithmetic errors

Integer division and modulo by zero raise Neo.ClientError.Statement.ArithmeticError. Integer +, -, *, and sum() over integers raise the same error on i64 overflow, rather than silently saturating or wrapping. A mixed integer/float expression is promoted to float before the operation (so it never overflows there — Neo4j parity), and float arithmetic is unchanged: it still yields ±Inf / NaN, not an error.

Procedures

CALL invokes built-in procedures (history, diff, full-text/vector search, graph algorithms, statistics, export, cache stats). See Procedures. User-defined scripting-language procedures are not supported, but user-defined read-only WASM procedures are — see WASM Procedures.

DDL

  • CREATE INDEX / DROP INDEX / SHOW INDEXES — single-property and compound (multi-field) B-tree indexes.

  • CREATE CONSTRAINT / DROP CONSTRAINT / SHOW CONSTRAINTS — all four Neo4j constraint kinds (uniqueness, existence, node-key/relationship-key, property-type), on both nodes and relationships. See Constraints.

  • CREATE FUNCTION … LANGUAGE wasm / DROP FUNCTION / SHOW FUNCTIONS — user-defined, sandboxed, read-only WASM procedures. See WASM Procedures.

  • CREATE POINT INDEX / DROP POINT INDEX / SHOW POINT INDEXES — accelerates spatial radius/bbox/kNN queries. See Spatial / Geo.

  • CREATE EDGE SORTED INDEX (patinaDB extension) — pre-orders, per anchor vertex, that anchor’s targets over one relationship type by a target property, so a traversal-fan-out + ORDER BY target.prop [DESC] LIMIT k is served by a seek + take (no fan-out, no post-sort — bench Q5–Q8). Two orientations:

    CREATE EDGE SORTED INDEX [name] FOR ()-[:REPORTED]->(m:Ticket) ON m.created_at
CREATE EDGE SORTED INDEX [name] FOR (m:Ticket)<-[:HAS_LABEL]-()  ON m.created_at
SHOW EDGE SORTED INDEXES

    Creating the index over a populated graph backfills existing edges; it is kept live on writes. EXPLAIN names limit.edge_sorted_topk once a covering index serves the query. On a server it replicates to every node (deterministic re-run). The name is optional and cosmetic (the def is identified by its shape). Not yet carried in Raft snapshots — see Limitations.

  • CREATE FULLTEXT INDEX / DROP INDEX / SHOW FULLTEXT INDEXES — see Full-Text Search.

  • CREATE DATABASE / DROP DATABASE / SHOW DATABASES (server only) — see Multi-Database.

  • EXPLAIN and PROFILE render the physical operator tree.

Query introspection

EXPLAIN MATCH (p:Person)-[:KNEW]->(q) WHERE p.born < 1850 RETURN q
PROFILE MATCH (p:Person {name: 'Ada'}) RETURN p

EXPLAIN shows the chosen plan (scan strategy: compound index, property-value index, label scan, or all-vertices) without running it; PROFILE runs and annotates it.

What is not supported

A precise list lives in Limitations. The headline gaps: no general scripting-language user-defined procedures (only sandboxed read-only WASM procedures), no triggers, and the ~5% of TCK scenarios that remain — mostly exotic temporal/list edge cases. (Index DDL and all four constraint kinds, on nodes and relationships, are supported; see Constraints and Limitations for the exact scope.) Side-effect counters (+nodes, -relationships in query summaries) are not tracked.