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 subset of Cypher, not complete Neo4j/openCypher parity. The openCypher Compatibility Reference is the feature-by-feature inventory, including unsupported syntax, function names and semantic differences. The openCypher Technology Compatibility Kit (TCK) is a prioritized gap report, not a strict regression gate or certification. This chapter provides an overview and usage examples.

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.

Relationship type alternatives in paths

A MATCH path such as (a)-[:ROAD|RAIL*0..3]->(b) accepts either listed relationship type at each hop, including routes mixing both types. Repeating a type name does not duplicate a route. Distinct routes remain distinct result rows unless the query requests DISTINCT; matching endpoints alone do not collapse them. A physical relationship cannot be reused within one matched path. A lower bound of zero permits the empty path where the endpoint constraints match.

The same type alternatives work with incoming and undirected patterns. CREATE and MERGE still require a single fixed relationship type and reject variable-length relationship creation.

Named shortest paths

MATCH p = shortestPath((a)-[:ROAD*]->(b)) selects one unweighted shortest path per endpoint pair. allShortestPaths selects every tied shortest path; it does not enumerate longer paths. When several shortest paths tie, shortestPath does not promise which one is chosen.

The supported named form contains one relationship segment, with a minimum length of zero or one and an optional maximum. Incoming, outgoing and undirected traversal and relationship-type alternatives are supported. A zero minimum permits an empty path for matching endpoints. Identical endpoints with a positive minimum raise an error. Disconnected pairs produce no path; OPTIONAL MATCH retains its null-row behavior. This zero-length behavior differs from Neo4j with its default dbms.cypher.forbid_shortestpath_common_nodes=true, which rejects common endpoints even when the minimum is zero.

Relationship-variable bindings, relationship property maps, multiple segments, and a minimum above one are rejected in this form. A WHERE attached to the shortest-path MATCH and another relationship pattern in that same MATCH clause are also rejected. Filtering after WITH p is supported: it filters paths already selected and does not search for a longer path satisfying the filter.

Shortest-path search uses the query’s execution-memory budget and cancellation checks. Search buffers and emitted path collections also observe PATINADB_MAX_AGG_ROWS. A refusal aborts the query; it does not return an incomplete set of tied shortest paths as a successful result. Result memory is conservatively retained for the query-result lifetime, so discarded intermediates can cause an early budget refusal.

Membership values

Relationship patterns can be Boolean operands in projections, for example RETURN NOT ((a)-[:LINK]->(b)) AS disconnected or RETURN ((a)-[:LINK]->(b)) AND true AS connected. Named pattern bindings must already exist in scope. A bare pattern is not a general scalar value: returning (a)-[:LINK]->(b) alone, assigning it directly, or passing it to size() is rejected. Use an explicit EXISTS { (a)-[:LINK]->(b) } for a standalone Boolean existence result. With a null endpoint there is no matching path, so the negated existence result is true. Scalar parentheses and list expressions keep their ordinary meaning.

item IN items and item NOT IN items compare the value bound to item. For example, a node tested against collect(node) remains an entity; it is not implicitly converted to its UUID. Scalar and list bindings likewise retain their types. Use an explicit property such as node.id, or node.__id for the internal UUID, when the list contains those values. Dotted property membership keeps its existing lookup and seek behavior; null membership retains three-valued logic.

The bare-binding parser correction in PR #1410 has focused and core regression coverage. It does not fix the existing identifier limitation for literal dots inside backticks. No query performance claim follows from this semantic correction.

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 searched forms). A simple CASE value may be a complete Boolean expression, for example CASE n.active IS NULL WHEN true THEN .... Boolean values use the same equality and NULL behavior as other simple CASE values; CASE WHEN predicate THEN ... remains the searched form.
  • 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.

datetime({epochMillis: value}) constructs an instant from integer Unix milliseconds, including negative values. For example, datetime({epochMillis: -1}).millisecond is 999. An optional timezone changes the local representation of that instant; calendar components can override it. A no-op override in an ambiguous daylight-saving hour retains the original offset. Invalid epoch types or competing temporal selectors raise an argument error; datetime(null) still returns null.

