count(*) returns 0 while count(r) returns 1 for a relationship inline WHERE predicate #5463

ArcadeDB version

ArcadeDB 26.8.1-SNAPSHOT

Build:

1134185d5494f5ab0583da4a508d846449f8e04c/1785129272315/main

Environment

• Host OS: Linux x86_64
• Deployment: Docker (arcadedata/arcadedb:latest)
• Runtime: OpenJDK
• Query interface: HTTP command API
• Query language: Cypher

Description

A MATCH containing a relationship inline WHERE predicate returns different aggregate results depending only on whether the query uses count(*) or count(r).

For the same matched pattern:

• count(*) returns 0
• count(r) returns 1
• directly returning r.tag produces the expected matching row

Because r is bound by a regular MATCH and is non-null for every produced row, count(*) and count(r) should return the same value.

The failure specifically appears when count(*) is combined with property access on the relationship variable inside the inline predicate:

[r:E WHERE r.tag = "ok"]

A literal inline predicate and the equivalent clause-level WHERE form both work correctly.

Related issues

This appears related to, but distinct from, several previous issues:

• ArcadeDB may ignore a relationship predicate written directly inside the relationship pattern. #3951/fix: inline relationship WHERE predicate is now applied #3953 concerned relationship inline WHERE predicates being ignored during regular MATCH evaluation. The current predicate is not simply ignored: if it were ignored, both relationships would be counted and the result would be 2, not 0.
• count(*) with inline-filtered start node and anonymous endpoint returns global relationship count #5071 concerned count(*) returning the global relationship count for a pattern with an inline-filtered source node and an anonymous endpoint. The current result is 0 and requires a relationship inline predicate.
• Cypher count(*) after OPTIONAL MATCH drops null rows #5094 concerned count(*) dropping null-preserving rows after OPTIONAL MATCH. The current reproduction uses a regular MATCH and contains no optional rows.

Steps to reproduce

Run each statement separately against a clean database.

1. Clean the database

MATCH (n)
DETACH DELETE n;

2. Create the test graph

CREATE (a:A {v:1}), (b:A {v:2});

MATCH (a:A {v:1}), (b:A {v:2})
CREATE
  (a)-[:E {tag: "ok"}]->(b),
  (a)-[:E {tag: "bad"}]->(b);

The graph contains two :E relationships:

tag = "ok"
tag = "bad"

Exactly one relationship satisfies r.tag = "ok".

3. Failing query

MATCH (a:A {v:1})-[r:E WHERE r.tag = "ok"]->(b:A)
RETURN count(*) AS c;

Actual result:

c = 0

Expected result:

c = 1

4. Change only the aggregate expression

MATCH (a:A {v:1})-[r:E WHERE r.tag = "ok"]->(b:A)
RETURN count(r) AS c;

Result:

c = 1

Expected behavior

The predicate:

r.tag = "ok"

matches exactly one relationship and therefore produces exactly one row.

Both aggregate expressions should consequently return:

count(*) = 1
count(r) = 1

Actual behavior

count(*) = 0
count(r) = 1

Changing only the aggregate expression changes the result of the preceding MATCH.

Controls

Control 1: Direct row materialization

MATCH (a:A {v:1})-[r:E WHERE r.tag = "ok"]->(b:A)
RETURN r.tag AS tag;

Result:

tag = "ok"

The relationship inline predicate produces the expected row when the relationship property is returned directly.

Control 2: Literal inline predicate

MATCH (a:A {v:1})-[r:E WHERE 1=1]->(b:A)
RETURN count(*) AS c;

Result:

c = 2

This shows that count(*) can work with the relationship inline predicate syntax when the predicate does not access a relationship property.

Control 3: Clause-level WHERE

MATCH (a:A {v:1})-[r:E]->(b:A)
WHERE r.tag = "ok"
RETURN count(*) AS c;

Result:

c = 1

Moving the same condition from the relationship pattern to a clause-level WHERE restores the correct result.

Workaround

Use a clause-level WHERE predicate:

MATCH (a:A {v:1})-[r:E]->(b:A)
WHERE r.tag = "ok"
RETURN count(*) AS c;

Alternatively, use count(r) when the relationship variable is already bound:

MATCH (a:A {v:1})-[r:E WHERE r.tag = "ok"]->(b:A)
RETURN count(r) AS c;

Cross-engine comparison

Isolated recheck

Reproduced in a fresh arcadedata/arcadedb:latest Docker container with a clean database.

The failing query was executed three times and returned 0 in all three runs. All controls passed in all three runs.

Why this looks like a bug

1. Internal aggregate contradiction

   The same MATCH produces one materialized row and count(r) = 1, but count(*) = 0.

2. count(*) and count(r) must agree here

   The relationship variable is bound by a regular MATCH and is therefore non-null in every produced row. Both expressions should count the same rows.

3. Predicate-location control

   Moving the identical condition to a clause-level WHERE restores the correct count(*) result.

4. Literal-versus-property control

   A literal relationship inline predicate works, while an inline predicate that accesses r.tag fails only on the count(*) path.

5. Silent wrong result

   The query completes successfully without an error or warning but returns an incorrect aggregate value.

Impact

Queries that combine count(*) with a relationship inline WHERE predicate that accesses a relationship property can silently return an incorrect count.

The equivalent query using a clause-level WHERE or count(r) returns the correct value.