[Order 01][P1] Fix cross-bucket contamination in snapshot candidate query #59

Priority order

Order: 01 of 09
Priority: P1 correctness and performance

Problem

GetStreamsRequiringSnapshots can mix data across buckets.

Evidence:

• src/NEventStore.Persistence.Sql/SqlDialects/CommonSqlStatements.resx:182
• The query joins snapshots with ON C.BucketId = @BucketId but does not filter Commits with WHERE C.BucketId = @BucketId.
• The snapshot join also omits S.BucketId = C.BucketId.

Impact

• GetStreamsToSnapshot(bucketId, threshold) can return streams from other buckets.
• Snapshots from another bucket with the same StreamId can change SnapshotRevision, hiding or creating snapshot candidates incorrectly.
• The query groups more rows than needed on large multi-bucket stores.

Mandatory test-first workflow

• Add failing sync and async acceptance tests first.
• Tests must create the same StreamId in buckets a and b.
• Tests must prove GetStreamsToSnapshot("a", ...) does not return bucket b streams.
• Tests must prove a snapshot in bucket b does not affect bucket a snapshot eligibility.
• Do not change production SQL until the regression is covered.

Proposed action

• Add WHERE C.BucketId = @BucketId.
• Change the snapshot join to include S.BucketId = C.BucketId.
• Keep the existing keyset paging predicate C.StreamId > @StreamId.
• Do not change Oracle SQL in this issue unless [Order 03][P1] Research and enable Oracle integration test infrastructure #58 is complete and Oracle tests are running; otherwise leave Oracle as a follow-up blocked by [Order 03][P1] Research and enable Oracle integration test infrastructure #58.

Acceptance criteria

• New tests fail before the fix and pass after it.
• Sync and async snapshot-candidate APIs behave consistently.
• Provider-specific SQL remains covered by nearest provider tests.

Source: docs/Project-Analysis-Issue-Drafts.md.