query_graph: RETURN DISTINCT … ORDER BY … LIMIT still under-returns on v0.8.1 (regression / incomplete fix for #237)

[rudi193-cmd]

Summary

On v0.8.1, the single-clause shape RETURN DISTINCT … ORDER BY … LIMIT still silently under-returns rows. #237 was closed 2026-05-31 for a DISTINCT execution-order bug; we re-checked after syncing to current main (affa223, 2026-07-05) and the failure mode below is still reproducible.

This matters for audit/dead-code queries that look complete but are truncated.

Environment

• Binary: codebase-memory-mcp 0.8.1 (linux-amd64, ~/.local/bin/codebase-memory-mcp)
• Project: home-sean-campbell-github-willow-2.0 — 17,832 nodes / 57,737 edges, status ready
• Public repo indexed: https://github.com/rudi193-cmd/willow-2.0 (Willow fleet)

Reproduction

Ground truth — five distinct labels exist:

MATCH (n) RETURN count(DISTINCT n.label) AS distinct_labels
-- → 5

Broken (under-returns):

MATCH (n) RETURN DISTINCT n.label AS label ORDER BY label LIMIT 30
-- → 2 rows: Section, Variable  (expected 5)

MATCH (n:Function) RETURN DISTINCT n.file_path AS path ORDER BY path LIMIT 5
-- → 1 row  (expected 5)

Workaround (correct):

MATCH (n) WITH DISTINCT n.label AS label RETURN label ORDER BY label LIMIT 20
-- → 5 rows: File, Folder, Module, Section, Variable

MATCH (n:Function) WITH DISTINCT n.file_path AS path RETURN path ORDER BY path LIMIT 5
-- → 5 rows

Expected

RETURN DISTINCT expr ORDER BY expr LIMIT k should apply DISTINCT (and ORDER BY) before LIMIT, matching the workaround semantics and count(DISTINCT …).

Impact

Agents running bounded “list all distinct X” queries via a single RETURN DISTINCT … ORDER BY … LIMIT clause get silently incomplete results — indistinguishable from a genuinely small universe. We hit this during a fleet structural audit (Willow F-005); our wrapper now steers to WITH DISTINCT first.

Relation to #237

#237 reported DISTINCT applied after ORDER BY + LIMIT. This report is the same user-facing failure class on v0.8.1: either the fix did not cover the RETURN DISTINCT … ORDER BY … LIMIT surface, or there is a regression. Happy to provide a minimal indexed fixture if helpful.

Confirmations

• Searched existing issues; Cypher: DISTINCT applied after ORDER BY + LIMIT instead of before (execution order bug) #237 is the closest prior report (closed).
• Repro uses a public OSS repository (willow-2.0).

[DeusData]

Thanks for retesting this on v0.8.1/current main. Triage: high-priority Cypher correctness under #594. Silent under-return is a dangerous failure mode because audit queries look complete, so the fix needs a reproduce-first query fixture for RETURN DISTINCT + ORDER BY + LIMIT and should fail loudly rather than truncate if the planner cannot support a shape.

[pcristin]

I’d like to take this one.

I checked the current Cypher execution path, and the under-return looks caused by the RETURN post-processing order rather than parsing. execute_return_clause currently applies:

1. projection
2. ORDER BY
3. SKIP / LIMIT
4. DISTINCT

That means RETURN DISTINCT … ORDER BY … LIMIT can first truncate duplicate projected rows, then dedupe the already-limited subset, which matches the reported symptom.

The fix I plan to try is narrow:

• Apply DISTINCT before ORDER BY and pagination for RETURN DISTINCT.
• Disable the early proj_cap = ret->limit optimization in execute_return_simple when ret->distinct is true, so RETURN DISTINCT … LIMIT without ORDER BY cannot under-return either.
• Keep the early limit fast path for non-distinct, non-ordered, non-skipped simple returns.
• Add reproduce-first tests using the existing Cypher fixture, e.g. RETURN DISTINCT f.file_path AS path ORDER BY path LIMIT 3 and RETURN DISTINCT f.file_path AS path LIMIT 3, both expecting 3 rows.

This should stay contained to src/cypher/cypher.c plus tests/test_cypher.c.

[pcristin]

Small correction to my test plan after re-reading the triage note: I’ll make the reproduce-first fixture explicitly cover duplicate projected values before LIMIT, e.g. MATCH (n) RETURN DISTINCT n.label AS label ORDER BY label LIMIT 2, so it fails on the current execution order instead of relying on file_path ordering.

I’ll also keep the codeowner requirement in scope: this path should not silently truncate. If the executor can support the shape, it should return the full distinct-before-limit result; if it cannot support a shape safely, it should fail loudly rather than under-return.