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

[AGiorgetti]

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.

[AGiorgetti]

Issue #59 is implemented and validated.

Test-first evidence:

• Added sync and async regression coverage for GetStreamsToSnapshot with the same stream id in buckets a and b.
• The regression adds a bucket b snapshot and verifies bucket a still returns its own snapshot candidate with SnapshotRevision == 0.
• SQLite focused regression failed before the SQL fix, then passed after it.

Fix summary:

• Common SQL GetStreamsRequiringSnapshots now filters Commits with WHERE C.BucketId = @BucketId.
• Snapshot joins now include S.BucketId = C.BucketId as well as stream id.
• Oracle SQL received the same logical fix now that [Order 03][P1] Research and enable Oracle integration test infrastructure #58 has restored Oracle support/tests.
• Shared regression tests were linked into all provider test projects.

Validation:

• dotnet test .\src\NEventStore.Persistence.Sqlite.Tests\NEventStore.Persistence.Sqlite.Core.Tests.csproj -c Release --no-build -f net8.0
  • Result: 141 passed, 0 failed, 0 skipped.
• dotnet build .\src\NEventStore.Persistence.Sql.Core.sln -c Release --no-restore /p:ContinuousIntegrationBuild=true -m:1 -nr:false
  • Result: passed.
• dotnet test .\src\NEventStore.Persistence.Oracle.Tests\NEventStore.Persistence.Oracle.Core.Tests.csproj -c Release -f net8.0 --no-build
  • Result: 141 passed, 0 failed, 0 skipped.

Handoff notes were added to docs/Project-Analysis-Issue-Drafts.md.