count(DISTINCT ...) with ORDER BY and LIMIT fails to compile on IRIS 2026.1 when the engine is created with from_connect() (the FETCH FIRST probe passes) (4.5.1)

Start in engine.py at _detect_fetch_first_unsafe() and compare the from_connect() probe path with the connection object's $ZVERSION fallback. Reproduce using the supplied count(DISTINCT ...) query with ORDER BY and LIMIT on IRIS 2026.1, then inspect the generated SQL and existing tests for FETCH FIRST versus TOP handling. Done when both engine creation paths execute the query successfully and the regression is covered.

A Cypher query that combines count(DISTINCT ...), ORDER BY on that count, and LIMIT fails with SQLCODE -400 when the engine is created with IRISGraphEngine.from_connect(...). The same query works when the engine is created from a connection object, and it works on both paths without LIMIT.

Environment

• iris-vector-graph 4.5.1 (PyPI, default dependencies: intersystems-irispython 5.4.1, intersystems-iris 5.3.3, iris-embedded-python-wrapper 0.6.1)
• InterSystems IRIS Community 2026.1 (Build 234U), official container containers.intersystems.com/intersystems/iris-community:2026.1, a new container for each run, namespace USER. Same result on irishealth-community:2026.1 (Build 235U)
• Client: Windows 11, Python 3.12.10, connecting over the network

Reproduction

import os

import iris
from iris_vector_graph.engine import IRISGraphEngine

PORT = int(os.environ.get("IRIS_PORT", "1972"))
PW = os.environ.get("IRIS_PASSWORD", "SYS")  # the SuperUser password of your instance

e1 = IRISGraphEngine.from_connect("127.0.0.1", PORT, "USER", "SuperUser", PW, embedding_dimension=4)
e1.initialize_schema()
for node_id, label, name in [
    ("Q948399", "Organization", "InterSystems"),
    ("Q7185789", "Person", "Phillip Ragon"),
    ("Q1882071", "Language", "MUMPS"),
    ("Q1150391", "Product", "InterSystems Caché"),
    ("L0645", "Product", "InterSystems IRIS"),
]:
    e1.create_node(node_id, labels=[label], properties={"name": name})
for s, p, o in [
    ("Q948399", "FOUNDED_BY", "Q7185789"),
    ("Q1150391", "DEVELOPED_BY", "Q948399"),
    ("L0645", "DEVELOPED_BY", "Q948399"),
    ("Q1150391", "IMPLEMENTS", "Q1882071"),
]:
    e1.create_edge(s, p, o)

e2 = IRISGraphEngine(iris.connect("127.0.0.1", PORT, "USER", "SuperUser", PW), embedding_dimension=4)

query = ("MATCH (x)-[:DEVELOPED_BY]->(c) "
         "RETURN c.name AS company, count(DISTINCT x.name) AS products "
         "ORDER BY products DESC LIMIT 5")
for engine in (e1, e2):
    r = engine.execute_cypher(query, {})
    print(engine._fetch_first_unsafe, r.rows, r.error)

Results

The error:

[SQLCODE: <-400>:<Fatal error occurred>] [Location: <Prepare>]
[%msg: <Error compiling cached query class %sqlcq.USER.cls45: ERROR: %sqlcq.USER.cls45.1(10) : <UNDEFINED>main+129^%qaqcasl *mt("top")>]

The SQL generated on the failing path:

SELECT %EXACT(p3.val) AS company, COUNT(DISTINCT %EXACT(p4.val)) AS products
FROM Graph_KG.nodes n0
JOIN Graph_KG.rdf_edges e2 ON e2.s = n0.node_id AND e2.p = ?
JOIN Graph_KG.nodes n1 ON n1.node_id = e2.o_id
LEFT JOIN Graph_KG.rdf_props p3 ON p3.s = n1.node_id AND p3."key" = ?
LEFT JOIN Graph_KG.rdf_props p4 ON p4.s = n0.node_id AND p4."key" = ?
GROUP BY %EXACT(p3.val)
ORDER BY %EXACT(SQLUser.CY_SORT_KEY(COUNT(DISTINCT %EXACT(p4.val)), 'x')) DESC
FETCH FIRST 5 ROWS ONLY

On the working path, the same query is wrapped as SELECT TOP 5 * FROM (... , ... AS __sort0 ...) __ob ORDER BY __sort0 DESC.

Where it seems to come from

from_connect() stores the connection parameters, so _detect_fetch_first_unsafe() (engine.py, around line 624) runs the subprocess probe. The probe query (SELECT a.id FROM ivg_ff_probe_a a JOIN ivg_ff_probe_b b ON b.sid=a.id FETCH FIRST 1 ROWS ONLY) succeeds on 2026.1, so the engine marks the build as safe and emits FETCH FIRST. But FETCH FIRST together with ORDER BY CY_SORT_KEY(COUNT(DISTINCT ...)) still fails to compile on this build. An engine created from a connection object has no stored parameters, falls back to the $ZVERSION check, treats 2026.x as unsafe and emits TOP, which works.

In a larger graph, the same failure also happens with a one-node pattern (MATCH (c:Label) RETURN c.a, count(DISTINCT c.b) AS k ORDER BY k DESC LIMIT 1), with any LIMIT. count(c) with ORDER BY and LIMIT works.

Context: we use iris-vector-graph in Medichrona, a viewer that turns Synthea patient records (FHIR R4) into a temporal knowledge graph and shows it in 3D (15 s teaser, walkthrough, overview).