Skip to content
Drivine

Cypher on Postgres, Oracle and SQLite

A statement written as Cypher, with cypher_statement or cypher_file, is compiled to SQL over the graph’s tables on Postgres, Oracle and SQLite, and gives the rows a graph engine gives, as drivine4j compiles it:

from drivine import QuerySpecification, cypher_statement


spec = QuerySpecification(cypher_statement("MATCH (p:Person {city: $city}) RETURN properties(p)"))
people = await pm.query(spec.bind({"city": "Sydney"}))

  • A read is a query. A write (CREATE, MERGE, SET, REMOVE, DELETE) is an anonymous PL/SQL block on Oracle, a function made for the statement in pg_temp on Postgres, and on SQLite, which has no procedural language, steps that drivine_cypher, a function Drivine registers on each connection, carries out under a savepoint: one statement, applied whole or not at all. A block hands its rows back whole, so on Oracle a write is run with query, and a cursor over one is refused. On Postgres a cursor over a write writes when it is opened, as on a graph engine.

  • Parameters are named, $name: a statement bound to a list of them is refused.

  • A row of one item is its value, and a row of several a list of them. A node is its properties.

  • A value is compared as JSON, so '7' is not 7, and a missing property is null. A map keeps its keys in the order written. Two lists or maps that may hold a null are compared item by item, as Cypher compares them: [null] = [null] is null, and [1, null] = [2, null] false. IN compares the value with each item as = does.

  • <, <=, > and >= order two lists item by item where the statement makes one of them a list (n.tags < ['b']). Two values whose kind it leaves open, such as two parameters or two properties, compare as null where they hold lists. Two maps compare as null, and two that differ do in a list too, where Neo4j orders them.

  • A map or a list that holds a node or a relationship ({x: r}, [r, 1]) compares by what it holds, not by its properties, through a WITH or a CALL { … } too, as on a graph engine. Unwound by UNWIND, as a comprehension’s or a reduce’s variable, or carried past a write in the statement, it compares by its properties.

  • openCypher’s temporal values are their ISO 8601 text, as the store keeps a moment: date, datetime, duration and the rest make one, their fields read, and two moments compare as the moments they are. A parameter of a date, time, datetime or timedelta is a temporal value of its kind. A year is numbered as Neo4j and ISO 8601 number it, where Oracle and Postgres keep no year 0: 0000 is 1 BC and -0044 is 45 BC, made, read, worked out, truncated, written and read back (#326). A stored value whose year is 1000 or later is read as before; one of an earlier year is read by its era, so a statement that reads a stored date or moment compiles to SQL up to three and a half times as long (a filter on a stored date, 1,005 characters on Oracle and 703 on Postgres before, is 1,747 and 2,211, and 2,831 on an Oracle store without the era functions below), and in drivine4j, which compiles the same SQL, an aggregate of temporal arithmetic runs 8 to 10% slower (a truncate on Postgres, about 16%). Oracle counts days before 1582-10-15 by the Julian calendar, so the day of the week, the ISO week, the day of the year and a leap day of such a date are not Neo4j’s (#335). Two stored values BC compare by their text, which orders them backwards (#237). Postgres and Oracle keep no date before 4713 BC, so a year before -4712 that the store works out or reads, as date({year: -9999}), fails there. Python’s date and datetime have no year before 1, so a value BC is given as its ISO text to date() or datetime(). A zoned value the statement makes sorts as its moment (ORDER BY datetime(n.at)); one read as a property sorts as its text, and so does a map that holds one. A list of them, or a list of lists of them, sorts item by item so in an ORDER BY, min and max. Oracle reads by their places, not with JSON_TABLE, the items of a list that holds temporal values the statement makes, at any depth: a list of them, a list of lists or of maps of them (collect(DISTINCT [l, l])), one a CASE, coalesce or reduce may give, or one taken back out of a map; and the keys of any map, which keys reads from a list Oracle makes of them. Oracle 23 takes many seconds, or minutes, to parse a JSON_TABLE over a list worked out item by item with temporal arithmetic ([i IN range(1, 10) | t + duration({minutes: i})]), and neither a query timeout nor a kill ends the parse (drivine4j#331, drivine4j#347). Other lists keep JSON_TABLE, which is about ten times as fast for a short list; read by their places, the keys are faster than JSON_TABLE gave them.

  • UNION, CALL { … }, EXISTS { … }, COUNT { … }, FOREACH, =~, variable-length relationships, shortestPath and allShortestPaths compile, as do quantified relationships (-[:R]->{1,3}) and quantified path patterns ((a) ((x)-[r:R]->(y) WHERE x.v < y.v){1,3} (b)), whose variables are lists of what each repetition matched. On Oracle, whose recursive queries read nothing of the row they are in, a quantified path pattern’s WHERE and properties read no variable from outside it, and hold a subquery only where no variable of its MATCH or before it is named, and that walks no variable-length relationship or quantified path pattern of its own; on Postgres they may. What isn’t compiled yet (a path returned whole, SKIP of an expression…) is refused by name before anything is sent, and what isn’t Cypher with where it went wrong.

  • A value whose kind the statement fixes (a literal, a list or a map written, or a variable a WITH binds to one) is refused where an operator or a function takes another kind, as Neo4j refuses it when it plans the statement: RETURN 123 AND true fails with AND takes a truth value, not an integer, as in Neo4j., and so do WITH 123 AS x RETURN x.num and RETURN 1 IN 'foo'. A step of 0 in range(), written or given, a parameter given as the index of a list or a map written that isn’t indexed by its kind, and a map, or a list of maps or of lists, written in SET, are refused too, though Neo4j fails on them only when a row reaches them. A value whose kind is known only when the row is read, such as a property or a parameter, is compiled: where it is of the wrong kind, the statement may give null where Neo4j fails. A variable used as two kinds is refused, as Neo4j refuses it: MATCH (a)-[a]->() fails with a is a node, and is used as a relationship: a variable is of one kind, as in Neo4j. So is one a pattern makes where it is bound already: MATCH (a) CREATE (a), a path variable bound before its MATCH, or a relationship variable bound as something else. A pattern used as an expression, which tests for a match and binds nothing, is refused where it names a variable not bound before it, as Neo4j refuses it: MATCH (n) WHERE (n)-[r]->() RETURN n fails with r is not defined: a pattern used as an expression binds no new variable, as in Neo4j. …, where EXISTS { (n)-[r]->() } may bind one. So is size() of a pattern, size((a)-->()): COUNT { … } counts its matches. An empty map of properties, (a {}) or [r {}], is met by every node and relationship, and compiles as the pattern without it, but a CREATE or MERGE still makes no node bound already with it, MATCH (a) CREATE (a {})-[:R]->(b), as Neo4j refuses it.

  • truncate takes each unit Neo4j does, from millennium to microsecond, and a map of fields to set on the value cut short (date.truncate('month', d, {day: 2})), but not yet a nanosecond, a week’s or a quarter’s field, or a named zone.

  • CALL db.labels() and CALL db.relationshipTypes() compile, alone or within a query with YIELD, and give each label and type something has now, read from the nodes and relationships; any other procedure is refused. A parameter or a variable may be named by a keyword ($limit, MATCH (end)), as in Neo4j. Functions of numbers (exp, log, sin, atan2, round by a mode …) give the floats Neo4j gives, but where Neo4j gives NaN or an infinity (log(0), sqrt(-1), exp(1000)) the statement fails: JSON holds neither. A float is written as Java writes a double, by toString and added to a text: 1.0E7, 1.2E-4.

  • On Postgres, compiled Cypher works float arithmetic, sum() and avg() out in exact decimal (numeric), not as doubles, so it is more accurate: 0.1 + 0.2 is 0.3, where Neo4j gives 0.30000000000000004. A result differs from a graph engine’s only around its 16th significant digit, and still comes back as a float. It shows only where a result is compared for exact equality with one worked out in doubles, which is fragile with floats anyway. A float’s text carries no trailing zeros: toString(4.0 / 2) is "2.0".

  • On Postgres, text is compared and sorted by its code points, as Cypher does (B, a, b), not by the database’s collation: in the C collation, whose byte order is code point order in a UTF-8 database, as initdb and Postgres’s images make one. In a list the statement makes, such as ORDER BY [n.last, n.first], a text is ordered by its code points over its first 500 characters, and past them by the database’s collation. A list read from a property, and a map, still order the texts in them by the database’s collation (ORDER BY n.tags).

  • On Postgres, a write’s loop over its rows that reads the node or edge table for each row, where it adds to that table or joins it to the rows of a CALL { … }, runs with enable_seqscan, enable_hashjoin, enable_mergejoin and enable_material off, set back after it to what they were (#420, #430, drivine4j#609). Postgres kept a plan made while the table was empty, or took a CALL’s one row for 100, and read the whole table for each row: MATCH (n) MERGE (n)-[:R]->(n) took 28 s over 20,000 nodes where it now takes under 1 s. The settings are set for the transaction (set_config(…, true)), so a statement that fails leaves them as its transaction or savepoint had them.

  • On Postgres, a statement’s rows, a JSON list, read each row’s node or relationship back from its table by its id through a probe of the table’s key, LEFT JOIN LATERAL (SELECT * FROM … WHERE nid = … OFFSET 0) … ON TRUE, not a plain join (#446, drivine4j#644). A plain join is planned by the table’s statistics, and where they said the table was empty while it still had pages, as an ANALYZE just after a DELETE leaves them, each row was compared with every row of the table: a FOREACH over 20,000 nodes took 19 s where it now takes under 1 s. The probe is a scan of the key’s index whatever the statistics say. Oracle and SQLite keep the plain join.

  • On Postgres, after a graph is emptied by DELETE and loaded again, analyze its tables before reading it:

    ANALYZE <graph>_node, <graph>_edge;

    An ANALYZE (or an autoanalyze) that ran while the node table was empty leaves statistics that say it holds no rows over the pages its deleted rows left, and a read that joins it twice is then planned as a scan of every node for each node: MATCH (:A)-[r:R]->(:A) RETURN count(r) took 470 s over 20,000 nodes, and 18 ms once the table was analyzed (drivine#680). Autoanalyze puts it right on its own within about a minute with Postgres’s default settings; a read planned before then is slow until it ends.

  • On Oracle a text read out of JSON past 4,000 bytes is read whole where it is measured, cut, changed, searched or reversed; an empty text is null; reduce doesn’t run in a statement that writes, in the step of a reduce, or where its step reads a path; and map[key] reads null where an expression gives the key. Two lists ordered with <, <=, > or >= are walked item by item by a function the query declares too, so such a comparison is refused in a statement that writes and in the step of a reduce. Lists or maps that may hold a null are compared item by item, as Cypher compares them: where one holds a null, two written as the same JSON are null at once, and any other two are walked by the graph’s function <graph>_nulls_equal (see Connecting), in a statement that writes and the step of a reduce too. Where the store has no such function, a read declares it, and a statement that writes, or the step of a reduce, compares two such values that differ as JSON as false, so a = [1], of [null] AS a, is false there, where Cypher’s comparison is null. Lists and maps written out are compared item by item everywhere, so [null] = [1] is null.

  • On Oracle a year before 1, which Oracle numbers from -1 with no year 0, is renumbered as a date or a moment is read and written by the graph’s functions <graph>_era and <graph>_era_date (see Connecting), in a statement that writes and the step of a reduce too. Where the store has no such functions, a statement renumbers the year inline and gives the same rows, but Oracle takes longer to plan one of temporal arithmetic: seconds, and over a minute for a moment two CALLs deep.

  • On Oracle a property that a RETURN or a WITH returns or passes on (RETURN n.name) is read through its text, which costs about 2.4 to 3.8 times a plain read: 100,000 rows of RETURN n.name take about 490 ms, against 150 ms. This works around an Oracle bug, seen on 23.26.3: a string past about 32 KB taken from JSON as JSON, returned or fed on into more SQL, crashes the session (ORA-03113, with ORA-00600 [17114] or ORA-07445 in the alert log) over two or more rows. Only an item that is the property itself is read so: one inside an item (RETURN [n.name], collect(n.name)) is read as JSON, as before, and isn’t worked around. It goes once an Oracle release no longer crashes on the repro in docs/oracle-long-json-string-crash.md. Such a text still can’t be sorted by, grouped by or made DISTINCT: Oracle sorts and groups JSON only up to about 32 KB, and refuses the statement (ORA-40180), leaving the session open.

  • Writes are worked around for the same bug: a text that a write takes from an unwound parameter’s item, or from another value of its rows, to write it (UNWIND $rows AS r MATCH (n {id: r.id}) SET n.a = r.a, CREATE (:X {a: r.a}), MERGE … SET n.a = r.a) is read from the row’s text, at no cost measured: 1,000 rows set by one statement take as long as before. Any other value, a map set whole by SET n += r among them, is read as before.

  • So are reads of an unwound item’s value: a text that a RETURN or a WITH gives from an unwound parameter’s item, or from another unwound list’s (UNWIND $rows AS r RETURN r.a, WITH r.a AS a, SET n.a = r.a RETURN r.a), is read from the item’s text, at about 1.4 times a plain read: 100,000 rows of RETURN r.a take about 840 ms, against 600 ms. Any other value is read as before, and so is a value of a map made of a node’s properties and not unwound (WITH n {.a} AS m RETURN m.a); after a write, which carries its rows on, such a value is read from the item’s text too. A pattern that matches on such a text (MERGE (n:X {a: r.a})) compares it as JSON, which Oracle refuses past about 32 KB (ORA-40180), leaving the session open.

  • So are reads of a list’s item by its place (#279): an item read by index, by head() or last(), or after a slice, of a list split makes, a stored list, or one a WITH passes on (split(n.bio, 'x')[0], head(n.tags)), is read from the list’s text where it is a text, at about 1.4 to 2.5 times a plain read as drivine4j measured it, and the list is written out whole for it. Any other item is read as before, and so is an unwound item. A list made of a long property ([n.bio, 'y']) still takes the property out as JSON, as above.

  • So are reads of an argument of coalesce() that is a property of a node or a relationship (#295), wherever the coalesce() is (coalesce($p.q, n.bio), size(trim(coalesce(n.bio, ''))), SET n.a = coalesce(n.bio, '')): a text of at most 4,000 bytes is read as a VARCHAR2, at no cost drivine4j measured (20,000 rows take 72 ms either way), and a longer one through its text. Any other value is read as before, and so is a value of a map (coalesce(m.a, 'z')). In a step of reduce(), where a node arrives as JSON, the property is still taken out as JSON and can close the session (#331).

  • On Oracle two texts are compared (=, <>, <, >=, IN …) as VARCHAR2s, and as CLOBs with DBMS_LOB.COMPARE where either is past 4,000 bytes (#291). As JSON, = of a text past about 32 KB was refused (ORA-40180), even n.bio = 'abc' where one node’s bio was that long, and < of one read from the store over two rows or more crashed the session (ORA-03113), as the bug above did. A property compared so is read by its own path, and such a query carries NO_JSON_TABLE_TRANSFORM (see below). Beside a number, a truth value or a short text the statement gives or writes, a list or a map it writes, or an IN of such items, only the other side is read for its kind (n.name = $x, n.name = 'abc', n.name IN $names): over 20,000 rows that costs about a fifth more for = and IN, and an order (n.name > $x, n.i < 10000) takes three fifths to two thirds of the time it took. Beside a single value the statement writes, a list or a map is not equal and isn’t compared as JSON. Where no item of an IN the statement gives or writes, none a list or a map, is equal, the answer is null where they hold a null and else false, and the value isn’t read again for its nulls, which took a text past 32 KB out as JSON and crashed the session (drivine4j#475). A statement that writes, and the step of a reduce, which run as PL/SQL, compare as JSON, as before, and refuse a text past 32 KB: there a CLOB read from an unwound row’s JSON crashed the session. A list or a map that holds such a text is compared as JSON anywhere, and refused. Between two texts, either past 4,000 bytes, that first differ where one has a character past U+FFFF and the other one from U+E000 to U+FFFF, the order is the other way round from Cypher’s, as a CLOB compares by UTF-16 units. See “Two texts, compared” in docs/oracle-long-json-string-crash.md.

  • On Oracle an OPTIONAL CALL or an OPTIONAL MATCH is joined ON 1 = 1, where Postgres joins ON TRUE. This works around an Oracle bug, seen on 23.26.3: in a scalar subquery, as a COUNT { … } is compiled, a join ON TRUE is refused (ORA-00907) or crashes the session (ORA-03113, with ORA-07445 in the alert log) (#200). It goes once an Oracle release no longer fails the repro in docs/oracle-on-true-in-scalar-subquery-crash.md.

  • On Oracle a compiled query that reads the graph’s node or relationship table twice or more, and each query of a write that does, carries the hint NO_JSON_TABLE_TRANSFORM. This works around an Oracle bug, seen on 23.26.3: Oracle’s JSON table transformation reads several JSON values of one node’s row by one JSON_TABLE, and so rewritten beside another node’s table the query lost that node’s label and property tests, giving rows its pattern doesn’t have and values of the wrong node, or crashing the session (ORA-03113) (#258). A query of one table carries no hint, unless it compares a property (see above): there the transformation lost the property’s reads beside a quantifier’s subquery.

  • On Oracle a list worked out on each row by a subquery (keys(n), [i IN range(1, k) | i], [(p)-->(q) | q.name]) is read from a derived table inside a lateral view. This works around an Oracle bug, seen on 23.26.3: a subquery that reads the row, in the select list of a lateral view that begins a subquery’s FROM, was worked out once for the statement and not for each row, so WHERE 'b' IN keys(n) found nothing (#272).

  • On Oracle a node compared with a node (c = q, c <> q, c IN [q]), or an id with an id (id(c) = id(q)), compares their keys with + 0 on one side and again the other way round, `(n4.nid = n2.nid + 0 AND n2.nid = n4.nid

    • 0)`, so either node is still read by its key. This works around Oracle rewrites, seen on 23.26.3, of a key equal to a key: Oracle dropped the later node’s join and tested its label in a form that misses a node with more than one label, or read the other node by the key of a node it had dropped, and gave no rows (#393, #396).

  • On Oracle, a compiled node with a label whose key a WHERE sets equal to another node’s (a = b, id(b) = id(a), as a condition the row must pass, not under NOT or beside an OR) tests its labels by their text, which the index over labels can’t answer, where the other node is found by a label, properties, a relationship or its id: Oracle then reads it by its primary key. With the index’s form, Oracle 23.26 read that index again for each row of the other node, so MATCH (a:Big) MATCH (b:Big) WHERE a = b took 1.5 s over 4,000 nodes, growing with their square (#404). A query that holds such a test carries the hint OPT_PARAM('_optimizer_use_feedback' 'false'): Oracle 23.26’s statistics feedback otherwise planned the second and third runs of a new cursor again from what the first run found, and read the node the slow way, 1.5 s each over 4,000 nodes (#415). The earlier of two such nodes keeps the index’s form, and so does a node equal to one found by nothing else (MATCH (a) MATCH (b:Rare) WHERE a = b), where the index is the way in.

  • On Oracle, a compiled DELETE that reads the ids it deletes from its match (DELETE FROM … WHERE nid IN (SELECT n1.nid + 0 …)) reads the key with + 0. This works around an Oracle rewrite of the same family, seen on 23.26: Oracle unnested nid IN (SELECT n1.nid FROM <nodes> n1 WHERE <label test>) into the table itself and found no rows, so MATCH (g:Gone) DETACH DELETE g deleted nothing (#410, drivine4j#587). That subquery also tests each node’s labels by their text, which the index over labels can’t answer, in a LATERAL’s query too: planned with the index, Oracle crashed the session (ORA-03113, with ORA-07445 [qjsnmvFindOpt()+16] in the alert log) on MATCH (a:X) OPTIONAL MATCH (a)-[r:R]->(:X {k: 2}) DELETE r, and on the DETACH DELETE of an optional match’s node, after transactions that deleted and read nodes of the label had rolled back. The hint NO_ADAPTIVE_PLAN did not prevent it. The subquery then reads the node table whole, once (drivine#612, drivine4j#595).

  • On Oracle, a node a compiled MATCH finds after a WITH that groups its rows (one that aggregates or is DISTINCT, or a WITH after one) tests its labels by their text, which the index over labels can’t answer, in its pattern and in a WHERE such as WHERE m:Movie. With the index’s form, Oracle 23.26 sometimes crashed the session building the statement’s cursor (ORA-03113, with ORA-00600 [QJSNMV_GET_MVI_MD:2] in the alert log, or ORA-07445 [qjsnmvFindOpt()+16] after a rolled-back delete), on MATCH (p:Person)-[r:ACTED_IN]->() WITH p, collect(DISTINCT [r]) AS l, count(DISTINCT [r]) AS n MATCH (p)-[r2:ACTED_IN]->(:Movie {title: 'The Matrix'}) RETURN … (#418, drivine4j#601, drivine#620). Such a node is mostly found by its key, from a node the WITH passed on; one found by its label alone is read from the whole node table. The nodes before the WITH, a WHERE’s label test of a node the WITH passed on, and every other read keep the index.

  • What each statement gives is the Cypher spec, which the three Drivine libraries run alike.

The compiler is the shared one drivine4j and Drivine for TypeScript run, the native library of the drivine-cypher-compiler wheel, on the platforms it has a wheel for: see Cypher on a relational database.