Standard SQL next to Cypher: measured evidence from LLM agents and Postgres-wire tools

[tae898]

I ran two small experiments on a question for after 26.11.1, not for this release: should ArcadeDB's SQL get a standard core (ANSI joins and a real catalog) next to Cypher? More and more databases are created and queried by AI agents (Databricks reported that over 80% of the databases provisioned on Neon are created by AI agents), and agents, like BI tools and drivers, speak standard SQL and Cypher. I wanted numbers on what that means for ArcadeDB, not opinions.

Everything is reproducible from one commit: harness, pre-registered rules and scripts. The rules were written before any run; nothing below was tuned after seeing results.

1. Which query languages LLMs write correctly

40 questions over a slice of the LDBC social network (SF1: 9,892 persons, 180,623 friendships, forums, tags and places), ten each of: one type, one join, several joins, and a graph pattern. Each question was asked in five languages, zero-shot, schema plus question, "answer with the query only". Claude Sonnet 5.5 and Claude Haiku 5.5, three samples each, 1,200 generations. Every generated query was executed and its result compared with a reference answer that was itself checked on every engine. ArcadeDB 26.10.2 (latest stable); DuckDB 1.5.6 stands in for an ANSI SQL engine.

Correct answers (95% Wilson interval):

language	Sonnet 5.5	Haiku 5.5
ANSI SQL (run on DuckDB)	95.7% [90.4, 98.2]	99.1% [95.3, 99.8]
Cypher (ArcadeDB)	92.3% [86.0, 95.9]	98.3% [94.0, 99.5]
ArcadeDB SQL (ArcadeDB)	39.3% [30.9, 48.4]	71.8% [63.0, 79.2]
ArcadeDB SQL with a one-page reference card	56.4% [47.4, 65.1]	92.3% [86.0, 95.9]
SQL/PGQ (DuckDB with DuckPGQ)	32.5% [24.7, 41.4]	19.7% [13.5, 27.8]

Where ArcadeDB SQL loses its answers, by question group:

ArcadeDB SQL	one type	one join	several joins	graph pattern
Sonnet 5.5	100.0% [88.6, 100.0]	26.7% [14.2, 44.4]	26.7% [14.2, 44.4]	0.0% [0.0, 12.5]
Sonnet 5.5 with the card	100.0% [88.6, 100.0]	36.7% [21.9, 54.5]	53.3% [36.1, 69.8]	33.3% [18.6, 52.2]
Haiku 5.5	100.0% [88.6, 100.0]	80.0% [62.7, 90.5]	33.3% [19.2, 51.2]	74.1% [55.3, 86.8]
Haiku 5.5 with the card	100.0% [88.6, 100.0]	100.0% [88.6, 100.0]	90.0% [74.4, 96.5]	77.8% [59.2, 89.4]

The reference card answers the "agents can read the docs" objection only in part: it lifts Haiku from 71.8% to 92.3%, close to standard SQL, and Sonnet from 39.3% to 56.4%, and it has to be in every prompt.

An observation the rules did not score, but worth reporting: on languages it is unsure of, Sonnet stops following "query only" and answers with prose and several candidate queries, often saying it is not sure the syntax is valid. Answers that are not a single query: ANSI SQL 4/120, Cypher 7/120, ArcadeDB SQL 65/120, ArcadeDB SQL with the card 23/120, SQL/PGQ 59/120. Those count as failures (the extraction rule takes one query), which is largely why Sonnet scores below Haiku on ArcadeDB SQL; Haiku answers with a single query nearly always, right or wrong.

The standard SQL the models write is portable: of the 228 answers correct on DuckDB, 225/228 = 98.7% [96.2, 99.6] are also correct unchanged on PostgreSQL 18 and 219/228 = 96.1% [92.7, 97.9] on SQLite. So the result is about standard SQL, not about DuckDB's dialect.

2. What Postgres-wire tools can do today

Six common clients against the Postgres wire, each step a pass or fail. Each cell reads 26.10.2 / the 26.11.1 snapshot (which has the catalog fixes made after #10228):

client	connect	list tables	list columns	SELECT	parameter	JOIN	GROUP BY
psql	yes / yes	no / yes	no / no	yes / yes	yes / yes	no / no	yes / yes
psycopg	yes / yes	no / no	yes / yes	yes / yes	yes / yes	no / no	yes / yes
sqlalchemy	yes / yes	yes / yes	yes / yes	no / no	yes / yes	no / no	no / no
pgjdbc	yes / yes	yes / yes	yes / yes	yes / yes	yes / yes	no / no	yes / yes
grafana	no / no	no / no	yes / yes	yes / yes	yes / yes	no / no	yes / yes
metabase	yes / yes	no / no	no / no	yes / yes	yes / yes	no / no	yes / yes

What fails, from the server's errors: every JOIN ("mismatched input 'JOIN'"); the catalog queries psycopg, Grafana and Metabase send return no tables (psql's \dt works on the snapshot); SQLAlchemy's LIMIT $1::INTEGER is a parse error on 26.10.2 and its reflection query is refused on the snapshot ("This catalog query has a shape this server does not support"); Grafana's health check -- ping is rejected as an empty statement. The catalog emulation improves release by release, but every client sends its own catalog queries, and every client's JOIN fails.

3. What others do, and the counterpoints

• Document and multi-model databases keep a join: Couchbase SQL++, CrateDB, MongoDB's $lookup, ArangoDB AQL, SurrealQL.
• Graph querying converges on Cypher-style patterns: Neo4j's GQL conformance, Oracle's SQL/PGQ, DuckPGQ. My numbers say SQL/PGQ is the hardest of the five for LLMs today, and DuckPGQ segfaulted on one correct reference query at this size.
• Counterpoints I take seriously: the guidance in Feature completeness of different protocols etc. #4472 is Cypher for traversals and SQL mainly for OrientDB users, and the data above agrees on the graph side. OrientDB decided against joins (orientdb#7888). I found no issue here that asks for JOIN directly; the demand shows up through tools (PostgreSQL wire: catalog queries that psql and psqlodbc send come back empty, unfiltered or as a syntax error (parenthesised join, join to pg_am, OPERATOR(pg_catalog.=), UNION, count(*)), so \dt and SQLColumns find nothing #10228, ArcadeDB into Apache Arrow through ADBC, using psqlodbc 16 over the PostgreSQL wire (adbcBridge 0.1.0) #6888) and agents.

4. A suggestion, for after 26.11.1

1. Keep Cypher as the graph language, moving toward GQL. Nothing to change there.
2. Add a standard core to ArcadeDB SQL: INNER and LEFT JOIN on equality using indexes, standard casts (:: and CAST), empty and comment-only statements, and catalog queries (pg_catalog, information_schema) answered by the engine over its schema instead of shape by shape. Documents are where it matters most, but every type can appear as a table: a document or vertex type as a table of its records, an edge type as a link table whose rows carry their two ends (@out, @in) and the edge properties, so tools can list and query all of them. The existing dialect stays as it is.
3. Keep each language faithful to its own standard instead of making the two mirror each other: ANSI SQL semantics for SQL (tables, joins, NULL logic, the catalog), openCypher moving toward GQL for Cypher (patterns, paths). Both read the same records in the same transactions, and a docs page says which language to use for what: Cypher for traversals and patterns, SQL for documents, tables and tools, the existing dialect for what neither covers.
4. Leave SQL/PGQ for later.

The 40 questions and the tool matrix could become acceptance tests: the matrix runs against a snapshot in a few minutes and shows which clients a change unblocks. I am happy to help: turn them into a CI job, test branches, or send PRs for parts of the core where that is useful to you.

Limits: one schema; Claude models only; DuckDB stands in for an ANSI core ArcadeDB does not have yet; the LLM figures use ArcadeDB 26.10.2.

Edited 2026-11-05: point 2 now says that every type, edge types included, can appear as a table, and point 3 that each language stays faithful to its own standard (both from the discussion below); nothing measured changed.

[tae898]

One clarification on point 2 of the suggestion, which says "for document types": that is where the standard core matters most (joins on keys, aggregation, BI tools), but it does not need to stop there. Every type can appear as a table under the same rules: a document or vertex type as a table of its records, an edge type as a link table whose rows carry their two ends (@out, @in) and the edge properties. That lets the catalog list every type and lets a tool such as Metabase or Grafana query any of them, while traversals stay with Cypher, which is shorter and which LLMs wrote correctly far more often in my test. A planner can also recognise a join through an edge type and walk the stored adjacency instead of joining, the way graph-on-SQL systems do.

[tae898]

I folded the clarification above into the suggestion in the post (point 2), and added point 3: keep ANSI SQL and Cypher each faithful to its own standard instead of making them mirror each other, with a docs page on which language to use for what. Nothing measured changed.

[lvca]

This is an old topic always relevant. I am in favor of updating our SQL to be more standard. The idea of the join is good, when we have edges the planner should transform it in a traversal. The only reason why we never did it is because the vast majority of failure on using OrientDB and ArcadeDB we have seen so far is the lack of graph modeling.

OrientDB and ArcadeDB can work as a RDBMS, but you lose the main advantage you have with a native GraphDB ending up doing many joins. Not having JOIN in our syntax forces the user to discover how to traverse relationship.

After all all of us come from a RDBMS background. Any idea is welcome.

[tae898]

Thank you, Luca. All three together works for me, and I will start on it.

Agreed on the warning. A join between two types that no edge or link connects is almost always a missing relationship, so I would log it once for each distinct statement text (a bounded set of statements already seen, so a hot query cannot flood the log) and say what is missing, for example that no edge type connects Person and Company and that a relationship may be needed.