Epoch-map construction rejects instants or override years outside the supported calendar range rather than wrapping them. Named zones have an additional calendar-library range bound. A calendar override into a nonexistent local daylight-saving time raises an error; it does not shift the time forward as Neo4j does. This is not full temporal-range or override compatibility.

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

A missing sort property is NULL. A storage error while reading a required node or relationship sort property fails the query, for both full sorting and ORDER BY ... LIMIT. Empty inputs and LIMIT 0 do not require those reads.

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. SHOW INDEXES returns an indexes list of strings. Single-property entries remain bare property names and point indexes retain their POINT FOR … form. Each registered compound definition appears once, for example COMPOUND INDEX [`batch_lookup`] FOR (:`Batch`) ON (`run_id`, `id`). The field order is part of its identity; matching registered names are listed together. Unnamed definitions omit the name list. Identifiers are backtick quoted, with embedded backticks doubled. This inventory reports registered schema; it does not report a separate readiness or population state.

  • 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 also includes a Decisions: section after the physical-access details. Each entry names the decision point, chosen alternative, available row/cost estimates, reason and provenance. These are the selector’s existing estimates, not measured runtimes. Priority walks stop when their winner is known, so later alternatives may be absent.

PROFILE adds Decisions (executed): and an Executed operators (query thread): section. Operator numbers are preorder indices of the executed plan. rows=N counts successful rows actually pulled from that iterator; a LIMIT can leave an input only partly consumed. MatchScan also shows its entry estimate and q-error (the larger of estimate/actual and actual/estimate, replacing zero with one). An operator bypassed by a fast path is marked rows=unobserved (bypassed/fused). Worker internals and mutation/seeded paths that bypass the iterator are not per-operator measurements. The separately planned access tree is still labelled Planned access (before execution); use the executed section for row counts.

The audit retains at most 128 distinct decisions and coalesces repeats by point, subject and chosen alternative. repeats includes repeated seeded executions; the displayed estimates are from the first observation. Ordinary queries do not construct audit events or use a per-row counting adaptor.

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.

Planning note for filtered pages

For indexed keyset pagination, use a matching compound index and a qualified ordering property, for example:

CREATE INDEX tickets_by_project_id IF NOT EXISTS
FOR (ticket:Ticket) ON (ticket.project, ticket.id)

MATCH (ticket:Ticket {project: $project})
WHERE ticket.id > $after
RETURN ticket.id AS id, ticket.title AS title
ORDER BY ticket.id LIMIT $limit

Use separate parameterized query shapes when an optional filter/cursor is absent, rather than assuming ($after IS NULL OR ticket.id > $after) preserves a seek. ORDER BY id is valid syntax, but the #1261 million-ticket application diagnosis observed a separate sort for that projected alias while ORDER BY ticket.id selected the compound sort-covered path. Check EXPLAIN on the actual query; equivalent result semantics do not guarantee identical access plans. The local engineering evidence is docs/customer-blackbox-million-1261-2026-09-16.md.

For a required single-node match with equality predicates covering exactly a compound index’s fields, count(*) or count(ticket) can use agg.filtered_count_scan: it counts matching index entries rather than loading each node. This is still a scan of matching entries, not a precomputed counter. Optional matches, additional filters, secondary-label matches and unsupported value encodings retain the general execution path. EXPLAIN reports the actual choice. The snapshot transaction and numeric-equality semantics are unchanged.

A non-null scalar comparison on the ordered property can prove that missing values cannot match, avoiding a separate whole-label population check for that compound sort. Without such a predicate, NULL placement must still be preserved; first-page planning can therefore cost more than a bounded keyset page. These planning rules do not promise a fixed latency for arbitrary distributions.

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 partial subquery, expression and error-semantic compatibility. See the compatibility reference rather than using a historical TCK percentage as a current support contract. (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_created, relationships_created, properties_set, … in query summaries) are reported on both surfaces: as stats in the REST write response and as ResultSummary.counters over Bolt — see Bolt → Side-effect counters for which counters exist and which are deliberately never emitted.