Compiled Cypher: an aggregate in the WHERE of a WITH that aggregates or is DISTINCT is refused, though Neo4j filters the groups by it (port of drivine4j#497) #313

Port of liberation-data/drivine4j#497.

What happens now. With #310 (landing), an aggregate in any WHERE is refused, as in "count(*) aggregates, and a WHERE holds no aggregate". Neo4j 2026.10.1 runs one in the WHERE of a WITH that aggregates, or is DISTINCT. There the WHERE filters the groups, as SQL's HAVING does:

UNWIND [1, 1, 2] AS i WITH i, count(*) AS c WHERE count(*) > 1 RETURN i      // [1]
UNWIND [1, 1, 2] AS i WITH i, count(*) AS c WHERE sum(i) > 1 RETURN i        // [1, 2]
UNWIND [1, 2] AS i WITH i AS j, count(*) AS c WHERE j > count(*) RETURN j    // [2]
UNWIND [1, 1, 2] AS i WITH DISTINCT i WHERE count(*) > 1 RETURN i            // [1]

Neo4j refuses one only where the WITH groups nothing (WITH i WHERE count(*) > 1), or inside a comprehension's, a quantifier's or a pattern's own WHERE or body within that WHERE. A key the WITH groups by, written as its item is before its name, is read in that WHERE too. So WITH i AS j, count(*) AS c WHERE i > 1 and WITH DISTINCT a.name2 AS name WHERE a.name2 = 'B' (TCK WithWhere1 #2) both run. drivine4j refused both, as "i is not defined", until this fix. The scope check behind that is the one #269 reports here.

Where (#310's branch agg-refusals-306-307, 265d3d3):

• drivine/cypher/sql/aggregates.py:80-82: the With case calls self._where(clause.where) for every WITH, and _where at :126-136 refuses any aggregate.
• drivine/cypher/sql/sql_cypher_compiler.py:1465-1480 (_with), and the CALL path at :2608-2615: the WHERE is compiled over the projection's columns alone, so an aggregate or a key's pre-WITH form has nothing to read.

The fix in drivine4j (liberation-data/drivine4j#501):

• Aggregates.check takes a WITH as grouping when an item aggregates, outside comprehension bodies, patterns and subqueries, or when it is DISTINCT. For such a WITH, it refuses only an aggregate found where the compiler's children doesn't reach: a comprehension's, a quantifier's, a reduce()'s or a pattern's own expressions. The message now reads "… a WHERE holds no aggregate unless its WITH aggregates, …".
• SqlCypherCompiler.having(with) runs before the WITH is projected:
  • Each aggregate in its WHERE becomes an item of the WITH, named #havingN (or read by an item's name where an item is that aggregate already), and the WHERE reads that item.
  • A part of the WHERE equal to a key item's expression (an item that doesn't aggregate and names a variable) is read as that item. A part that names an item's name is left alone, so WITH i + 10 AS i … WHERE i + 10 > 20 reads the new i, as Neo4j does.
  • The #having items are removed from what the WITH binds once its WHERE is met, so RETURN * doesn't show them.
• The WHERE is still met after the WITH's ORDER BY, SKIP and LIMIT, as Neo4j meets it.

Tests to port:

• Spec: Pin an aggregate in the WHERE of a WITH that groups, as a filter over its groups drivine-cypher-spec#41 adds "Aggregate in the WHERE of a WITH that groups" to queries.yaml, and "Variable before a WITH that groups, in its WHERE" to refusals.yaml. It also changes the message of every "Aggregate in a WHERE" step, and adds more steps to it.
• drivine4j: CompilerRefusalTest "an aggregate in a WHERE is refused, as Neo4j refuses it", and CypherQueryCrossEngineTest "an aggregate in the WHERE of a WITH that aggregates or is DISTINCT filters its groups, as Neo4j does".
• TCK: in drivine4j, WithWhere1 Fragments and views: models, the model registry, and loading #2 passes on Postgres, Oracle and SQLite with the fix.