Next from my side is a short design note in this thread: which parts of the SQL parser, the planner and the catalog each of the three steps touches, how I test it (the LLM question set, the Postgres-wire tool matrix, and the engine's own unit tests on JDK 21 and 25), and what I would leave out of the first version. I am finishing the Python 26.11.1 release first, so expect the note within the week.

[tae898]

Design note: a standard SQL core next to Cypher (steps 1 to 3 and the join warning)

This note follows the plan from this thread: three steps, each its own pull request with tests, existing ArcadeDB SQL unchanged, no performance work on the existing paths. Everything below comes from reading main at 42b9b66326; I have not run or built anything yet, and the file and line references are in a separate evidence file I can attach. Two findings changed my first plan, so they come first.

What reading the code changed

• The Postgres wire catalog is already generated from the schema, not canned (PostgresCatalog, information_schema.tables/columns/schemata and the pg_* set), with its own small join and :: evaluator inside postgresw. So step 1 does not start a catalog. It makes information_schema reachable from the SQL engine itself (embedded, HTTP, Studio), and leaves postgresw as it is until the engine join exists.
• An edge type does not record its endpoint vertex types (only bidirectional, lightweight, unique). The rewrite in step 3 does not need them, because it recognises the shape e.@out = a.@rid AND e.@in = b.@rid. The warning is the part that would like them (see below).

Step 1: additive pieces (three small pull requests)

• 1a casts: CAST(x AS type) and x::type. Adds a :: token and a CAST rule to SQLLexer.g4 and SQLParser.g4, one AST node with the usual copy/toString/equals, a SQL type name to Type mapping, and a case in SQLLiteralParameterizer. It is strict: a value that cannot convert raises, unlike the lenient convert() method. Test: TestHelper cases for both spellings, a bound $1::INTEGER (the SQLAlchemy LIMIT shape), an invalid cast, and unchanged parsing of bucket::x and :name. Size S to M: the grammar change is small, the type table and the node boilerplate are the work.
• 1b empty and comment-only statements: the parse rule requires one statement, so -- ping and /* x */; are errors in the engine (the wire already answers ; and blanks as an empty query). The engine returns an empty result for them and the wire maps that to EmptyQueryResponse. Note the lexer only reads -- with a space as a comment. Test: engine query and a wire IT next to Issue11028EmptyParseIT. Size S.
• 1c catalog in the engine: FROM information_schema.tables|columns|schemata, a new fetch step beside the FetchFromSchema*Step classes that serve schema:types, built from the same schema calls PostgresCatalog uses. Test: types created in a test appear with the right names, types and order. Size M.
• Left out of step 1: pg_catalog (stays in postgresw), SET/SHOW/SELECT version() (wire only today and fine), a CAST type list beyond the common scalar types, and any change to postgresw behaviour.

Step 2: INNER and LEFT JOIN on equality between types

• What it adds: SELECT ... FROM A a [INNER|LEFT [OUTER]] JOIN B b ON a.x = b.y [AND ...], one or more joins, equality conditions only. If the inner side has an index on its join key the step is an index nested loop (the inner SELECT is planned once with the key as a parameter, like the correlated sub-plans of LET); otherwise a hash join whose build side is capped by OperationHeapLimit and fails with a clear message instead of growing without bound.
• Files: SQLParser.g4 (a join clause in selectStatement only, since fromClause is also used by UPDATE, DELETE and TRAVERSE), SQLASTBuilder (aliases are erased at parse time today, so several aliases in scope is real work), new JoinClause AST nodes, SelectExecutionPlanner.handleFetchFromTarget (one new branch that plans each source with the existing methods), new IndexNestedLoopJoinStep and HashJoinStep with prettyPrint for EXPLAIN. Joined rows are one property per alias, as MATCH rows are, so projection, WHERE, ORDER BY and GROUP BY are reused.
• JOIN, LEFT and INNER are legal type, property and alias names today. I propose soft keywords (recognised only where a join can start) so no existing query changes meaning; that needs a grammar predicate and a test that SELECT FROM left and ... AS join still parse.
• Test: two indexed types, INNER and LEFT results against hand-computed rows, a join with no index, a name that is also a keyword, EXPLAIN names the step. Size L: new grammar, alias model, two executor steps, and the most places to break existing parsing.
• Left out: RIGHT, FULL and CROSS joins, FROM a, b, USING, non-equality ON, NATURAL, and join reordering across more than a few sources. SELECT * over a join needs a decision (qualified names, or flatten and error on duplicates) that I would like to settle in the pull request.

Step 3: a join through an edge type becomes a traversal

• What it adds: an edge type is a table whose rows carry @out, @in and the edge properties (lightweight edges too, through the vertex walk the planner already has). When a join has the shape JOIN E e ON e.@out = a.@rid JOIN B b ON e.@in = b.@rid, the planner builds the existing vertex-centric edge fetch (the @out = RID path in SelectExecutionPlanner) instead of a join step. EXPLAIN says so on the step: that it is a traversal, and the equivalent Cypher MATCH (a)-[e:E]->(b).
• Files: SelectExecutionPlanner (a recogniser before the join step is built), the join steps' prettyPrint. No grammar change beyond step 2.
• Test: the same data with an edge type, join result equals the Cypher MATCH result, EXPLAIN contains the note, a lightweight edge type gives the same rows. Size M, and only after step 2.
• Left out: rewriting joins on a LINK property (planned as a join first), variable-length paths (stay Cypher), and an unconstrained "any edge between A and B" join.

The warning

• Rule: log once per distinct statement when a join connects two vertex types with a condition that is neither @rid/@out/@in based nor backed by a LINK property of one side to the other. Joins on document types are never warned about, since they have no edges to miss.
• Where: at planning, when the join step is built. The flag lives on the cached parsed statement (the statement cache is a bounded LRU, arcadedb.sqlStatementCache, 300 by default), so memory is bounded and a plan that is rebuilt does not log again; the same idiom as IncomingEdgeLookup's warned flag. Level WARNING the first time.
• Switch: GlobalConfiguration entry arcadedb.sqlJoinWithoutEdgeWarning, database scope, boolean, default on.
• Message: the statement text, "joins Person and Company by value on p.cid = c.id; no edge connects them in a way this planner can see; if this is a relationship, an edge type may be missing".
• Limit: without declared endpoints I cannot say "no edge type connects A and B", only that this join does not use one. A sharper message needs an endpoint declaration (for example CREATE EDGE TYPE ... FROM A TO B, or custom metadata), which is a schema decision for you, not something I will add on my own.

Order of work and unknowns

• Order: 1b, 1a, 1c in parallel (small, independent), then step 2 behind them, then step 3 and the warning together on top of step 2. Each its own pull request, rebased on main.
• Unknown: whether soft keywords in the grammar are acceptable to you, or whether you would rather reserve the words in a major release.
• Unknown: how to treat SELECT * over a join, and whether tools need the flattened shape.
• Unknown: whether endpoint declarations on edge types are wanted, now or later.
• Unknown: how many of the failing cells of my six-client matrix still fail on main after the recent catalog fixes. I will re-run it on main before step 1 and post the result.
• Unknown: where the docs for the new syntax go (I saw they live in arcadedb-docs); I will open a docs pull request there with each step.
• Open pull request feat: columnar faceting (plans 1-6) #10855 touches SQLParser.g4 and SQLLexer.g4, so my grammar pull requests will rebase on it.
• Tests run on JDK 21 as in your CI; I also run them on 25 on my side.

[gramian]

Since discussing SQL changes, I just link #893

[tae898]

Thanks for the link, gramian. That discussion is about the spelling of the DDL (CREATE TYPE and CREATE PROPERTY), which this note does not touch: my scope here is the query side (casts, empty statements, the catalog, JOIN), and existing statements stay as they are. A standard-style DDL could be a later step of its own, but I have not looked at it yet.

[tae898]

Following the design note, I studied a second option and would like your view before I write any code.

Option A (the note): extend the existing sql language. Option B: add a separate language, working name postgresql, with its own grammar and binder that translates into the existing SQL statement objects and planner. It would be experimental, off by default, and sql stays untouched. Registering a language is small (QueryEngineManager.register; the mongo engine is a roughly 200-line example). The execution layer is the hard part in both options, since there is no join step today and the planner and its steps take the SQL AST. Cypher did not reuse SQL steps (it has its own planner and 62 step classes), so B is only cheap if translating into SQL AST nodes works for every shape. A spike would show that.

Why B is attractive: the three risks of A disappear (aliases erased at parse time, JOIN, LEFT and INNER legal as names today, a grammar shared with UPDATE, DELETE and TRAVERSE). And for vectors, the existing lexer reads <=> as null-safe equality, which blocks pgvector's cosine-distance operator in sql and costs nothing in a new language. The cost of B is two SQL stacks next to a planner that changes weekly.

On vectors in general: Cypher already has the Neo4j syntax in 26.11.1. For SQL I would follow pgvector (vector(n), <->, <=>, <#>, ORDER BY col <=> q LIMIT k, CREATE INDEX ... USING hnsw), mapped onto the LSM_VECTOR index. The planner would need a rule that turns ORDER BY distance with LIMIT k into an index search, and I would check results against real pgvector.

My proposal is a spike first: one JOIN query parsed by a new grammar, translated to the existing steps, run over the Postgres wire and compared with PostgreSQL. It needs no engine change. Would you prefer A, B, or A now and B later if the spike works?