René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"[Order 01][P1] Fix cross-bucket contamination in snapshot candidate query","articleBody":"## Priority order\n\nOrder: 01 of 09\nPriority: P1 correctness and performance\n\n## Problem\n\n`GetStreamsRequiringSnapshots` can mix data across buckets.\n\nEvidence:\n\n- `src/NEventStore.Persistence.Sql/SqlDialects/CommonSqlStatements.resx:182`\n- The query joins snapshots with `ON C.BucketId = @BucketId` but does not filter `Commits` with `WHERE C.BucketId = @BucketId`.\n- The snapshot join also omits `S.BucketId = C.BucketId`.\n\n## Impact\n\n- `GetStreamsToSnapshot(bucketId, threshold)` can return streams from other buckets.\n- Snapshots from another bucket with the same `StreamId` can change `SnapshotRevision`, hiding or creating snapshot candidates incorrectly.\n- The query groups more rows than needed on large multi-bucket stores.\n\n## Mandatory test-first workflow\n\n- Add failing sync and async acceptance tests first.\n- Tests must create the same `StreamId` in buckets `a` and `b`.\n- Tests must prove `GetStreamsToSnapshot(\"a\", ...)` does not return bucket `b` streams.\n- Tests must prove a snapshot in bucket `b` does not affect bucket `a` snapshot eligibility.\n- Do not change production SQL until the regression is covered.\n\n## Proposed action\n\n- Add `WHERE C.BucketId = @BucketId`.\n- Change the snapshot join to include `S.BucketId = C.BucketId`.\n- Keep the existing keyset paging predicate `C.StreamId \u003e @StreamId`.\n- Do not change Oracle SQL in this issue unless #58 is complete and Oracle tests are running; otherwise leave Oracle as a follow-up blocked by #58.\n\n## Acceptance criteria\n\n- New tests fail before the fix and pass after it.\n- Sync and async snapshot-candidate APIs behave consistently.\n- Provider-specific SQL remains covered by nearest provider tests.\n\nSource: `docs/Project-Analysis-Issue-Drafts.md`.","author":{"url":"https://github.com/AGiorgetti","@type":"Person","name":"AGiorgetti"},"datePublished":"2026-06-11T09:19:37.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":1},"url":"https://github.com/59/NEventStore.Persistence.SQL/issues/59"}