find_nearest_posts_with_embedding returns slice/PCA results in arbitrary order: reduced-path rerank has no ORDER BY and joins chunks on post_id only
Problem and intended result
In slice and PCA modes, hivesense_app.find_nearest_posts_with_embedding (the paging search, db/search.sql:347) returns posts in arbitrary order instead of nearest first. #63 (closed)'s new embed-slice check (.aidev/run-checks.sh check_paging) caught this on session/hivesense at 5137236. The query was post 900101's own first chunk, embedded with mxbai-embed-xsmall-v1, slice mode, 256 dims:
one-shot order: [900101,900102,900105,900103]
FAIL: find_nearest_posts_with_embedding(_limit => 2) returned [900103,900105], expected [900101,900102]
ok: find_nearest_posts_with_embedding(_limit => 100) returned [900103,900105,900102,900101]
FAIL: find_nearest_posts_with_embedding(_limit => 100, _start_post_id => 900102) returned [900101], expected [900105,900103]The post the query came from ranks last. The one-shot variant (what the endpoints call) and the full-vector path of the paging function rank correctly. A small limit therefore returns the wrong posts, and a continuation from _start_post_id returns the wrong remainder. This is the same class of defect as #56 (closed): the slice branch of the paging variant diverges from the full branch.
Root cause
Both reduced-vector branches of the main retrieval loop (db/search.sql, slice at about lines 471-491, PCA at about lines 494-516) rerank like this:
SELECT ann.post_id, ann.chunk_number, (pv.embedding <=> $1)::float4 AS similarity
FROM ann
JOIN hivesense_app.posts_vectors pv ON pv.post_id = ann.post_id- No
ORDER BY. TheannCTE is ordered byann_dist, but the outer join's row order is unspecified. TheFOR rec IN EXECUTE sqlloop emits posts in the order the rows arrive, keeping the first chunk it sees per post (seen_ids). So the emitted rank, the_limitcut and the_start_post_idcontinuation all follow arbitrary order. - The join ignores
chunk_number. Each ANN row is joined to every chunk of its post, sosimilaritycomes from whichever chunk arrives first rather than from the chunk the ANN step found. Meanwhileann.chunk_numberreports the ANN chunk, which doesn't match.
The full-vector path (ORDER BY similarity, hpv.post_id, about line 573) gets both right.
Required behavior
- In both reduced branches of
find_nearest_posts_with_embedding, join the rerank on(post_id, chunk_number)and addORDER BY similarity, ann.post_idto the outer select. Ranking by the full-embedding rerank distance is what the one-shot variant and the full path already do. Keep the parameter layout and the rest of the loop unchanged. - Add
check_pagingtoembed_pcain.aidev/run-checks.sh, asembed-sliceandembed-searchalready have it. The PCA path has the same defect, and today nothing checks it.
Acceptance criteria
- In slice mode,
find_nearest_posts_with_embeddingwith_limit2 returns the first two posts of the one-shot order, and the continuation from the second one returns the rest of that order, with no repeats or gaps. - The same holds in PCA mode, and
embed-pcanow runs those checks. - Every step of the
fullslot passes:stack-pins,install,events-id,install-lock,reinstall,embed-search,embed-slice,embed-pcaandapi-smoke. The stack must be.aidev/test-stack.compose.ymlwith the slot's arguments as.aidev/project.yamldeclares them on this branch. - Full-mode results are unchanged.
Verification
The workflow's own run_full_tests runs the slot as approved on aidev/integration, which doesn't yet include #63 (closed)'s new steps. So the gate passing proves nothing about embed-slice or embed-pca. Run the full step list by hand, as .aidev/README.md's "To run the stack checks by hand" section shows, using the step list from .aidev/project.yaml's full slot. Record the junit result for every step in the workflow log. Before the fix, embed-slice fails exactly as shown above. After it, every step must pass.
Integration points
db/search.sqlhivesense_app.find_nearest_posts_with_embedding— reduced-path rerank joined on(post_id, chunk_number)and ordered by similarity.aidev/run-checks.shembed_pca— runscheck_paging
Scope boundary
No changes to find_nearest_posts_with_embedding_one_shot, the endpoints, or the paging logic's start/limit handling, which is correct once its input is ordered. Do not weaken check_paging.