Correlated COUNT { ... } subqueries with outer-variable access may evaluate as zero (original) (raw)

ArcadeDB version
Observed on Docker images:

Older images also behave incorrectly, but differently:

On those older lines, the same correlated form currently returns null instead of an integer.
On the 26.4.x line, the subquery is accepted and typed as an integer, but still evaluates to the wrong value 0.

Environment

Describe the bug
On ArcadeDB 26.4.1-SNAPSHOT and 26.4.2, a correlated COUNT { ... } subquery may evaluate as 0 when the inner body compares against an outer variable.

In the minimized repro below, the inner subquery clearly produces rows:

Neo4j returns 2, 1, and 0.
ArcadeDB returns 0 for every row.

This does not look like a general COUNT { MATCH ... } problem.
If the inner MATCH is uncorrelated, ArcadeDB counts correctly.
The failure starts when the inner predicate reads an outer variable such as p.age.

To Reproduce

Setup:

CREATE (:Person {name:'Alice', age:30}), (:Person {name:'Bob', age:25}), (:Person {name:'Charlie', age:35}), (:Person {name:'Diana', age:40});

Query:

MATCH (p:Person) WHERE p.age > 25 RETURN p.name AS name, COUNT { MATCH (p2:Person) WHERE p2.age > p.age RETURN p2 } AS older_count ORDER BY name;

Expected behavior
Observed Neo4j result:

Alice,   2
Charlie, 1
Diana,   0

Actual behavior
Observed ArcadeDB 26.4.1-SNAPSHOT / 26.4.2 result:

Alice,   0
Charlie, 0
Diana,   0

Observed ArcadeDB latest / 26.3.2 result:

Alice,   null
Charlie, null
Diana,   null

So the correlated COUNT { ... } does not count the matching rows correctly.

Control cases

The same logic written with ordinary aggregation behaves correctly:

MATCH (p:Person) WHERE p.age > 25 OPTIONAL MATCH (p2:Person) WHERE p2.age > p.age RETURN p.name AS name, count(p2) AS older_count ORDER BY name;

Observed result on Neo4j and all tested ArcadeDB versions:

Alice,   2
Charlie, 1
Diana,   0

So the problem is not with the comparison p2.age > p.age itself.
It is specific to the correlated COUNT { ... } form.

If the COUNT { ... } body is left uncorrelated, ArcadeDB 26.4.1-SNAPSHOT / 26.4.2 also behaves correctly:

MATCH (p:Person) WHERE p.age > 25 RETURN p.name AS name, COUNT { MATCH (p2:Person) WHERE p2.age > 30 RETURN p2 } AS older_count ORDER BY name;

Observed result on Neo4j and ArcadeDB 26.4.1-SNAPSHOT / 26.4.2:

Alice,   2
Charlie, 2
Diana,   2

This makes the boundary much clearer: