[Order 02][P1] Fix async paged-query infinite loop when PageSize is zero

[AGiorgetti]

Priority order

Order: 02 of 09
Priority: P1 correctness

Problem

Async paged reads can loop forever when the configured page size is 0 and the result set is empty.

Evidence:

• src/NEventStore.Persistence.Sql/SqlDialects/CommonDbStatement.cs:331
• Continuation condition is while (Dialect.CanPage && recordsRead == PageSize).
• SqlPersistenceEngine allows PageSize == 0, where zero represents infinite page size.

Impact

• Empty async reads can hang for pageable dialects.
• Affected APIs include checkpoint reads, bucket-scoped reads, and stream reads that call ExecutePagedQueryAsync.

Mandatory test-first workflow

• Add tests first that reproduce or verify the issue before changing production code.
• Include an async empty-result test with configured page size 0.
• Cover at least GetFromAsync(0, observer, token) and one bucket-scoped async read.
• The test must fail or time out before the fix and complete after the fix.

Proposed action

• Base continuation on the computed local pageSize.
• Continue only when pageSize > 0 && recordsRead == pageSize.

Acceptance criteria

• Empty async reads complete when PageSize == 0.
• Multi-page async reads still return ordered, complete results when PageSize > 0.
• Sync behavior is unchanged.

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

[AGiorgetti]

Implemented and validated locally.

Test-first evidence:

• Added async regression coverage for page size 0 under src/NEventStore.Persistence.Sql.Tests/PersistenceTests.InfinitePageSize.Async.cs.
• The regression initializes the provider fixture with page size 0 and verifies empty async reads complete for both GetFromAsync(0, observer, token) and GetFromAsync(Bucket.Default, 0, observer, token).
• SQLite focused regression failed before the fix with missing @Limit binding for page size 0, then passed after the fix.

Fix summary:

• ExecutePagedQueryAsync now keeps the local paging size at 0 so continuation paging is disabled for infinite page size.
• For pageable dialect SQL, it still binds the required limit parameter using an effectively unbounded value so provider SQL can execute.
• Sync paging behavior was left unchanged.
• The shared regression test is linked into all provider test projects.

Validation:

• dotnet test .\src\NEventStore.Persistence.Sqlite.Tests\NEventStore.Persistence.Sqlite.Core.Tests.csproj -c Release -f net8.0 --filter "FullyQualifiedName~when_reading_empty_async_pages_with_infinite_page_size"
  • Result: 2 passed, 0 failed, 0 skipped.
• dotnet test .\src\NEventStore.Persistence.Sqlite.Tests\NEventStore.Persistence.Sqlite.Core.Tests.csproj -c Release --no-build -f net8.0
  • Result: 143 passed, 0 failed, 0 skipped.
• dotnet test .\src\NEventStore.Persistence.Oracle.Tests\NEventStore.Persistence.Oracle.Core.Tests.csproj -c Release -f net8.0 --no-build --filter "FullyQualifiedName~when_reading_empty_async_pages_with_infinite_page_size"
  • Result: 2 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.

Doc updated:

• docs/Project-Analysis-Issue-Drafts.md now has [Order 02][P1] Fix async paged-query infinite loop when PageSize is zero #60 handoff notes.