Skip to content
Drivine

Cypher on Oracle and Postgres

A Cypher statement given to a PersistenceManager on Oracle or Postgres is compiled to SQL over the graph’s tables, so a statement written for a graph engine runs there and gives the same rows. The compiler is drivine4j’s, ported: what a statement gives on each store, compiled or not, is the shared Cypher spec, which Drivine’s tests run on Neo4j, Postgres and Oracle. To get started, see Cypher on a relational database.

const names = await pm.query<string>(
    new QuerySpecification(cypherStatement('MATCH (p:Person)-[:KNOWS]->(f) WHERE p.name = $name RETURN f.name')).bind({ name: 'Keanu' })
);
  • A statement must say it is Cypher: a statement of no language is SQL on these stores.
  • The graph’s tables are made on the first connection to the store, on Oracle and Postgres alike, so Cypher runs on a new store with no object manager.
  • A row is one value: the one item a RETURN has, or a list of its items when it has more. A node is its properties, and so is each node or relationship of a list of them (collect(n), nodes(p)); compared, indexed, searched with IN or iterated, its items are the nodes themselves, so two with equal properties differ.
  • A statement that writes (CREATE, MERGE, SET, REMOVE, DELETE, FOREACH) runs as one statement, applied whole or not at all: an anonymous PL/SQL block on Oracle, and a function of the session’s own (pg_temp) on Postgres.
  • Variable-length relationships (-[:R*1..3]->, -[:R]->{1,3}) and quantified path patterns ((a) ((x)-[r:R]->(y) WHERE x.v < y.v){1,3} (b)) compile. A quantified path pattern’s variables are lists of what each repetition matched. On Oracle, whose recursive queries read nothing of the row they are in, its 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.
  • 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.
  • Lists and maps that hold a null compare item by item, as in Cypher: [null] = [null] is null, [1, null] = [2, null] false, and IN compares a value with each item as = does, so 2 IN [1, null] is null. On Oracle, 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, in a statement that writes and the step of a reduce too. Where the store has no such function (see Oracle), 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. Lists and maps written out in the statement, [null] = [1], are compared item by item everywhere.
  • <, <=, > 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. On Oracle, two lists so ordered are walked item by item by a function the query declares, so such a comparison is refused in a statement that writes and in the step of a reduce.
  • 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 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 drivine4j’s 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.
  • On Oracle, 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 drivine4j 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. See “In a PL/SQL block, from a JSON variable” in drivine4j’s docs/oracle-long-json-string-crash.md.
  • On Oracle, 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 800 ms, against 560 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.
  • On Oracle, so are reads of a list’s item by its place (#455): 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, 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. See “An item of a list, read by its place” in drivine4j’s docs/oracle-long-json-string-crash.md.
  • On Oracle, so are reads of an argument of coalesce() that is a property of a node or a relationship (#475), 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 (#516). See “An argument of coalesce()” in drivine4j’s docs/oracle-long-json-string-crash.md.
  • On Oracle, a text past 4,000 bytes is measured, cut, changed, searched, reversed and added to whole (size(), toUpper(), substring(), replace(), split(), reverse(), STARTS WITH, CONTAINS, =~, + …): Oracle reads a text out of JSON as a VARCHAR2 of at most 4,000 bytes, so a longer one is read again as a CLOB, and a short one isn’t. CONTAINS looks for a text past 32,767 UTF-16 units, which INSTR refuses (ORA-22828), by comparing it whole at each place its first 1,000 units are (#457), over an aggregate’s result too (head(collect(n.t)) CONTAINS $x), whose aggregates are worked out first (#414). A property such a function reads is read from the properties by its own path, which costs about a quarter more on short texts, and a + that may put texts together costs about a tenth more, as drivine4j measured.
  • On Oracle, two texts are compared (=, <>, <, >=, IN …) as VARCHAR2s, and as CLOBs with DBMS_LOB.COMPARE where either is past 4,000 bytes (#469). 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), and beside a single value it 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 drivine4j’s 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) (#377). It goes once an Oracle release no longer fails the repro in drivine4j’s 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) (#435). A query of one table carries no hint, but where it compares a property as a text (see above): there the transformation lost the property’s reads beside a quantifier’s subquery.
  • On Oracle, a compiled 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 (#596, #598).
  • 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 (#612, 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: each session already plans without adaptive plans, and the crash came as often with it, or with NO_INDEX. The subquery then reads the node table whole, once (#612, drivine4j#595).
  • 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, and takes about 10 ms now (#606). 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 (#614). 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 node with a label that a compiled pattern finds by a property (MATCH (n:Person {id: $id}), UNWIND $rows AS row MATCH (n:Person {id: row.id})) is compared by it as JSON alone for now, and Oracle reads the label’s nodes to find it. The compiler can read it through the label’s range index, or a uniqueness constraint over the property alone, where it is told the graph has one (OracleCypherCompiler’s indexedProperties); the connection doesn’t tell it yet (drivine4j#638). Told so, it compares the node as JSON and, beside that, as that index reads it, so Oracle finds the node through the index; its labels are tested by their text, and an UNWIND whose item is compared so carries the hint CARDINALITY(<alias> 10), as Oracle takes a list given as a value for 8,168 rows and hashed every node of the label to them. Compared as JSON alone, inside a CASE, even a parameter’s value read every node of the label, once for each row of an UNWIND (#636, drivine4j#617). A row’s value that may be a list, such as one worked out, is let through to the JSON comparison too, which Oracle reads no index beside; one a parameter gives, or a list written of literals, maps, lists and parameters, as APOC’s export writes its rows, is known to hold none where it holds none. An empty text, and one past 4,000 bytes, are compared as JSON alone, as the index holds no text for them. A node found after a WITH that groups is compared as JSON alone (below).
  • 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-00600 [QJSNMV_GET_MVI_MD:2], NJS-500 to the client), 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 …, while other sessions made and dropped tables with indexes of their own (#620, drivine4j#320). 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.
  • 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 (#448).
  • On Postgres, float arithmetic, sum() and avg() are worked out in exact decimal (numeric), not as doubles, so they are 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).
  • Cypher the compiler doesn’t take is refused by name before the store is sent anything, e.g. A path variable in CREATE or MERGE is Cypher, and is not compiled to SQL yet.
  • 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.
  • 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.
  • A temporal value is its ISO 8601 text, where a graph engine’s driver gives a value of its own. A year is numbered as Neo4j and ISO 8601 number it on every SQL store, 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 (#511). On Oracle and Postgres 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, 979 characters on Oracle and 703 on Postgres before, is 2,805 and 2,211, and 1,721 on Oracle where the store keeps <graph>_era), and in drivine4j, which compiles the same SQL, an aggregate of temporal arithmetic runs 8 to 10% slower (a truncate on Postgres, about 16%). SQLite’s calendar, Drivine’s functions, numbers years so already. 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. On every SQL store, two stored values BC compare by their text, which orders them backwards (#415); on SQLite, whose timestamps are texts, so do two the statement makes. A JavaScript Date BC given as a parameter is written with six digits of its year, as toISOString writes it (-000044-03-15T…), which the stores don’t read as that year: give its ISO text, -0044-03-15T…, to 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.
  • A parameter is bound as JSON, so a number that is whole is a whole number, though it was written 2.0: toString($f) of it gives '2', where Neo4j, which the driver sends a number to as a float, gives '2.0'.