Multiple standard features not supported

[e7nd7r]

Context

Gnapsis is a code intelligence graph being developed to help AI assistants understand codebases through semantic relationships. It's built on top of Apache AGE (PostgreSQL + graph capabilities), and Cypher is used for all graph operations.

During the migration from Neo4j to AGE, several openCypher standard features were found to be unsupported. This issue was created to document these findings in one place to help track compliance and potentially guide future contributions.

Discovery Process

All Cypher queries were systematically tested against AGE, and failures were cross-referenced against the official openCypher specification:

• openCypher9.pdf
• Grammar files
• Accepted CIPs

Incompatibilities Found

Feature	openCypher Reference	AGE Error
MERGE ... ON CREATE SET	openCypher9.pdf, style-guide	syntax error at or near "ON"
MERGE ... ON MATCH SET	Same as above	syntax error at or near "ON"
datetime() function	CIP2015-08-06-date-time	function datetime does not exist
NOT (pattern) in WHERE	basic-grammar.xml:316, L405	syntax error at or near ":"

Examples

MERGE ON CREATE/MATCH SET (Related: Issue #1619)

-- Standard openCypher (not supported in AGE)
MERGE (n:Person {name: 'Alice'})
ON CREATE SET n.created_at = timestamp()
ON MATCH SET n.updated_at = timestamp()
RETURN n;

-- Error: syntax error at or near "ON"

datetime() function

-- Standard openCypher (not supported in AGE)
RETURN datetime() as now;

-- Error: function datetime does not exist

NOT pattern predicate

-- Standard openCypher (not supported in AGE)
MATCH (e:Entity)
WHERE NOT (e)-[:BELONGS_TO]->(:Entity)
RETURN e;

-- Error: syntax error at or near ":"
-- Note: The issue is with anonymous node patterns (:Label) inside NOT predicates

Features That DO Work (verified)

These were initially suspected but confirmed to work correctly:

• ✅ EXISTS { subquery } - Works as expected
• ✅ count() on empty MATCH - Returns 0 correctly
• ✅ RETURN after DELETE - Works as expected

Workarounds

Workarounds for each issue have been documented here: AGE_CYPHER_COMPATIBILITY.md

For example:

• ON CREATE SET → A check-then-create pattern can be used with separate MATCH and CREATE statements
• NOT (pattern) → OPTIONAL MATCH combined with IS NULL check can be used
• datetime() → Timestamps can be passed as parameters from the application layer

Contribution

Some analysis on the ON CREATE SET implementation has already been done: NOTES_ON_CREATE_SET.md

PRs would be gladly contributed for any of these features if the team is interested. Which features would be considered highest priority for the project?

AGE is a fantastic project - having graph capabilities in PostgreSQL is greatly appreciated! 🐘

---

Tested on AGE 1.6.0 with PostgreSQL 17.7

[jrgemignani]

@e7nd7r Thanks for compiling this! We can use it as we add more functionality.